Custom Functions

[djt76010]

I am writing some custom functions to be saved in an Add-In file, but am having trouble saving named ranges. I would like named ranges in the Add-In file to be available in other spreadsheets. Is there a way to do this? Or is there a way to take a range from Excel and save it into a globally available named range using VBA?

 

[djt76010]

Sorry, the code I have written is recursive and lengthy and so I just posted a simplified version. I figured out that by defaulting the value of an Optional variable you can test the default value

Function MyFunction(Age, Optional Table As String = "missing")

    Dim MyRANGE As Range
    
    If Table = "missing" Then
        MyFunction = "Table Missing"
    Else   
        Set MyRANGE = ThisWorkbook.Names(Table).RefersToRange
        MyFunction = Application.VLookup(Age, MyRANGE, 2, False)
    End If

End Function

but if the variable is not Optional the default variable assignment is telling me that there is a compile error because it is expecting a list separator or ) instead of the =

Function MyFunction(Age, Table As String = "missing")

    Dim MyRANGE As Range
    
    If Table = "missing" Then
        MyFunction = "Table Missing"
    Else   
        Set MyRANGE = ThisWorkbook.Names(Table).RefersToRange
        MyFunction = Application.VLookup(Age, MyRANGE, 2, False)
    End If

End Function

 

[RoryA]

I don't see anything wrong with the first version. (It wouldn't make sense to have a default value for a variable that is not optional!)

 

[djt76010]

When you add the "As String" declaration to the variable the "IsMissing" test stops working. How do I test for missing "As String" variables that are not optional?

 

[mikerickson]

If you fail to give a non-optional argument, the calling statement (myVar = myFunction) will give a "Argument not optional" error.

If you have an optional string argument and the argument is omitted, that argument will be set to the default; either the default from the function declaration statement or vbNullString (if no such default is specified).