VBA Input Pop up Boxes: Application.InputBox Method versus VBA.InputBox Function
Hi
Recently I have been trying to revise my knowledge of various ways to get simple user interaction with simple Pop ups. ( Later I will go into the “custom route” and learn about UserForms )
I started at a fairly ignorant level of knowledge and I thought the notes that I made for myself could be useful for someone learning about these things, so I am sharing them here. I have noticed some problems / Bug “features” , so I might try to extend the notes later to try to share an alternative semi – self made solution using the available collection of supplied with Windows small additional programs ( API, User 32 dll stuff ) that can also be accessed in a “user customerised” way in VBA.
But Initially , I am talking about these two things:
https://msdn.microsoft.com/en-us/vba...x-method-excel
https://msdn.microsoft.com/en-us/lib...ffice.11).aspx
https://msdn.microsoft.com/en-us/vba...utbox-function
https://powerspreadsheets.com/excel-vba-inputbox/ )
Bug Features
The comparison of these two similar things is complicated by various Bugs in Application.InputBox . Often when one gets caught out for the first time by such a Bug , then one learns of the two similar alternatives available in VBA to get an Input Pop up Box
I have never quite got a clear understanding or definition of the difference between a Method and a Function in these things. However in this case that is probably the key to understanding the difference.
My conclusion at the end is that the Application.InputBox Method is something of an abortion, and I hope to give later a simple alternative to one of its most popular uses, that of returning the range object of a selection made by the user in a worksheet.
Preparations Some preliminary stuff. ( Help File )
To help test check and understand the differences it is necessary to do some preliminary stuff which due to other Microsoft Bug features may not work.Never mind.
Follow the following instructions. You need to get a “working” Microsoft help type Window to pop up. If you cannot get it to work then never mind: It just means that you will have to forgo on a demo of one of the features.
So, here we go… Prepare a new File, or use a spare one. Important is that you save it as .xls, not .xlsm. It would be useful if you have access to two Excel versions:
Excel 2003 or earlier
and
Excel 2007 or later.
If not, no worries – you will just have to forgo on a few other experiments.
Having got a File saved somewhere ( it does not matter where it is saved ), then download this file:
https://app.box.com/s/bx2pkvtemsppscz60rd6f430wm89c6fj This is a “.chm Microsoft Help file” It has the name _ AnyFileName.chm
Important is to save it in the same folder as your file. Try and open that file
either
_1 directly
Or
_2 by double clicking on the file
GetOpen_1_2.JPG https://imgur.com/gRjn0HJ
Or
_3 Use this code
You should see this:Code:Sub HelpGetItUp_3() Application.Help HelpFile:=ThisWorkbook.Path & "\AnyFileName.chm", HelpContextID:=2 End Sub
HelpGetUpBollox.JPG https://imgur.com/KdKOYWr
If that does not work… you could try to sort it out based on the information here: https://www.excelforum.com/excel-new...ml#post4827566
If you can not get that to work, then no worries – you will just have to forgo on a few other experiments.
InputBox Method and Function. Method or Function
I think loosely a Function is defined as some sort of process, Sub code , set of instructions or some pre defined way of doing something. In normal life a Method is just another word for that. In programming the word Method might loosely be described as a Function “within” an Object and/ or a Function “using” things in an object. There lies the crux of the difference.
So if there is any truth to that last explanation, then the basic or more fundamental or more “lower level” of the two things can thought of as being the….
Input Box Function.
This returns a string of what the user types into the Pop up box which should come up when you use a code of this form:
Dim strReturned As String
_ Let strReturned = InputBox(Prompt:="Type Something in", Title:="MyBox", Default:="Something", xpos:=100, ypos:=100, HelpFile:=ThisWorkbook.Path & "\AnyFileName.chm", Context:=2)
A code like that should give something like this:
InputBoxFunctionWithHelpButton.JPG https://imgur.com/htritBi
As far as I know all is well understood and works with the VBA InputBox Function.
One thing worth having a quick play around with at this stage is those x y positional numbers
It should result in the box coming up in different places. In the next post I have a demo code, and the first short part, Rem 1 , has those code lines. So.. open your .xls file in Excel, hit Alt+F11 to get into the VB Editor, paste in that code into the large Code window, then run the code, preferably in Debug Mode, F8, for this initial selection. Then you can hit the stop button ( VBEditorStopButton.JPG https://imgur.com/0o8Ojqm ), edit those positional number values, the re run the code.
_._______________________
For Input Box Function You do not have any chance to select anywhere in the spreadsheet. You simply have to enter a string value into the Pop up Input Box Input Bar. The technical term here is along the lines of “The VBA InputBox Function is always Model” . In English that means you can’t do anything else in the worksheet until you either close the pop up Input Box or Hit the OK Button. Effectively the Excel Window is “hung up”, or “control is passed” to the Pop up Box
This is perhaps a very important point , particularly in discussions of the difference in the two
Application Input Method
From now on it is a bit of a complicated mess because of Bug features.
The basic idea is that within the object that is the current instance of the (Excel in this case ) object ( known loosely as the “Application” ) that the VBA InputBox Function is used somehow. That is to say , one might imply that the Application Input Method somehow uses the VBA InputBox Function “within it”, that is to say it is using for it data / information from the Application object.
I doubt anyone knows much better then that description for sure.
And I question whether there is any truth at all to that.
Clearly who ever “invented” it doesn’t know what is going on. If he or she did, then it might work consistently or the Bug features which have been around for about 10 years might have been fixed. I only have Excel 2003, 2007 and 2010. As far as I can see the Bug features started at Excel 2007 for the Positional arguments, and are still there in Excel 2010. If anyone following this and trying the experiments on a more recent version can add anything here then that would be welcome.
But the Help option arguments do not appear to work in any version for the Application.Input Box Method. So probably the Application Input Method was flaky right from the start and got worse as time went on.
Very briefly for now, and just as a quick approximate statement: The difference in the two things is that the Method has an extra last optional argument and if that is included then what is actually done is a bit more complicated ( I would suggest it is a mess actually – hence someone messed up and that causes the Bug features ).
Before we go full out in using the Application Input Method, consider the following that is often seen in Microsoft documentation:
……. that Application.InputBox calls the InputBox method; InputBox with no object qualifier calls the InputBox function.
I don’t think this makes any sense, or at least it is very unclear.
One reasonable interpretation is that the box that comes up probably does not have a lot to do with the VBA InputBox Function once the last optional argument of type is given. Or visa versa, a reasonable interpretation of that might reasonably be that without the extra last optional Type:= argument then effectively the InputBox function is done.
A short bit of experimenting suggest however that in any situation ( that is to say any argument selection used, even the minimum of just the first ( only not optional ) Prompt:= ) , something significantly different is going on with the Application.InputBox Method compared to the VBA InputBox Function.
Regardless of whether the last argument, Type:= , is given or not, clearly what happens is significantly different in the case of Application.InputBox Method compared to VBA InputBox Function
I did some experimenting, and my observations are that for any options combination you have, with Application.InputBox Method , an additional possibility to add data other than just entering into the Box input bar: You can select the spreadsheet: Although the code that fires up the Application.InputBox Method “waits” , it is possible to make a selection in the spreadsheet. If a selection is made then the address is shown in the absolute column letter and row number notation ( Like $A$1:$B$6 etc.. ) appears automatically in the pop up box input bar. It would appear that some coding determines what to return based on the area selected and the Type:= . ( If no Type:= is given the default type is taken as Text (a string), ( but as noted, the Application.InputBox Method does not then simply work as if it were the VBA InputBox Function )
A full code is given in the next post in which a section, Rem 3 , loops through types allowing an input for each type to be made. ( As is standard for the Application.InputBox Method you can make an entry by typing into the pop up box input bar, or make a spreadsheet selecting in which case the pop up box input bar is automatically filled with the selection address in the absolute column letter and row number notation.
Note that for the case of a returned range object, there will not be an error in assigning the returned thing to a variant ( Let Var = , or Var =) , ( rather than a more conventional in this case of Setting it to a range object) , as this is a typical case where VBA returns the default property of .Value from the range object. However the code has to do a bit of juggling about for this return and in the case of an Array return in order to show those values in a single displayable String
After selecting OK , a message box attempts to show what is actually returned from the Application.InputBox Method.
( If you have any difficulties with the help optional arguments_...
HelpFile:=ThisWorkbook.Path & "\AnyFileName.chm", Context:=2
or
HelpFile:=ThisWorkbook.Path & "\AnyFileName.chm", HelpContextID:=2
-…. Then if you cannot get the workarounds ( https://www.excelforum.com/excel-new...ml#post4827566 ) to work then simply remove them and ignore the help stuff )
_.______________
My guess is that there is a messy routine full of bug features which looks initially
either
at what is inputted by the user in the Input Box Input Bar,
or
if a selection is made by the user in a worksheet, the address is placed into the Input box, and a note is made of the actual range object that is referred to by the selection.
Thereafter an attempt is made to give a Type correct return. Depending on exactly what is inputted, this may or may not be accepted. This last point is reasonable I think.
As example: if type Number is specified in the options ( ____, Type:=1 ) _ ‘ Number ) then
an error occurs if a string is inputted or if a cell is referenced which has a string in it. A reference to an empty cell will result in _ 0 _ for this case. This is consistent with assigning a Number variable to the .Value of a range object.
If no string is entered into the Input Box Input Bar then Application.InputBox Method issues a pop up warning that an incorrect entry has been made. You are able then to correct this by adding a correct entry. It is not clear from this exactly what process then follows, but clearly it is a bit different to the user worksheet area selection case.
For all but the Type:= range and Type:= array , and Type:= formula, a multi cell selection results in just the top left cell being considered.
A full listing of what happens for different entries for each type would take a book. I might do that later. For now it is probably best to experiment with the code for the thing you are trying to do and variations thereof. Better still, don’t use the Application.InputBox Method as it is too flaky / full with bug features.
In the over next post I may try some alternatives, but not yet the UserForm. For now I am interested in the more standard/ simpler alternatives.
Demo code in next post and a few other places possibly: ( https://www.excelforum.com/developme...ml#post4829620
https://pastebin.com/58U8eagx )
References in the next post also.
Reply With Quote
Bookmarks