How to Call VBA Function from Excel Cells?

The Problem (Q-score 7, ranked #91st of 303 in the Excel VBA archive)

The scenario as originally posted in 2013

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:UsersUserNameDesktopTestSample.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?

Why community consensus is tight on this one

Across 303 Excel VBA entries in the archive, the accepted answer here holds strong answer (top 25 %%) status — meaning voters are unusually aligned on the right fix.

The Verified Solution — strong answer (top 25 %%) (+16)

6-line Excel VBA pattern (copy-ready)

Here’s the answer

Steps to follow:

1. Add a new module from the Visual Basic Editor (In Excel, hit Alt+F11 on Windows / fn+option+F11 on a Mac).

2. 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
   

3. Then use it in any cell like you would any other function: =findArea(B12,C12).

When to Use It — classic (2013–2016)

Ranked #91st in its category — specialized fit

This pattern sits in the 95% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the Excel VBA archive for a higher-consensus alternative.

What changed between 2013 and 2026

The answer is 13 years old. The Excel VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.