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)

Answer 2

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.