如何在VBS中使用excel内置常量 [英] How to use excel built-in constant in VBS

问题描述

我想使用VBScript来处理EXCEL而不是VBA。但是当调用Range.Find方法时遇到代码错误，请参见下文

I want to use VBScript to process EXCEL instead of VBA. But I encountered code errors when calling method Range.Find, see below

Function find_range(wb, domain, var)
    Dim sheet
    Dim rg1, rg2, rg3
    Set sheet = wb.Sheets(domain)

    Set rg1 = sheet.Range("D:D").Find(var, , xlValues, xlWhole )

    If Not rg1 is Nothing Then
        'msgbox rg1.Cells(1,1).row
        Set rg2 = sheet.Cells(rg1.Cells(1,1).row, 19)
        msgbox(rg2.value)
    End if
End Function

当执行find_range函数时，我得到

when executed find_range function, I got

变量未定义xlValuesxlWhole

variable is undefined "xlValues" "xlWhole"

错误。

所以我想我不能在VBScript中使用excel这样的内置常量。

那么正确的方法是什么？ >

So I guess that I cannot just use excel built-in constants this way in VBScript.
So what is the correct way?

推荐答案

不幸的是，这些命名常量是Excel对象库的一部分，VBScript没有方法引用，所以最好的方法是查找命名常量在Excel VBA中的对象浏览器中或通过各种引用在线，然后创建您自己的命名常量，并在代码中使用它们。

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.

在此示例中，您使用两个可以通过查看 Range.Find（）方法。

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

• xlValues 是一个在 xlFindLookIn 枚举，并具有值 -4163 。


• xlWhole 是 xlLookAt 枚举，值为 1 。



• 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.

还值得注意的是，VBScript只对数值感兴趣从技术上讲，您可以为常数命名任何您想要的。但是，遵循命名约定是很好的做法，特别是如果您在Excel VBA中重新使用了功能代码。

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.

这篇关于如何在VBS中使用excel内置常量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！