How to test optional parameter ?

Topic

New Reply

How do I determine whether an optional parameter was passed to a VBA/Excel 97 function ? The function below is supposed to look up a value in the previous sheet (default=same address). This works and will allow me to make a “worksheet chain” with little to no adaptations.

However, the second parameter is causing problems, see code. How to solve this ? Or is there a much simpler work-around than this, kind of “Offset accross worksheets” ?

Function ValPrevSheet(Optional SourceAddr As String, Optional InitVal As Variant) As Variant

Dim SheetNo As Integer, TargetAddr As String

With Application.Caller ‘cell which called function
TargetAddr = .Address
SheetNo = .Parent.Index ‘determine its worksheet _number_
If SourceAddr = “” Then SourceAddr = TargetAddr ‘define default
End With

‘Debugging only
MsgBox “Sheet ” & SheetNo & ” at ” & TargetAddr _
& ” to have value of previous sheet at ” & SourceAddr

If SheetNo = 1 Then ‘No previous sheet
‘See whether the default is given in the callup, if not ask for it
‘If IsEmpty(InitVal) = True Then ‘does not work, why ??
‘If Len(InitVal) = 0 Or Len(InitVal) = Null Then ‘does not work either
If InitVal = “” Then ‘does work but “” has to be passed, arghhhh…
MsgBox “Killroy was here” ‘Debugging only,
ValPrevSheet = InputBox(“Indicate init value for first sheet:”, “ValPrevSheet”)
Else
ValPrevSheet = InitVal
End If
Else
ValPrevSheet = Worksheets.Item(SheetNo – 1).Range(SourceAddr).Value
End If

End Function

Reply | Quote

Replies

Sorry, the indents where not taken

Reply | Quote

To keep indents, use the “pre” tags.

The trouble with those though, is that if you copy code from the forum straight into VBA, the “soft returns” which the PRE tags give you are not inserted.

You can put code into a “.txt” and attach it to the message- that would give you the best of both worlds

Or you could go through and change leanding spaces with the non-breaking space character

Reply | Quote

Just on the “pre” tags topic:

I found the same problem with copying pretagged code into the VBE, but found a simple workaround was to paste it into a plain Word document first, then copy that, and then paste it into the VBE – all the indents etc. then come in fine.

The most frequent problem I get with the pre tags are they sometimes omit plus symbols “+” – kind of a problem!

Attaching the .txt files seems like a good idea – will have to try that one.

Reply | Quote

That was Elaine’s idea to attach a text file- see my post in the Word forum for an example.

Aother way might be like this:
Option Explicit
Sub main()
Dim lPos As Long
Dim sText As String
Dim dlg As Dialog
sText = ActiveDocument.Sections(2).Range.Text
lPos = InStr(sText, vbCr)
If lPos = 0 Then
lPos = InStr(sText, vbLf)
End If
If lPos > 0 Then
sText = Mid$(sText, 1, lPos – 1)
End If

sText = ActiveDocument.BuiltInDocumentProperties(1) & “- ” & sText
If Len(sText) > 60 Then
sText = Mid$(sText, 1, 60)
End If
Set dlg = Dialogs(wdDialogFileSaveAs)
With dlg
.Name = Trim$(sText)
.Show
End With
End Sub

Where I used Word to change all occurrences of 2 adjacenet spaces with 2 non-breaking spaces before pasting in.

Reply | Quote

The way I’ve always tested for the existence of optional arguments (parameters) is to use the IsMissing function.

If IsMissing(NameofArg) = True Then …

The only drawback to using IsMissing is that you must make all the optional arguments Variants because it *only* works with the Variant data type.

Stephan

Reply | Quote

There’s a workarounf d for this in the help for “ismissing”:

Note IsMissing does not work on simple data types (such as Integer or Double) because, unlike Variants, they don’t have a provision for a “missing” flag bit. Because of this, the syntax for typed optional arguments allows you to specify a default value. If the argument is omitted when the procedure is called, then the argument will have this default value, as in the example below:

Sub MySub(Optional MyVar As String = “specialvalue”)
If MyVar = “specialvalue” Then
‘ MyVar was omitted.
Else
…
End Sub

In many cases you can omit the If MyVar test entirely by making the default value equal to the value you want MyVar to contain if the user omits it from the function call. This makes your code more concise and efficient.

Reply | Quote

Thanks to all who responded up so far. I could use the IsMissing but, as it was pointed out to me, there is the limitation of having make all optional parameters a variant. IMO comparing to a “specialvalue” i.e. a default can not be used _here_ because how do I set the default to “Zilch ?” (i.e. nothing at all, not even a “”).

Reply | Quote

cri,

As far a I can tell, you can’t. If you have string parameter, and it’s not specified, it gives you a blank string, not a null.

Can you use the variant though? You can use the syntax:

“if Typeof varName is String then”

after having checked the IsMissing, to check you have the datatype.

Not being aware of what the problem you’re trying to solve, I can’t see if I’m really addressing your problem- sorry if I’ve missed your point

Reply | Quote

It’s perfectly acceptable to set the default of a variant argument to Null like this:

Optional varNew As Variant = Null

In answer to your original post, IsEmpty only works when a variable hasn’t been initialized. With a variant, if you don’t specify the default, you have to test for IsMissing AND IsNull, since they could have either ignored the argument or passed a Null. All other variable types return their inherent default value or the value you set (i.e., “SpecialValue”) if the user doesn’t pass a value.

BTW, another way to test for null values is like this:

If Len(varNew & “”)>0 Then

Reply | Quote

Thanks to your help the whistles and bells of my code now work too:

‘See whether the default is given in the callup, if not ask for it
If IsMissing(InitVal) = True Then
ValPrevSheet = InputBox(“Indicate init value for first sheet:”, “ValPrevSheet”)
Else

My code is intended as workaround for a missing “Offset accross worksheets” or a kind of “Relative link”. Reason is I have a workbook with 12 worksheets, one for each month, which pass several values to the next month. To make the annual update easier and safer I want to be able to copy the revised sheet 11 times. Regular external links would have to be adapted or updated by VBA.

gwhitfield, sorry if my last remark came across as complaint, it was not meant this way.

Reply | Quote

cri,

I’m glad it’s working as you intended.

Thanks- but no, it didn’t come across as a complaint at all. I just had a feeling I was missing your point, that’s all. Sorry.

Reply | Quote

One thing to add:

Should you use this function, you _might_ need to add

Application.Volatile

to at the beginning of the code above to enforce the recalculation of the function calls. As rule of thumb check whether the target cell also is dependent from cells with calculated values and/or manual input, otherwise nothing short of Ctrl+Alt+F9 will cause the cell to refresh. As I was burnt by volatile functions once, I made a complementary Sub (triggered by the Workbook_SheetChange event) which _forwards_ the value (option links) of any monitored cell to the next worksheet.

Again, my thanks to all who responded.

Reply | Quote