在 VBA 中使用 StrPtr 函数的好处和风险是什么？

What are the benefits and risks of using the StrPtr function in VBA?

提问by ChrisB

While looking for a way to test when a user cancels an InputBox, I stumbled across the StrPtrfunction. I believe it checks if a variable was ever assigned a value and returns zero if it was never assigned and some cryptic number if it was.

在寻找一种测试用户何时取消 的方法时InputBox，我偶然发现了该StrPtr功能。我相信它会检查一个变量是否曾经被赋值，如果它从未被赋值，则返回零，如果是，则返回一些神秘的数字。

It seems like a useful function! I started with this code:

好像是个有用的功能！我从这个代码开始：

Dim myVar as string
myVar = InputBox("Enter something.")
MsgBox StrPtr(myVar)

The message box shows a zero if the user cancelled.

如果用户取消，消息框显示零。

Fantastic! But then why do some insist that StrPtrnever be used? I read it's unsupported. Why does that matter?

极好的！但是为什么有些人坚持StrPtr从不使用呢？我读到它不受支持。为什么这很重要？

A good answer will explain benefits (beyond my example above) and risks of using the StrPtrfunction, possibly how you use (or don't use) it without giving an opinion as to whether everyone or no one should use it.

一个好的答案将解释使用该StrPtr功能的好处（超出我上面的示例）和风险，可能是您如何使用（或不使用）它，而无需就是否每个人都应该使用它发表意见。

回答by Comintern

tldr;There's no real risk to using StrPtrlike that, but there's not really a benefiteither.

tldr; StrPtr那样使用没有真正的风险，但也没有真正的好处。

While it might look like you get a null pointer back from the InputBoxcall, you actually don't. Compare the result of StrPtrto VarPtr:

虽然看起来您从InputBox调用中得到了一个空指针，但实际上并没有。比较StrPtrto的结果VarPtr：

Sub Test()
    Dim result As String
    result = InputBox("Enter something.")        'Hit cancel
    Debug.Print StrPtr(result)                   '0
    Debug.Print VarPtr(result)                   'Not 0.
End Sub

That's because InputBoxis returning a Variantwith a sub-type of VT_BSTR. This code demonstrates (note that I've declared resultas a Variantso it doesn't get implicitly cast - more on this below):

那是因为InputBox正在返回Variant子类型为 的VT_BSTR。此代码演示（请注意，我已将其声明result为 a，Variant因此它不会被隐式转换 - 下面将详细介绍）：

Sub OtherTest()
    Dim result As Variant
    result = InputBox("Enter something.")   'Hit cancel
    Debug.Print StrPtr(result)              '0
    Debug.Print VarPtr(result)              'Not 0.
    Debug.Print VarType(result)             '8 (VT_BSTR)
    Debug.Print TypeName(result)            'String
End Sub

The reasonwhy StrPtrreturns 0 is because the return value of InputBoxis actually malformed (I consider this a bug in the implementation). A BSTRis an automation type that prefixes the actual character array with the length of the string. This avoids one problem that a C-style null terminated string presents automation - you either have to pass the length of the string as a separate parameter or the caller won't know how large to size a buffer to receive it. The problem with the return value of InputBoxis that the Variantthat it's wrapped in contains a null pointerin the data area. Normally, this would contain the string pointer - the caller would dereference the pointer in the data area, get the size, create a buffer for it, and then read the N bytes following the length header. By passing a null pointer in the data area, InputBoxrelies on the calling code to check that the data type (VT_BSTR) actually matcheswhat is in the data area (VT_EMPTYor VT_NULL).

的原因为何StrPtr返回0是因为返回值InputBox实际上是畸形的（我认为这是在实现中的错误）。甲BSTR是前缀的实际字符数组与字符串的长度的自动化类型。这避免了 C 风格的空终止字符串呈现自动化的一个问题——您要么必须将字符串的长度作为单独的参数传递，要么调用者不知道接收它的缓冲区有多大。有返回值的问题InputBox在于Variant，它的包裹在包含空指针在数据区。通常，这将包含字符串指针 - 调用者将取消引用数据区域中的指针，获取大小，为其创建缓冲区，然后读取长度标头后面的 N 个字节。通过在数据区中传递一个空指针，InputBox依赖调用代码来检查数据类型 ( VT_BSTR) 是否实际匹配数据区 (VT_EMPTY或VT_NULL) 中的内容。

Checking the result as a StrPtris actually relying on that quirk of the function. When it's called on a Variant, it returns the pointer to the underlying string stored in the data area, and it offsets itself by the length prefix to make it compatible with library functions that require a C-string. That means the StrPtrhas toperform a null pointer check on the data area, because it's not returning a pointer to the start of the actual data. Also, like any other VARTYPE that stores a pointer in the data area, it has to dereference twice. The reason VarPtractually gives you a memory address is that it gives you the raw pointer to whatever variable you pass it (with the exception of arrays, but that's not really in scope here).

检查结果作为 aStrPtr实际上依赖于函数的那个​​怪癖。当它在 a 上被调用时Variant，它返回指向存储在数据区中的底层字符串的指针，并用长度前缀偏移自己，使其与需要 C 字符串的库函数兼容。这意味着StrPtr必须对数据区域执行空指针检查，因为它不会返回指向实际数据开头的指针。此外，与在数据区中存储指针的任何其他 VARTYPE 一样，它必须取消引用两次。VarPtr实际上为您提供内存地址的原因是它为您提供了指向您传递给它的任何变量的原始指针（数组除外，但这实际上不在此处的范围内）。

So... it's really no different than using Len. Lenjust returns the value in the header of the BSTR(no, it doesn't count characters at all), and it alsoneeds a null test for the similar reason that StrPtrdoes. It makes the logical conclusion that a null pointerhas zero length - this is because vbNullstringisa null pointer:

所以......这与使用Len. Len只返回头部中的值BSTR（不，它根本不计算字符），并且出于类似的原因，它还需要一个空测试StrPtr。它得出了一个空指针长度为零的逻辑结论——这是因为它vbNullstring是一个空指针：

Debug.Print StrPtr(vbNullString) '<-- 0

That said, you're relying on buggy behavior in InputBox. If Microsoft were to fix the implementation (they won't), it would break your code (which is whythey won't). But in general, it's a better idea to not rely on dodgy behavior like that. Unless you're looking to treat the user hitting "Cancel" differently than the user not typing anythingand hitting "Enter", there really isn't much point in using StrPtr(result) = 0in favor of the much clearer Len(result) = 0or result = vbNullString. I'd assert that if you need to make that distinction, you should throw together your own UserFormand explicitlyhandle cancellation and data validation in your own dialog.

也就是说，您依赖于InputBox. 如果微软要修复实现（他们不会），它会破坏你的代码（这就是他们不会的原因）。但总的来说，最好不要依赖这样的狡猾行为。除非您希望将点击“取消”的用户与不输入任何内容并点击“回车”的用户区别对待，否则使用StrPtr(result) = 0更清晰的Len(result) = 0或result = vbNullString. 我断言，如果您需要进行区分，您应该将自己的内容放在一起UserForm，并在您自己的对话框中明确处理取消和数据验证。

回答by GSerg

I find the accepted answer to be rather misleading, so I was compelled to post another one.

我发现接受的答案相当具有误导性，所以我不得不发布另一个。

A good answer will explain benefits (beyond my example above) and risks of using the StrPtrfunction, possibly how you use (or don't use) it without giving an opinion as to whether everyone or no one should use it.

一个好的答案将解释使用该StrPtr功能的好处（超出我上面的示例）和风险，可能是您如何使用（或不使用）它，而无需就是否每个人都应该使用它发表意见。

There are three "hidden" functions: VarPtr, StrPtrand ObjPtr.

共有三个“隐藏”函数：VarPtr、StrPtr和ObjPtr。

• VarPtris used when you need to get the address of a variable(that is, the pointer to the variable).
• StrPtris used when you need to get the address of the text dataof a string (that is, the BSTR, a pointer to the first Unicode character of the string).
• ObjPtris used when you need to get the address of an object(that is, the pointer to the object).

• VarPtr当需要获取变量的地址（即指向变量的指针）时使用。
• StrPtr当您需要获取字符串的文本数据的地址时使用（即BSTR，指向字符串的第一个 Unicode 字符的指针）。
• ObjPtr当需要获取对象的地址（即指向对象的指针）时使用。

They are hidden because it may be unsafe to mess around with pointers.
But you cannot go completely without them.

它们是隐藏的，因为乱用指针可能不安全。
但是你不能完全没有它们。

So, when do you use them?
You use them when you need to do what they do.

那么，你什么时候使用它们？当您需要做它们所做的事情时，您
可以使用它们。

You use VarPtrwhen your problem in hand is "I need to know the address of that variable" (e.g. because you want to pass that address to CopyMemory).
You use StrPtrwhen your problem in hand is "I need to know the address of the first character of my BSTR string" (e.g. because you want to pass it to an API function that accepts wide strings only, but if you simply declare the parameter As String, VB will convert the string into ANSI for you, so you have to pass StrPtr).
You use ObjPtrwhen your problem in hand is "I need to know the address of that object" (e.g. because you want to examine its vtable or manually check if the object address does or does not equal some value you knew previously).

VarPtr当您手头的问题是“我需要知道该变量的地址”时，您可以使用（例如，因为您想将该地址传递给CopyMemory）。当您手头的问题是“我需要知道我的 BSTR 字符串的第一个字符的地址”时，您
可以使用StrPtr（例如，因为您想将它传递给只接受宽字符串的 API 函数，但如果您只是声明参数As String， VB 会为您将字符串转换为 ANSI，因此您必须通过StrPtr)。当您手头的问题是“我需要知道那个对象的地址”时，您
可以使用ObjPtr（例如，因为您想检查它的 vtable 或手动检查对象地址是否等于或不等于您以前知道的某个值）。

These functions correctly do what they are supposed to do, and you should not be afraid to use them for their intended purpose.

这些函数正确地完成了它们应该做的事情，您不应该害怕将它们用于预期目的。

If your task in hand is different, you probably should not be using them, but not out of fear that they will return a wrong value - they will not.

如果您手头的任务不同，您可能不应该使用它们，但不要担心它们会返回错误的值——它们不会。

---

---

In a perfect world, you would stop at that conclusion. That is not always possible, unfortunately, and the InputBoxsituation you mention is one of the examples.

在一个完美的世界中，你会停止在那个结论上。不幸的是，这并不总是可能的，InputBox您提到的情况就是其中一个例子。

From what is outlined above, it would appear that you should notbe using StrPtrto determine if Cancel was pressed in an InputBox. Realistically though, you don't have a choice.

从上面概述的内容来看，您似乎不应该使用StrPtr来确定是否在InputBox. 但实际上，你别无选择。

VBA.InputBoxreturns a String. (This fact is incorrectly omitted from the current documentation making it look like it returns a Variant.) It is perfectly okay to pass a string to StrPtr.

VBA.InputBox返回一个String. （当前文档中错误地省略了这一事实，使其看起来像返回Variant.）将字符串传递给StrPtr.

However, it is not documented that InputBoxreturns a null pointer on a cancel. It is merely an observation. Even though realistically that behaviour will never change, theoretically it may in a future version of Office. But that observation is all you have; there is no documented return value for a cancel.

但是，没有记录InputBox在取消时返回空指针。这只是一个观察。尽管实际上这种行为永远不会改变，但理论上它可能会出现在未来版本的 Office 中。但这种观察就是你所拥有的；没有记录的取消返回值。

With this in mind, you make a decision on whether or not you are comfortable with using StrPtron the InputBoxresult. If you are happy to take the very smallrisk of this behaviour changing in future and your app therefore breaking, you do use StrPtr, otherwise you switch to Application.InputBoxthat returns a Variantand is documentedto return a Falseon a cancel.

考虑到这一点，您可以决定是否StrPtr对InputBox结果感到满意。如果您愿意承担这种行为在未来发生变化的很小风险并且您的应用程序因此而中断，您确实使用StrPtr，否则您切换到Application.InputBox返回 aVariant并记录为False在取消时返回 a 。

But that decision will not be based on whether StrPtris correct in what it tells you. It is. It is always safe to pass the Stringresult of VBA.InputBoxto it.

但该决定不会基于StrPtr它告诉您的内容是否正确。这是。将String结果传递VBA.InputBox给它总是安全的。

---

---

Fantastic! But then why do some insist that StrPtrnever be used? I read it's unsupported. Why does that matter?

极好的！但是为什么有些人坚持StrPtr从不使用呢？我读到它不受支持。为什么这很重要？

When someone insists that something should neverbe used, it's almost always wrong. Even GoTohas its correct uses.

当有人坚持的东西应该永远不会被使用，它几乎总是错的。甚至GoTo有其正确的用途。

回答by Bob

Read through this thread and ultimately ended up doing the following... which does exactly what I want.... If the user deletes the previous entry which is the default... and clicks ok.. it moves forward and deletes the back end data ( not shown ). If the user click's cancel, it exists the sub without doing anything. This is the ultimate objective and... this allows it to work as intended... Move forward unless cancel is clicked.

通读此线程并最终完成以下操作......这正是我想要的......如果用户删除了默认的前一个条目......并单击确定......它向前移动并删除后面结束数据（未显示）。如果用户点击取消，它会在不做任何事情的情况下存在子。这是最终目标，并且...这使它能够按预期工作...除非单击取消，否则继续前进。

hth, ..bob

嗯，..鲍勃

Dim str As String

If IsNull(Me.Note) = False Then
   str = Me.Note
Else
    str = "Enter Note Here"
End If

Dim cd As Integer
cd = Me.ContractDetailsID

str = InputBox("Please Enter Note", "NOTE", str)


If StrPtr(str) = 0 Then
    Exit Sub    'user hit cancel
End If

回答by Sumit Pokhrel

I tired both using StrPtrand without using StrPtr. I tested my Sub with several examples. I got same results except in one occasion - When User inputs null value (nothing) and presses OK. Precisely I tried these two:

使用StrPtr和不使用StrPtr. 我用几个例子测试了我的 Sub。我得到了相同的结果，除了在一种情况下 - 当用户输入空值（无）并按 OK 时。正是我尝试了这两个：

Using StrPtr. "Invalid Number" was the result here

使用 StrPtr。“无效号码”是这里的结果

    ElseIf StrPtr(Max_hours_string) = 0 
            MsgBox "Cancelled"
    Else
            MsgBox "Invalid Number"

Without Using StrPtr. "Cancelled" was the result here

不使用 StrPtr。“取消”是这里的结果

 ElseIf Max_hours_string = "" Then
        MsgBox "Cancelled"
 Else
        MsgBox "Invalid Number"

This is my code.

这是我的代码。

Sub Input_Max_Hours_From_The_User()
        'Two Common Error Cases are Covered:
        '1. When using InputBox, you of course have no control over whether the user enters valid input.
        '        You should store user input into a string, and then make sure you have the right value.
        '2. If the user clicks Cancel in the inputbox, the empty string is returned.
                 'Since the empty string can't be implicitly coerced to a double, an error is generated.
                 'It is the same thing that would happen if the user entered "Haughey" in the InputBox.

     Dim Max_hours_string As String, Max_hours_double As Double
        Max_hours_string = InputBox("Enter Maximum hours of any Shift")
        If IsNumeric(Max_hours_string) Then
            Max_hours_double = CDbl(Max_hours_string) 'CDbl converts an expression to double
            Range("L6").Value = Max_hours_double
            Range("L6").Interior.ColorIndex = 37
        ElseIf StrPtr(Max_hours_string) = 0 Then 'ElseIf Max_hours_string = "" Then MsgBox "Cancelled"  also works !
            MsgBox "Cancelled"
        Else
            MsgBox "Invalid Number"
        End If

    End Sub

So I think it depends how important it is to handle the null value for you. All other test cases, including pressing Cancel, non-numerical inputs etc. give the same results. Hope this helps.

所以我认为这取决于为您处理空值的重要性。所有其他测试用例，包括按取消、非数字输入等，都会给出相同的结果。希望这可以帮助。

回答by excelledsoftware

In my opinion: Using StrPtrin order to identify if a value converts to 0 is extra code to write. if you use the following function like your example above

在我看来：使用StrPtr以确定值是否转换为 0 是要编写的额外代码。如果您像上面的示例一样使用以下函数

Sub woohoo()
    Dim myVar As String
    myVar = "hello"
    myVar = InputBox("Enter something.")
   'if Cancel is hit myVar will = "" instead of hello.
    'MsgBox StrPtr(myVar) not needed
    MsgBox myVar 'will show ""
End Sub

Now is this the only reason to not use StrPtrno not at all. The other issue you run into with using unsupported functions is that eventually they can break the application. Whether its a library issue or another programmer looking through your code and trying to find that function it just is not a good idea. This may not seem like a big deal if your script is only 100 lines long. But what about when it is thousands of lines long. If you have to look at this code 2 years down the road because something broke it would not be very fun to have to find this magical function that just does not work anymore and try to figure out what it did. Lastly especially in VBA you can get overflow errors. If StrPtris used and it goes past the allocated space of your data type that you declared it's another unnecessary error.

现在这是不使用StrPtrno not的唯一原因。使用不受支持的函数时遇到的另一个问题是它们最终会破坏应用程序。无论是库问题还是其他程序员查看您的代码并试图找到该功能，这都不是一个好主意。如果您的脚本只有 100 行长，这似乎没什么大不了的。但是当它有数千行时呢。如果您必须在 2 年后查看这段代码，因为某些事情发生了故障，那么必须找到这个不再起作用的神奇函数并试图弄清楚它做了什么，这将不是很有趣。最后，尤其是在 VBA 中，您可能会遇到溢出错误。如果StrPtr使用并且它超出了您声明​​的数据类型的分配空间，则这是另一个不必要的错误。

Just my 2 cents but due to being able to use less code and the function being more stable without it I would not use it.

只是我的 2 美分，但由于能够使用更少的代码并且没有它的功能更稳定，我不会使用它。

10+ years Excel Programmer.

10 年以上 Excel 程序员。