Excel Object cannot be used as a parameter in custom function, is it?

Apr 19, 2013 at 3:46 AM
code below:
Public Shared Function test(ByVal range As Object) As String
    Return range.Address(False, False)
End Function
In Excel, calling this fucntion will result in an error "#VALUE!"
=test(A1)
Coordinator
Apr 19, 2013 at 9:29 AM
Hi,

This is quite a common question, so I've added a page that tried to address it. Have a look at the Range Parameters page.

I haven't checked the code, so if I need to fix anything or explain better, please write back.

Regards,
Govert
Apr 20, 2013 at 12:38 PM
Thanks a lot! I'll try it!
Apr 20, 2013 at 3:44 PM
Edited Apr 20, 2013 at 3:46 PM
I'm sorry Govert, following your guide in the Range Parameters page, the function still return a "#VALUE!" error:
Public Shared Function test(<ExcelArgument(AllowReference:=True)> ByVal range As Object) As String
    If TypeOf range Is ExcelReference Then
        Return ReferenceToRange(range).Address(False, False)
    End If
End Function
Is there anything else needed?
Coordinator
Apr 21, 2013 at 9:03 PM
Hi,

Sorry - yes , there was one bit missing. The exported function must also be marked as IsMacroType:=True. Can you try:
<ExcelFunction(IsMacroType:=True)> _
Public Shared Function GetAddress(ByVal arg As Object) As String
    If TypeOf range Is ExcelReference Then
        Return ReferenceToRange(range).Address(False, False)
    End If
End Function
-Govert
Apr 22, 2013 at 3:27 AM
It works! Thank you!