Solved: Tell Me About the StrPtr Function

[Cyberdude]

there's the StrPtr function that can help you to determine if user pressed cancel. See below

VBA:

Sub CheckIfCancelWasPressed()

Dim Response As String
Response = InputBox("Your string here:")
If StrPtr(Response) = 0 Then MsgBox "Cancel was pressed!"
End Sub

This is super cool! Where is the "StrPtr" function described? Help didn't seem to know anything about it, but when I used it, it worked.

[Bob Phillips]

StrPtr (like VarPtr, VarPtrArray, VarPtrStringArray, and ObjPtr) is an undocumented function that is used to get the underlying memory address of variables.

As it is undocumented, I would suggest not using it, it certainly isn't needed where ALe used it.

[vba]

Sub CheckIfCancelWasPressed()
Dim Response As String
Response = InputBox("Your string here:")
If Response = "" Then MsgBox "Cancel was pressed!"
End Sub
[/vba]

[Cyberdude]

Actually the code change you showed is what I have always used, except I use vbNullString instead of "". It looked like maybe StrPtr might have some neat characteristics to explore. Oh, well ...

Thanks xld!

[johnske]

Actually the code change you showed is what I have always used, except I use vbNullString instead of "". It looked like maybe StrPtr might have some neat characteristics to explore. Oh, well ...

Thanks xld!

If anything's undocumented then at any time in the future it's use may be unsupported.

Also, check out the Help files: vbNullString - String having value 0 (Not the same as a zero-length string ("")) used for calling external procedures.


EDIT: Also, if it's really an issue as to whether the user presses OK or cancel for an empty string entry, you should be using the InputBox method and not the InputBox function because the method is specifically designed to provide selective validation of user input where-as the function is not. e.g. [vba]Sub CheckIfCancelWasPressed2()
Dim Response As String
Response = Application.InputBox("Your string here:", , , , , , , 1 + 2)
If Response = "False" Then MsgBox "Cancel was pressed!"
End Sub[/vba](you'll notice here that Visual Basic is able to discern the "False" generated by clicking the Cancel button as being different to a "False" being typed in the input box as an entry )

[Bob Phillips]

If anything's undocumented then at any time in the future it's use may be unsupported.

It is already gone in VB 2005.

[malik641]

EDIT: Also, if it's really an issue as to whether the user presses OK or cancel for an empty string entry, you should be using the InputBox method and not the InputBox function because the method is specifically designed to provide selective validation of user input where-as the function is not. e.g. [vba]Sub CheckIfCancelWasPressed2()
Dim Response As String
Response = Application.InputBox("Your string here:", , , , , , , 1 + 2)
If Response = "False" Then MsgBox "Cancel was pressed!"
End Sub[/vba](you'll notice here that Visual Basic is able to discern the "False" generated by clicking the Cancel button as being different to a "False" being typed in the input box as an entry )

I don't understand where there's a point to having both a function AND a method for InputBox.

[johnske]

I don't understand where there's a point to having both a function AND a method for InputBox.

The InputBox "function" is a broader 'general purpose' or 'cross-application' VBA function that's part of the VBA model. It's also available for use in (for example) Word as well as Excel.

On the other hand, the InputBox "method" (Application.InputBox, or more specifically, Excel.Application.InputBox) is quite specific to the Excel object model and can't be used in Word.

The generic "function" is only designed to handle strings, where-as the more specific "method" is designed to allow for data validation of user input in Excel.


EDIT: Because the 'method' is designed specifically for Excel then, by rights, we should only ever use the InputBox method for Excel. But often the 'function' will suffice for the job at hand (and we all get lazy from time to time don't we? )

[malik641]

Thanks for clearing that up John

So I guess the InputBox function is native to VB?

(and we all get lazy from time to time don't we? )

Of COURSE!!!!!

[Cyberdude]

Regarding the use of vbNullString instead of "", at one time there was a lot of discussion on this topic. The conclusion was that it is more efficient to use vbNullString because (as I recall) the "" creates a seperate constant for each occurrence, whereas vbNullString doesn't create a constant.

I am quite surprised at your dissenting comments, John, because I thought it had been made very clear that vbNullString is preferable. Hmmmm.

[johnske]

vbNullString - String having value 0 (Not the same as a zero-length string ("")) used for calling external procedures

Didn't mean to start any arguments, but this was a quote straight out of the VBA help files

[Cyberdude]

It's not a matter of arguing ... I'm just trying to get to the truth of the matter. I remember being referred to another link that went into detail defending vbNullString. The claim was (as I remember) that this was a Microsoft recommendation. Sorry, but I can't remember the details. Well, it's not a biggy, although I've been using it with no problems. Hmmmm.

[ALe]

StrPtr (like VarPtr, VarPtrArray, VarPtrStringArray, and ObjPtr) is an undocumented function that is used to get the underlying memory address of variables.

As it is undocumented, I would suggest not using it, it certainly isn't needed where ALe used it.

VBA:
Sub CheckIfCancelWasPressed() Dim Response As String Response = InputBox("Your string here:") If Response = "" Then MsgBox "Cancel was pressed!" End Sub

The advantage of StrPtr is that it tells you "Cancel" was pressed. With other solutions, if the inputbox is left blank, you can't so easily understand wheter "OK" or "Cancel" was pressed.

[Bob Phillips]

But the HUGE disadvantage is that because it is unsupported, you cannot guarantee it is in the next release. One day your perfectly working code stops working.

[ALe]

Yes it is true, it is a temporary solution and shouldn't be used for "long run projects".

BTW as Keynes said "In the long run we are all dead"

[Bob Phillips]

Yes it is true, it is a temporary solution and shouldn't be used for "long run projects".

BTW as Keynes said "In the long run we are all dead"

But in the medium run, we want life to be as pleasant as possible.

[abbeyWigan]

But in the medium run, we want life to be as pleasant as possible.

Folks, I know this is a very very old old thread (at least in coding timescale) but having just discovered it and read through it all, I thought it's worth adding my bit. Everybody has argued the issue from a particular perspective that suits them but I don't think anyone has from my perspective.

Yes Application.InputBox is all singing and all dancing and does everything that you would like to do with InputBox. I quite like its compactness too. However, it has annoyed me too many times. Have you ever tried to edit any text in it by using any of the navigation keys - all 10 of them? It immediately starts populating the box with cell addresses and formulae and basically ruins the text you're trying to edit. I have not found any way of turning this behaviour off. If anyone does, please tell me.

Plain InputBox on the other hand doesn't know the difference between a Cancel button and a null string which is a pain when you need to know whether the user has truly cancelled or just got trigger happy and returned a blank. Here, StrPtr comes to the rescue and I like it. So I'm using it.

Like ALe said: "In the long run, we're all dead. In the meantime, we should make life as pleasant as possible." I will use it and trap any possible future errors it might throw up, enjoy my code and leave the rest to fate (aka MicroSoft). Hey! It still works in Excel 2010. Not tried it yet in 2016 but will shortly. If it's aint dead yet, may be it won't.

[mdmackillop]

Have you tried F2 prior to editing?

[leemoknows]

I was just searching for a way to handle cancels with an InputBox and found this thread. I'm using VBA with Access 2010 and the strptr() function still works in this version. I assume this function is part of the VB for Applications reference..? Anyway, I thought it was funny that it still works LOL.