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

by

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.

More Related Contents:

  1. Run Excel Macro from Outside Excel Using VBScript From Command Line
  2. How to set recurring schedule for xlsm file using Windows Task Scheduler
  3. Export each sheet to a separate csv file
  4. Code for looping through all excel files in a specified folder, and pulling data from specific cells
  5. Bad data selection using VBScript , on date field values
  6. Can I export excel data with UTF-8 without BOM?
  7. How do I use awk under cygwin to print fields from an excel spreadsheet?
  8. Print function selecting wrong Type of Change option excel vba
  9. What is the difference between .text, .value, and .value2?
  10. Setting mime type for excel document
  11. TEXTJOIN for xl2010/xl2013 with criteria
  12. What is the difference between “Form Controls” and “ActiveX Control” in Excel 2010?
  13. Check if the file exists using VBA
  14. How can I create a progress bar in Excel VBA?
  15. VBA rounding problem
  16. Pasting an Excel range into an email as a picture
  17. Excel VBA to Export Selected Sheets to PDF
  18. Excel 2013 VBA Clear All Filters macro
  19. How to convert a Google Docs-File to an Excel-File (XLSX)
  20. Rounding in MS Access
  21. Count unique values in Excel
  22. Sum function in VBA
  23. How can I reference a cell’s value in PowerQuery
  24. How to save Excel Workbook to Desktop regardless of user?
  25. How to count cells in a range with a value less than another cell in excel?
  26. Excel, save and close after run
  27. Excel: How to gather unique values in one column that are associated with duplicates in another column?
  28. Is it possible to see more than 65536 rows in Excel 2007? [closed]
  29. Excel: Find min/max values in a column among those matched from another column
  30. Access files with long paths (over 260)

Leave a Comment