How do I change a function's otional parameter default values programmically

[Michael]

I wrote a function in VBA that has two required parameters and two
optional parameters (which have default values set to defined
constants). I want to be able to have the user open a form that lets
them change the optional parameters default values, then store these
values in a table in the spreadsheat.

The problem is trying to get these values from the spreadsheet to the
optional parameter default values. If I read the optional default
values in from the spreadsheet, I have to redefine the default
constants as variables, and when I try to compile the code, I get
"Compile Error: Constant Expression Required", (which references the
optional paramter defaults in the function).

Any ideas on how to allow the user to change these optional parameter
default values without going into the VBA code?

~Michael

 

[Vasant Nanavati]

Hi Michael:

This seems to work for me (no error-checking provided):

Function SumFour(a As Integer, b As Integer, Optional c As Integer = 3, _
Optional d As Integer = 4)
If Range("A1") <> "" And Range("A2") <> "" Then
c = Range("A1")
d = Range("A2")
End If
SumFour = a + b + c + d
End Function

So if you have a UserForm to put values into the cells, those values will be
used as the optional parameters instead of your defaults.

Regards,

Vasant.

 

[Michael]

Vasant,

Thanks for the tip, the problem with the solution below is that the
two optional parameters values from the spreadsheet ALWAYS override
any data specifically passed into the function (c&d). I only want the
optional defaults to be used if NO DATA is passed into the optional
parameters of the function (c&d). For the two optional parameters,
one is type double, the other is type boolean, which doesn't allow me
to set the optional parameters to some recognizable dummy (to flag me
there was not a parameter passed so I can retrieve it from the
spreadsheat, for example, if the two optional parameters where
strings, I could set the optional default to "none passed", then use
an if-then statement to retrieve the defaults.)

Any more ideas?

~Michael

 

[Vasant Nanavati]

Hi Michael:

How about this?

Function SumFour(a As Integer, b As Integer, Optional c As Variant, _
Optional d As Variant)
If IsMissing(c) Then If Range("A1") <> "" Then c = Range("A1") Else c =
3
If IsMissing(d) Then If Range("A2") <> "" Then d = Range("A2") Else d =
4
SumFour = a + b + c + d
End Function

I'll leave it to you adjust the function for doubles and Booleans (use CDbl
and CBool if you need to coerce the parameters).

Regards,

Vasant.

 

[Michael]

Vasant,

Worked perfectly, a very sincere thanks. I spent hours chasing this
one in the wrong direction.

~Michael

 

[Vasant Nanavati]

Hi Michael:

You're most welcome.

Been there; done that many times! <g>

Regards,

Vasant.