I am a VBA newbie, and I am trying to write a function that I can call from Excel cells, that can open a workbook that's closed, look up a cell value, and return it.
So far I know how to write a macro like this:
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
The macro OpenWorkbook() runs perfectly fine, but when I am trying to call OpenWorkbookToPullData(...) directly from an Excel cell, it doesn't work. The statement:
Set openWb = Workbooks.Open(path, , True)
returns Nothing.
Does anyone know how to turn it into a working VBA function that can be called from Excel cell?
A Function will not work, nor is it necessary:
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
The issue you have encountered is that UDF
s cannot modify the Excel environment, they can only return a value to the calling cell.
There are several alternatives
For the sample given you don't actually need VBA. This formula will work
='C:\Users\UserName\Desktop\[TestSample.xlsx]Sheet1'!$B$2
Use a rather messy work around: See this answer
You can use ExecuteExcel4Macro
or OLEDB
Steps to follow:
Open the Visual Basic Editor. In Excel, hit Alt+F11 if on Windows, Fn+Option+F11 if on a Mac.
Insert a new module. From the menu: Insert -> Module (Don't skip this!).
Create a Public
function. Example:
Public Function findArea(ByVal width as Double, _
ByVal height as Double) As Double
' Return the area
findArea = width * height
End Function
Then use it in any cell like you would any other function: =findArea(B12,C12)
.
Source: Stackoverflow.com