PDA

View Full Version : Is Late-binding possible in VBA?



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!!

Kenneth Hobs
04-28-2011, 07:22 PM
Yes, late binding can be used for objects.

In your case, use Indirect() to build the variable name using strings.