CareerChange
04-28-2011, 06:15 PM
Please consider this code snippet...
Public Const _
ciLeftItem As Integer = 1, _
ciRightItem As Integer = 2
<< some code >>
Call SubX()
Call SubX("Right")
<< some code >>
Sub SubX(Optional sSide as String = "Left")
Selection.AutoFilter Field:="ci" & sSide & "Item", _
Criteria1:="=N/A", Operator:=xlOr, _
Criteria2:="="
End Sub
Prior to an enhancement request, "SubX()" did not have any input parameters and I hard-coded ciLeftSide into the AutoFilter method which correctly passed the Integer constant value of 1, and everything worked fine.
Because "SubX()" is a pretty large method and is used extensively in this application, I wanted to avoid coding "IIf" or "If" logic when I needed to pass in the correct, albeit dynamic constant. I've done late-binding in other languages, so I (unsuccessfully) tried how I thought that it might be done in VBA and to no one's surprise other than my own, I ended up passing a String called "ciLeftItem" (or "ciRightItem" depending on the usage) into the AutoFilter() instead of passing a late-bound Integer as I had hoped... not good.
Do I have any options other than "IIf"/"If" logic to pull this off?
Thank you!!
Public Const _
ciLeftItem As Integer = 1, _
ciRightItem As Integer = 2
<< some code >>
Call SubX()
Call SubX("Right")
<< some code >>
Sub SubX(Optional sSide as String = "Left")
Selection.AutoFilter Field:="ci" & sSide & "Item", _
Criteria1:="=N/A", Operator:=xlOr, _
Criteria2:="="
End Sub
Prior to an enhancement request, "SubX()" did not have any input parameters and I hard-coded ciLeftSide into the AutoFilter method which correctly passed the Integer constant value of 1, and everything worked fine.
Because "SubX()" is a pretty large method and is used extensively in this application, I wanted to avoid coding "IIf" or "If" logic when I needed to pass in the correct, albeit dynamic constant. I've done late-binding in other languages, so I (unsuccessfully) tried how I thought that it might be done in VBA and to no one's surprise other than my own, I ended up passing a String called "ciLeftItem" (or "ciRightItem" depending on the usage) into the AutoFilter() instead of passing a late-bound Integer as I had hoped... not good.
Do I have any options other than "IIf"/"If" logic to pull this off?
Thank you!!