Microsoft Excel

Herbers Excel/VBA-Archiv

Tabellenblatt aus VBA-Funktion heraus einfügen


Betrifft: Tabellenblatt aus VBA-Funktion heraus einfügen von: Stephan
Geschrieben am: 24.07.2017 18:03:44

Hallo,

ich rufe eine VBA-Funktion über die Formeleingabe in einer Excel-Tabellenzelle auf
mit =meineFunktion(Inputwert).

Wenn diese VBA-Funktion aus der Tabelle aufgerufen wird, möchte ich temporär ein neues Tabellenblatt in der aktiven Arbeitsmappe einfügen, um den Excel-Solver darin zu nutzen.

Ich versuche das beispielsweise über

Public Function meineFunktion(Input as Double) as Double
With ThisWorkbook
    .Sheets.Add
    ActiveSheet.Name = Bezeichnung
End With
End Function
Der Code der Funktion liegt in einem VBA-Modul vor.

Das Tabelle einfügen schlägt jedoch fehl und funktioniert nur, wenn ich den VBA-Code zum Tabelle einfügen aus einer Sub oder dem Direktfenster heraus durchführe.
Gibt es irgend einen Weg, Tabellenblätter auch aus Funktionen heraus einzufügen, die aus einem Arbeitsblatt aufgerufen werden?

Ich benötige das, da ich in der VBA-Funktion als Zwischenschritt den Excel-Solver für ein nichtlineares Gleichungssystem nutzen will und der Excelsolver in VBA nur über ein Tabellenblatt läuft. Das temporär erzeugte Tabellenblatt soll dann nach der Solverroutine wieder gelöscht werden und nur das Endergebnis als Double-Wert über die Funktion in die Tabellenzelle zurückgegeben werden, in der die Funktion aufgerufen wurde.

Vielen Dank im Vorraus für Eure Hilfe.

  

Betrifft: Das ist normalerweise nicht möglich, ... von: Luc:-?
Geschrieben am: 24.07.2017 18:58:08

…Stephan,
da derartige Aktionen aus einer ZellFml heraus nicht ausgeführt wdn; das verhindert der Fml-Interpreter von Xl. Es geht auch nicht über Zuhilfenahme der vbFkt Evaluate, aber evtl mit einem anderen Trick (mal abgesehen von API-Nutzung), sollte dann aber nur in Ausnahmefällen genutzt wdn. Das teste ich noch.
Allerdings wundert mich, dass du deine Fkt, so wie sie ist, in den VBE schreiben konntest. Eigentlich sollte die KopfZeile in Rot erscheinen, weil du einen VBA-Begriff verwendet hast, der Parametrierung erfordert. Einen solchen Begriff sollte man niemals als VariablenNamen verwenden! Deutsche Begriffe sind hier meist unverdächtig, also bspw Eingabe statt Input.
Feedback nicht unerwünscht! Gruß, Luc :-?

Besser informiert mit …


  

Betrifft: AW: Das ist normalerweise nicht möglich, ... von: Stephan
Geschrieben am: 25.07.2017 09:48:06

Hallo Luc,

danke für Deine Mithilfe. Mit dem Inputparameter hast Du Recht :-)
Meine Funktion hat in Wirklichkeit mehrere Inputs, ich habe das hier für den Thread vereinfacht,
ohne es vorher im VBA einzugeben...
Bin gespannt, ob sich noch ein Workaround für das Problem findest.
Viele Grüße, Stephan


  

Betrifft: So, mal als Ansatz Folgendes, ... von: Luc:-?
Geschrieben am: 25.07.2017 21:07:58

…Stephan;
meine ursprünglich angedachte Aktion fktioniert leider nicht mit BlattEinfügen, aber mit WerteEintrag in andere Zellen. Evtl müssen wir darauf in der Folge noch zurückkommen, weil ich eine gewisse Problematik noch in der Nutzung eines im Nachhinein erzeugten Blattes in der diese Erzeugung initiierenden UDF sehe. Evtl muss die 2× berechnet wdn (war jedenfalls bei entsprd älteren Versu­chen so), was alles komplizierter gestalten würde. Außerdem müssen dann ja wohl auch Werte in das Solver-Blatt einge­tragen wdn! Nur die BlattErzeugung wäre per physischer Entkopplung von UDF (in der ZellFml) und BlattEinfügungsProzedur kein sonderliches Problem. Letztere müsste eine EreignisProzedur sein, die entweder auf Worksheet_Change oder …_Calculate reagiert, je nachdem, ob das ein- oder mehrmalig erfolgen soll. Ich habe das PgmBsp jetzt mal auf mehrmalig (inkl Löschen des tempBlattes) ausgelegt:
• In einem NormalModul (.bas) die UDF:

Option Explicit

Public isInit As Boolean

Function SolverPrep(ByVal Eingabe As Double)
    Const HilfsblattTitel$ = "tempTab"
    isInit = Not isInit
    SolverPrep = Eingabe
End Function
• Im DokumentKlassenModul der Mappe (.cls) die EreignisProz:
Option Explicit

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Const naRelSh$ = "Tabelle2", naAuxSh$ = "tempTab"
    On Error Resume Next
    Application.EnableEvents = False
    If IsError(Sheets(naAuxSh)) Then
        If Sh.Name = naRelSh And isInit Then
            Me.Sheets.Add , Sheets(naRelSh)
            ActiveSheet.Name = naAuxSh
        End If
    ElseIf Sh.Name = naRelSh Then
        Me.Sheets(naAuxSh).Delete: isInit = False
    End If
    Application.EnableEvents = True
End Sub
Das wäre jetzt die in dieser Form fktionierende Basis für deine eigenen Experimente. Für die GesamtFuntionalität des fertigen Projekts kann ich natürlich nicht garantieren. Dabei könnten sich noch etliche Probleme einstellen.
Die BlattNamensKonstanten der EreignisProz musst du natürlich anpassen.
Viel Erfolg! Gruß, Luc :-?


Beiträge aus den Excel-Beispielen zum Thema "Tabellenblatt aus VBA-Funktion heraus einfügen"