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

tldr; There's no real risk to using StrPtr like that, but there's not really a benefit either.

While it might look like you get a null pointer back from the InputBox call, you actually don't. Compare the result of StrPtr to 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 InputBox is returning a Variant with a sub-type of VT_BSTR. This code demonstrates (note that I've declared result as a Variant so it doesn't get implicitly cast - more on this below):

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 reason why StrPtr returns 0 is because the return value of InputBox is actually malformed (I consider this a bug in the implementation). A BSTR is 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 InputBox is that the Variant that it's wrapped in contains a null pointer in 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, InputBox relies on the calling code to check that the data type (VT_BSTR) actually matches what is in the data area (VT_EMPTY or VT_NULL).

Checking the result as a StrPtr is 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 StrPtr has to perform 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 VarPtr actually 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).

So... it's really no different than using Len. Len just returns the value in the header of the BSTR (no, it doesn't count characters at all), and it also needs a null test for the similar reason that StrPtr does. It makes the logical conclusion that a null pointer has zero length - this is because vbNullstring is a null pointer:

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 why they 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 anything and hitting "Enter", there really isn't much point in using StrPtr(result) = 0 in favor of the much clearer Len(result) = 0 or result = vbNullString. I'd assert that if you need to make that distinction, you should throw together your own UserForm and explicitly handle cancellation and data validation in your own dialog.

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 StrPtr function, possibly how you use (or don't use) it without giving an opinion as to whether everyone or no one should use it.

There are three "hidden" functions: VarPtr, StrPtr and ObjPtr.

• VarPtr is used when you need to get the address of a variable (that is, the pointer to the variable).
• StrPtr is used when you need to get the address of the text data of a string (that is, the BSTR, a pointer to the first Unicode character of the string).
• ObjPtr is used when you need to get the address of an object (that is, the pointer to the object).

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 VarPtr when 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 StrPtr when 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).

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 InputBox situation you mention is one of the examples.

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

VBA.InputBox returns 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.

However, it is not documented that InputBox returns 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.

With this in mind, you make a decision on whether or not you are comfortable with using StrPtr on the InputBox result. If you are happy to take the very small risk of this behaviour changing in future and your app therefore breaking, you do use StrPtr, otherwise you switch to Application.InputBox that returns a Variant and is documented to return a False on a cancel.

But that decision will not be based on whether StrPtr is correct in what it tells you. It is. It is always safe to pass the String result of VBA.InputBox to it.

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

When someone insists that something should never be used, it's almost always wrong. Even GoTo has its correct uses.