Require atleast one optional function parameters

[JTBorton]

Excel 2010, even though this should really be done in Access

I am using a series of worksheets in place of a database since the people using it really do not want to use Access. I don't blame them either. A particular function gathers item data given the Item ID, but it can also accept the worksheet Row Index of the desired item. This way, if the parent function has already tested to make sure the desired item is valid and exists on the worksheet, the child function does not have to search for it a second time. I decided that both function parameters should be optional, because if both an ID and a Row Index are passed the ID should take precedence over the row index to ensure the correct data is returned. So if a row index is passed, the ID should not be passed with it.

So here is the question:
How can I require at least one parameter to be passed? I do not mean testing to make sure one of the parameters WAS passed when the child function is running. I want the debugger to catch a syntax error in case I, or another person editing the code, forget to pass at least one parameter (hey man, it happens).

(The function returns a user-defined structure, in case you are wondering what the heck fdbRcpComponentData is.)

Public Function GetRcpItemData(Optional ByVal RcpItemID As Integer = -1, Optional ByVal RowIndex As Integer = -1) As fdbRcpComponentData

    bla bla bla

End Function

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[MajP]

AFAIK, no such construct exists.

I would make two functions and force the user to choose. If they got an ID, then pass the ID if they have only the row then pass the row. Why would they ever pass two?

GetRcpItemFromID(RcpItemID As Integer)

GetRcpItemDataFromRow(ByVal RowIndex As Integer = -1)

 

[JTBorton]

MajP

That's probably the right way to go, I just didn't want to manage two nearly identical functions. That always leads to changing one and forgetting to change the other.

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[Gruuuu]

So have the two different functions call the same sub. Test the value of the parameter passed.

 

[Andrzejek]

How about, no optional parameters:

Public Function GetRcpItemData(ByVal ItemIDOrRowIndex As Integer, WhatIsIt As Integer) As fdbRcpComponentData

Select case WhatIsIt
    Case 1[green]
        'RcpItemID was passed to the Function[/green]
    Case 2[green]
        'RowIndex is here[/green]
End Select
[green]
'Here we know what was passed  :-)[/green]
    bla bla bla

End Function

Have fun.

---- Andy

 

[MajP]

Either suggestion probably does not what you are looking for. Sounds like you would like to have the capability to do overloaded functions, like you can do in java or C++. There you can name the same function multiple times each with different number and/or types of parameters. Unfortunately vb/vba does not have that construct.