I have spent the past 2 days trying to figure this, but finally decided to seek help here. This must be a straightforward query for the VBA experts here.
I create an Empty Excel. Then go to the Code Editor, create a Module and define a Subroutine in it like this
I invoke the subroutine using Alt-F8 and the code works as intended. The text "Hello World..." appears in cell B2 on my sheet.
Next I define a function in the same Module as follows
Code
Public Function TestFromFunction() As Integer
Range("B2") = "Hello world. Called from Function"
TestFromFunction = 1
End Function
I invoke this Function in my Excel file in cell A1 by defining the value of A1 as "=TestFromFunction()"
At this point I get a #VALUE error in cell A1.
The same code which works as a Sub DOES NOT work as a function in VBA.
What am I missing? Any help/pointers is appreciated in advance. I am using Excel 2007