Menu
I'm using Excel 2010, and have defined the following 3 functions in spreadsheet's Module.
Option Explicit
Public Function AAA() As Double
AAA = 3
End Function
Public Function AAA2() As Double
AAA2 = 4
End Function
Public Function AAA3AAA() As Double
AAA3AAA = 5
End Function
When I reference the three functions in my spreadsheet by entering the following into 3 adjacent cells
=AAA()
=AAA2()
=AAA3AAA()
The second function generates a #REF error. The other functions work as expected. Anyone know why this is happening? The reason I'm asking is a few of my macros quit working when I upgraded from Office XP to Office 2010. After quite a bit of experimenting, it appears the function name itself is the culprit? The error did not occur in Excel from Office-XP.
439
With VBA, you can create a function that returns a variant that can contain an entire array of values. Array formulas are also available as inbuilt functions in Excel worksheets.
no there are no nested functions in VBA, but if you want to make it more streamlined then declare the function private then only that module can see it.
Press Alt + F11 when VBE is open to go back to the Excel window. After opening VBE, you need to add a new module where you will write your functions. Right-click on the VBA project pane and select Insert -> Module. An empty module window will appear where you are to specify your custom function.
If you are not interested in the return value of a function, you can call a function the same way you call a Sub procedure. Omit the parentheses, list the arguments, and don't assign the function to a variable, as shown in the following example.
Same thing happened to me. What I notice is that unlike the other two, "AAA2" could be the text of a cell address. I suspect that's the problem.
165
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
