Ich bin ein VBA-Neuling, und ich versuche, eine Funktion zu schreiben, die ich von Excel-Zellen aufrufen kann, die eine Arbeitsmappe öffnen kann, die geschlossen ist, einen Zellwert nachschlagen und ihn zurückgeben.
Bis jetzt weiß ich, wie man ein Makro wie dieses schreibt:
Sub OpenWorkbook()
Dim path As String
path = "C:\Users\UserName\Desktop\TestSample.xlsx"
Dim currentWb As Workbook
Set currentWb = ThisWorkbook
currentWb.Sheets("Sheet1").Range("A1") = OpenWorkbookToPullData(path, "B2")
End Sub
Function OpenWorkbookToPullData(path, cell)
Dim openWb As Workbook
Set openWb = Workbooks.Open(path, , True)
Dim openWs As Worksheet
Set openWs = openWb.Sheets("Sheet1")
OpenWorkbookToPullData = openWs.Range(cell)
openWb.Close (False)
End Function
Das Makro OpenWorkbook() läuft einwandfrei, aber wenn ich versuche, OpenWorkbookToPullData(...) direkt aus einer Excel-Zelle aufzurufen, funktioniert es nicht. Die Anweisung:
Set openWb = Workbooks.Open(path, , True)
gibt nichts zurück.
Weiß jemand, wie man diese Funktion in eine funktionierende VBA-Funktion umwandelt, die von einer Excel-Zelle aus aufgerufen werden kann?
Schritte zu folgen:
Öffnen Sie den Visual Basic Editor. Drücken Sie in Excel Alt+F11 wenn Sie Windows verwenden, Fn+Option+F11 wenn Sie einen Mac verwenden.
Fügen Sie ein neues Modul ein. Über das Menü: Einfügen -> Modul (Überspringen Sie dies nicht!).
Erstellen Sie eine "öffentliche" Funktion. Beispiel:
Public Function findArea(ByVal width as Double, _
ByVal height as Double) As Double
' Rückgabe der Fläche
findArea = Breite * Höhe
End Function
Verwenden Sie diese Funktion dann in einer beliebigen Zelle wie jede andere Funktion: =findArea(B12,C12)
.
Das Problem, auf das Sie gestoßen sind, ist, dass UDFs die Excel-Umgebung nicht verändern können, sondern nur einen Wert an die aufrufende Zelle zurückgeben können.
Es gibt mehrere Alternativen
Für das angegebene Beispiel brauchen Sie eigentlich kein VBA. Diese Formel wird funktionieren
='C:\Benutzer\Benutzername\Desktop\[TestSample.xlsx]Sheet1'!$B$2
Verwenden Sie eine ziemlich chaotische Lösung: Siehe diese Antwort
Sie können ExecuteExcel4Macro
oder OLEDB
verwenden
Eine Funktion wird nicht funktionieren und ist auch nicht notwendig:
Sub OpenWorkbook()
Dim r1 As Range, r2 As Range, o As Workbook
Set r1 = ThisWorkbook.Sheets("Sheet1").Range("A1")
Set o = Workbooks.Open(Filename:="C:\TestFolder\ABC.xlsx")
Set r2 = ActiveWorkbook.Sheets("Sheet1").Range("B2")
[r1] = [r2]
o.Close
End Sub