The InputBox is one of the most commonly called functions in the whole of VBA. Whether you’re writing a complete database application in Microsoft Access, or just adding bit of functionality to your Microsoft Excel workbooks, the odds are that you won’t do much coding before you make your first call to the InputBox function.
So, I thought I’d dedicate this post to getting a little more from the InputBox – particularly in Excel, but also in Access.
When is an InputBox not an InputBox?
If you’re new to programming in Microsoft Excel, you may not be aware that there are, in fact, two different InputBoxes available to you in Excel.
Firstly, there’s the one you get with a bit of code like this:
Dim rng As Range
Dim strRangeAddress As String
strRangeAddress = InputBox(“Select the cells to be analyzed”)
Set rng = Range(strRangeAddress)
This is the standard VBA InputBox. It will look like this:
So, the user types A1, for example, in the box, and the last line in the sub sets the variable called rng to point to the range A1, ready for us to do whatever we want with it.
This kind of message box is fine for asking the user for some text. But look closely, and we actually ask the user to select the cells to be analysed. That’s all well and good, but they can’t actually do so! If they try to click on the worksheet in the background, they’ll be presented with a small “dong” sound, and that’s it – they can’t interact with anything else until they’ve closed the InputBox.
This is where the “other” InputBox comes in. Technically, the InputBox that we’ve used above is the VBA InputBox Function. If you just type InputBox as part of your code, that’s what you’ll get. But there’s also the InputBox method – it’s a method of Excel, and so must be called like this:
x=Application.InputBox(“Select the cells to be analyzed”)
At first glance, the code looks pretty similar – as does the end result that pops up on the screen for the end user. But there are a couple of key differences.
Firstly, I’ve been deliberately vague with regard to the variable to which I’m assigning the return value. In our original code above, I assigned the return value to a string, and then used that string as the address parameter of the Range function in order to convert it to a range. But this code would need some real error handling – what if the user didn’t select (or type the address of) a cell? What if they hit Cancel? What if they typed 7867, or “Pineapple” or “Bob”?
So, why the vague “x” variable? Because the Application.InputBox method doesn’t just return a string. It can do, but it can also return a Range object, a Boolean or a number.
All of which begs a question – if the Application.InputBox function can return all these different data types, how do we know what it actually will return?
The answer to that comes with the addition of an extra parameter for the InputBox method:
The purpose of this type parameter is to determine the type of the return value. So, in our case, we are asking the user to select the cells to be analyzed, so we’ll add “type:=8” to the end of our line, to specify that the return value will be a Range.
A quick look at Excel VBA’s help tells us that the following type parameter values are allowed:
|2||Text (a string)|
|4||A logical value (True or False)|
|8||A cell reference, as a Range object|
|16||An error value, such as #N/A|
|64||An array of values|
So now, because we can specify that we’re expecting the user to select a Range and that we therefore want to return a Range object, we don’t need to convert a string to a range with the Range function:
Dim rng As Range
Dim strRangeAddress As String
Set rng=Application.InputBox(Prompt:=”Select the range to be analyzed”, _ Type:=8)
This return type parameter allows us to do some validation without writing code to check data types in our Sub. For example, if we want the user to input a number, or to select a cell containing a number, we simply specify a Type parameter of 1. Anything that’s not a numeric value will then return a “Number is not valid” message to the user and will prompt them to try again.
But if we’d like to allow the user to enter (or select) either a number or text, we can combine the type values of 1 (number) or 2 (text) and provide the paramter “type:=3”.
There’s another advantage of this InputBox method over the standard VBA InputBox function: it’s not modal. If you’re not familiar with that term, a modal form or dialog box is one which, while on the screen, precludes any interaction with any other object. So, the fact that the InputBox function generates a Modal dialog box is what prevents us from selecting anything in the worksheet behind, meaning that we have to type “A1” or whatever in the InputBox.
But with the InputBox method, we can select a range in the worksheet, and the InputBox “sees” our selection:
What if the user cancelled the InputBox?
This is another difference between the InputBox function and the InputBox method.
With Excel’s Application.InputBox method, if the user cancels the InputBox, it returns False. So, we can track whether the user has cancelled the InputBox with code like this:
Dim varResponse as Variant
(“Select the cells to be analyzed”, Type:=8)
If varResponse = False Then
‘Do stuff here to handle the cancellation…
If you use the InputBox function, rather than the Application.InputBox method, it’s not quite so straightforward to spot when the user cancels the InputBox.
If the user clicks OK without entering a value, the return value will be an empty string (“”).
But if the user clicks cancel, the return value is… an empty string.
Using strptr() to check for InputBox cancellation.
From what we’ve seen so far, it should be obvious that there are lots of benefits of using the Application.InputBox method, rather than the VBA InputBox function.
But what about all the times when you can’t do that? For example, whenever you’re not using Excel? If you’re writing code in a Microsoft Access database, there’s no point in looking for the InputBox method – it’s a method of Excel. So, we’re left using the good old VBA InputBox function.
As noted above, one of the limitations of the VBA InputBox function is that it doesn’t return False when cancelled – it only ever returns a string. If the user enters text, of course that’s the string that gets returned. If the user enters nothing and clicks OK, the function returns an empty string – as it does if the user clicks Cancel.
But there is a way around this. By using the undocumented strptr() function, we can tell whether the empty string was returned as such by the InputBox because the user click OK when there was nothing in it, or the empty string exists because the user hit Cancel and so didn’t return a value of any kind.
Here’s how it works. The strptr() function must be passed a string as its parameter. It will then return the location in memory where that string is held. If the string has never held anything (technicall, it points to vbNullString), the strptr function will return a zero, but if the empty string is actually the “” result of the user clicking OK with an empty InputBox, the string will actually exist (even if empty) and so the strptr() function will return a value other than zero.
Here’s the sort of thing you may want to do. This example is taken from a database, part of which asks the user for a date, and checks if the date is valid. If the user clicks Cancel the zero length string returned will not be a valid date, but the code does a check for whether Cancel was pressed, via our strptr() function, and if so, assumes that the user didn’t mean to initiate this procedure, returning a -1 from the function:
One word of warning…
The strptr() function can be really useful when you need to check for an InputBox being cancelled, and you have to use the Function, rather than the Excel method. But I mentioned earlier that the strptr() function is “undocumented”. What does this mean? Well, apart from anything else, it meant that there’s no help for it in the VBA online help, and you won’t be able to find it in the Object Browser.
But it also means that – according to Microsoft – it is essentially unsupported, and could be dropped from any future update to VBA. So, it may be useful now, but it may equally be gone in Access 15, whenever that makes it to the shops. And if not then, it could just as easily be gone in Access 16. Or some other update.
You have been warned!