How to call VBA function from Excel cells (2010)?

Question

I defined a few functions in a workbook using VBA, and then expected to be able to use them in a cell formula - but Excel does not recognise the function. I just get #NAME?

Tried:

• Realising I had created an XSLX file, I converted it to a XSLM file. Didn't work.
• Removed all types from the function declaration. Didn't work.
• Moved the function into the worksheet VBA module. Didn't work.
• Added Public to the declaration. Didn't work.

What am I missing?

This isn't clever code, either:

Function Square2(AnyNumber)

'return the square of any integer
Square2 = AnyNumber * AnyNumber

End Function

Answer 1

Answer

Putting the function in the "ThisWorkbook" area can cause the #NAME? problem. Create a new Module (Right Click on the VBAProject Folder, Insert, New Module) and put the function there instead.

Steps

1. Open the VBA Editor (Alt + F11 on Windows / Fn + Option + F11 on a Mac)
2. Right-click VBAProject
3. Select Insert >> Module

4. Create a Public function inside Module1, for example:

   Public Function findArea(ByVal width as Double, _
                            ByVal height as Double) As Double
       ' Return the area
       findArea = width * height
   End Function
   

5. Call it from a cell, like any other function: =findArea(B12,C12)