Excel VBA: Can a sub call a UDF with unassigned arguments?

Question

I've written an Excel module to convert latitude/longitude to UTM coordinates. The main procedure is a sub that defines the arguments and calls the function that in turn calls a DLL function. The idea is the function opens the function argument window for the user to provide the data (function is defined in another sub with Application.MacroOptions).

Sub GeoToTM()

'Declare variables
Dim TM() As Variant
Dim Lat As Double
Dim Lon As Double
Dim TMProj As Integer
Dim Ellipsoid As Integer

'Call Excel function

TM = GeoConv(Lat, Lon, TMProj, Ellipsoid)

End Sub

'VBA function wrapper
Function GeoConv(Lat, Lon, TMProj, Ellipsoid) As Variant

Dim East As Double
Dim North As Double
Dim locTM(1 To 2) As Variant

'Call C++ function
GeodeticToTM Lat, Lon, TMProj, Ellipsoid, East, North

locTM(1) = East: locTM(2) = North
GeoConv = locTM

End Function

The function on its own performs as advertised but when I run the entire module the function is accessed but the function argument window does not appear; the arguments are never populated so the function returns zeros.

How do I get this to work? Is "Option" arguments the trick here?

Thanks,

Chris

If I understand what you're asking about, [you can't](http://stackoverflow.com/q/5282128/11683). — GSerg, Jul 15 '15 at 18:53

score 0 · Answer 1 · answered Jul 15 '15 at 19:16

You should be able to pass in optional values as parameters that default to a value that would never be used. If the values are those 'impossible' values then rewrite then with input boxes.

Sub GeoToTM()

    'Declare variables
    Dim s As String
    Dim TM() As Variant
    Dim Lat As Double
    Dim Lon As Double
    Dim TMProj As Integer
    Dim Ellipsoid As Integer

    'Call Excel function

    'with Lat and Lon as values then
    TM = GeoConv(TMProj, Ellipsoid, Lat, Lon)

    'with Lat and Lon unassigned
    TM = GeoConv(TMProj, Ellipsoid)

End Sub

'VBA function wrapper
Function GeoConv(TMProj As Integer, Ellipsoid As Integer, _
            Optional Lat As Double = -1, Optional Lon As Double = -1) As Variant

    Dim East As Double
    Dim North As Double
    Dim locTM(1 To 2) As Variant

    If Lat < 0 Then _
        Lat = CDbl(InputBox("Latitude: ", "Supply Latitude", "0.000"))
    If Lon < 0 Then _
        Lon = CDbl(InputBox("Latitude: ", "Supply Longtitude", "0.000"))

    'Call C++ function
    GeodeticToTM Lat, Lon, TMProj, Ellipsoid, East, North

    locTM(1) = East: locTM(2) = North
    GeoConv = locTM

End Function

Hm, there must be something else wrong with the function call and/or argument pass. Even with putting "Optional" on all the parameters with values (for ALL the arguments, not just Lat and Lon), I'm still getting zeros as a function return. I'm sure the fact the function argument window appears with a direct function call but not with a call from the sub is a clue ... — Chris Iriarte, Jul 15 '15 at 21:30

score 0 · Answer 2 · answered Jul 21 '15 at 21:45

Well, here's how I got it to work:

Rearranging the code, I ...

declared the "input" variables in the main sub,
selected the cell range,
put the Excel formula in the range with Selection.FormulaArray,
called the already populated function wizard with Application.Dialogs(xlDialogFunctionWizard).Show,
then called the Excel function.

So, the .Show allows the user to populate the arguments which then get passed to the Excel function/DLL function.

Sub GeoToTM()

'Declare variables
Dim TM() As Double
Dim Lat As Double
Dim Lon As Double
Dim TMProj As Integer
Dim Ellipsoid As Integer

Dim resultRng As Range
Dim Arg As Boolean

'Initialize Cells
ActiveCell.Offset(-1, 0).Value = "Easting"
ActiveCell.Offset(-1, 1).Value = "Northing"

'Call Excel function
Set resultRng = Range(ActiveCell, ActiveCell.Offset(0, 1))
resultRng.Select
Selection.FormulaArray = "= GeoConv()"
Arg = Application.Dialogs(xlDialogFunctionWizard).Show
If Arg = False Then Exit Sub

TM = GeoConv(Lat, Lon, TMProj, Ellipsoid)

End Sub

I actually had to remove "Option" from the arguments in the function so this would work, but it did!

Chris

Excel VBA: Can a sub call a UDF with unassigned arguments?

2 Answers2