How to use excel built-in constant in VBS [duplicate]

Solution 1:

Unfortunately these Named Constants are part of the Excel Object Library which VBScript has no way of referencing so the best approach is to lookup the named constants in Object Browser inside Excel VBA or online via various references then create your own named constants and use them in your code.

In this example you are using two enumerations that can be identified by looking up the Range.Find() method.

• xlValues is a named constant in the xlFindLookIn enumeration and has the value -4163.
• xlWhole is a named constant in the xlLookAt enumeration and has a value of 1.

So once you know the value you can define them and your code should work without any more changes being required.

Const xlValues = -4163
Const xlWhole = 1

Ideally these values should be declared in the global scope of your script so they are accessible to any function or procedure.

You might ask why not specify the numeric value? Well while this is indeed a valid approach if you use the value in multiple places you then have to modify that value in multiple places if the value ever changes (however unlikely in this scenario). With a named constant you make one change and wherever in your code that value is referenced is also changed.

It's also worth noting that VBScript is only interested in the numeric value so technically you can name the constants anything you wish. However, it is good practice to follow the naming convention, especially if you re-used the function code in Excel VBA for example.