[vba] How to Call VBA Function from Excel Cells?

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?

This question is related to vba excel

The answer is


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 UDFs cannot modify the Excel environment, they can only return a value to the calling cell.

There are several alternatives

  1. For the sample given you don't actually need VBA. This formula will work
    ='C:\Users\UserName\Desktop\[TestSample.xlsx]Sheet1'!$B$2

  2. Use a rather messy work around: See this answer

  3. You can use ExecuteExcel4Macro or OLEDB


Here's the answer

Steps to follow:

  1. Open the Visual Basic Editor. In Excel, hit Alt+F11 if on Windows, Fn+Option+F11 if on a Mac.

  2. Insert a new module. From the menu: Insert -> Module (Don't skip this!).

  3. 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
    
  4. Then use it in any cell like you would any other function: =findArea(B12,C12).