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

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

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)

I faced the same issue, after struggling around following worked for me:

My function was inside a module in macro workbook called Personal.XLSB. I prefixed the function name with the personal macro workbook filename and !, so if function name is theFunction(x,y) I entered in cell "=PERSONAL.XLSB!theFunction(x,y). This worked.

Please note that PERSONAL.XLSB is always open in hidden mode for me.

Make sure you are not in design mode.

There is a design mode button on the developer tab in excel and beside the run/stop buttons in the VBA editor. If it is selected, and won't let you unselect it, then try reopening the workbook with macros enabled.

If it still is enabled, or won't let macros run, make sure macros are enabled.

Activate macros.

• Excel 2010
• Excel 2007

-- https://stackoverflow.com/a/20659823/258482

I had an identical problem, including a working function that later stopped working giving a #NAME error. I have managed to fix both by making sure the name of the module is not the same as the name of the function. I had a working function F_1 in Module1, I changed the module name to F_1 and it stopped working, now back at Module1 and the function works again. My second function also began working when I changed the module name from F_2 to Module2.