VBA Input Pop up Boxes: Application.InputBox Method versus VBA.InputBox Function

**DocAElstein** · 02-09-2018, 02:19 AM

Section 3a) would be your main code in which you wanted to use / call up the Pop Up User pseudo InputBox with range selection thing which is the main issue of the last few posts.
The simple demo I have done helps illustrate that thing I mentioned about the change of the ByRef to ByVal. If you play around with the code and change that __(By___) at the start of the next code section, then I think you will get the point of what I am suggesting there
So in section 3a) the main thing related to the issue is the line
_ Call HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(RSel)
That code line makes the pop up come up, and the selected spreadsheet range is returned as a range object in the variable RSel. The code will wait until you have made the selection, but it will not prevent you from making the selection. So in that respect it works similarly to the Application Input Box Method when you use that Application Input Box Method and choose the last option as , Type:=8.

Code:

'  ========================
Rem 3a) This is just to demo the idea of a Pop Up User InputBox with range selection alternative with API User 32 dll Programs.      ' Normally in this section 3a) there would be other stuff and probably lots of it and if I have anything then it will be very Pretty.. Pretty well disgusting probably.
Sub MainSubWithAllOtherStuffInIt() ' This would be your main coding and would nornally be a lot bigger, it is just here as part of the demo for a Pop Up User InputBox with range selection alternative with API User 32 dll Programs
' Some where in the main code I might want to ask the user to select a range. So to do that I
Dim RSel As Range ' This is a variable to hold the Pointer to the users range object..        So this variable in VBA is like the Link to the part of a URL string reducing size site where a few things about the actual Final site is informed about. This area in that site, like a pigeon Hole to which the variable refers, ( the "pigeon hole" location address, and all its contents would be defined as the "Pointer". Amongst other things it has a link, a "Pointing part", pointing to actually where all the stuff is
 'Set RSel = Selection ' This line will be needed if you chose to send ByVal. That is necerssary to ensure that you have a range object - If you do not have a range object when you go to HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThatMsgBoxUp(ByVal RcelsToYou), then you wont have one when you get back neither, as in HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThatMsgBoxUp you will be Set ing the copy variable, not the actual RSel variable. You put a copy of the Pointer in the new variable. But it is an object. A different object. A Copy object.   https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-2.html#post4386360
 Call HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThatMsgBoxUp(RSel)  '                     In a normal application of the main Theme of all this, this would be the main code line you use to cause a the "Pop Up User pseudo InputBox with range selection alternative with API User 32 dll Programs"
 VBA.MsgBox Prompt:="Address check RSel - It is now " & RSel.Address & "" & vbCrLf & "Da .Value of the range object is " & RSel.Value ' Just done to demo that A simple change of the ByRef to ByVal in the signature line of a Called routine allows you to change the value of a range object to that of the selection, but the original range object will not change, that is to say its address remains as before the selection.
End Sub

**DocAElstein** · 02-09-2018, 02:37 AM

Section 3b)-3c) does two main things.
_ A “Hook” is “Hanged” to both
__ “catch” events similar to my “Non Modal message box” popping up,
and then when it does it
__ triggers off a Function WinSubWinCls_JerkBackOffHooKterd
I have tried to explain everything in more detail in the ‘Comments
What actually appears to happen in end effect is that typically as my message box Pops up the function is triggered 6 times before it gets to the one I actually want which is the Message box window being activated

Code:

Private Sub HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThatMsgBoxUp(ByRef RcelsToYou) ' This will by referral To You, (RSel), the actual Pointer of you the original RSel.  This is not too important a point here, but intersting if you consider the next line alternative to this one.....
' Public Sub HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThatMsgBoxUp(ByVal RcelsToYou)     The RSel Pointer aint Gone anywhere if you do this. Just a copy of the Pointer is here. This will allow you to change the value as the Pointer or a copy of it will tell you where to go and do that... But in neither this line or the last line case have you sent the range object. If you use this line then you will find that the address of the range object will not change, as that refers to the range object of the copy variable in this subroutine. But that will not change the range object of RSel
 Set RcelsToYou = Selection ' 3c(-i)                                                          Pointer GoneTo -1 WTF
Noughty:                    ' 3c(0i)                                                          Pointer GoneTo 0y WTF
' 3b) Hang A Hook to catch things like APIssinUserDLL_MsgBox,   ....  HOOK: Hook the pseudo Windows Sub Class Function WinSubWinCls_JerkBackOffHooKerd
Dim BookMarkClassTeachMeWind As Long: Let BookMarkClassTeachMeWind = 5 ' I do not need this. 5 is Hooktype that I will be using. Using a variable for two reasons: '1_- general point in computing that you might get problems when a number is used to refer to something that might take or give a number at some point. But you might need to do that, so having an intermediate word is a workaround for that so that the number is set to a word which is then related to a word that might be being referred to or returning a number. Function = Word --- Word = 873248   '2_- Just to avoid confusion later as in this particular case later another option number in Rem 4 happens to be 5. That is checking for a Window opening. So it is similar to the 5 of BookMarkClassTeachMeWind, but it is a narrowed down version of those window happening things. So a bit of aa coincidence really. Using the variable just reminds me of that.
 Let hHookTrapCrapNumber = SetWindowsHooksExample(BookMarkClassTeachMeWind, AddressOf WinSubWinCls_JerkBackOffHooKerd, 0, GetDaFredId)   ' (5-pull before flush,  somehow arranges that the function gets called  ,
' 3c) Bring APIssinUserDLL_MsgBox up
Dim Valyou As Variant: Let Valyou = RcelsToYou.Value: If IsArray(Valyou) Then Valyou = Valyou(1, 1) 'For display Value of Top Left of Selection
Dim Rpnce As Long '                                                                           Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in.  '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )       https://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-4.html
 Let Rpnce = APIssinUserDLL_MsgBox(hWnd:=&H0, Prompt:="Yes,  or No to ReCheck, Cancel for help ", Title:="Selection Check: Address is " & RcelsToYou.Address & "  Value is """ & Valyou & """", Buts:=vbYesNoCancel) ' ' Pseudo Non Modal MsgBox
 Set RcelsToYou = Selection: Let Valyou = RcelsToYou.Value: If IsArray(Valyou) Then Valyou = Valyou(1, 1) 'The code waited until you made one of the three message box options. But in this time you could change the selection object
    If Rpnce = 2 Then Application.Help HelpFile:=ThisWorkbook.Path & "\AnyFileName.chm", HelpContextID:=2 '              -----    download this file:  https://app.box.com/s/bx2pkvtemsppscz60rd6f430wm89c6fj This is a “.chm Microsoft Help file” It has the name _ AnyFileName.chm ---  Put in same folder as this Workbook  ---   Check out possible workarounds  --- http://www.excelfox.com/forum/showthread.php/2146-%E0%A4%AC%E0%A5%8D%E0%A4%B2%E0%A5%89%E0%A4%97-%E0%A4%95%E0%A5%8B%E0%A4%B6%E0%A4%BF%E0%A4%B6-%E0%A4%95%E0%A4%B0-%E0%A4%B0%E0%A4%B9%E0%A4%BE-%E0%A4%B9%E0%A5%88-%D8%A8%D9%84%D8%A7%DA%AF%D8%B2-%DA%A9%DB%8C-%DA%A9*Trying-Blogs?p=10467#post10467   ---  you shopuld get this  HelpGetUpBollox.JPG imgur.com/KdKOYWr
    If Rpnce = 7 Then GoTo Noughty ' Option to update the displayed Address and Value in Top Left cell of that range
End Sub
'_-=Rem 4===========..

**DocAElstein** · 02-09-2018, 02:48 AM

Section Rem 4 is the Function WinSubWinCls_JerkBackOffHooKterd
This picks out the specific event I want, my “Non modal message box” being activated, and changes the window dimensions, using the API thing SetWindowPos
( You will need to experiment about with, mainly, the 10 50 400 150 The other two numbers 0 40 you may want to adjust as well. Understanding those two numbers fully is a bit beyond me – it took me best part of a week to figure out WTF all the other stuff was about ).
The last thing this function does is “kill” or “drop” or ““take off” the “Hook”” or ““Unhook” the hook”. If you don’t do that the function seems to be triggered indefinitely.
A last thing on a similar point: Some other codes doing something similar that I saw, seemed to cause a wild recursion: The size adjustment done in the function seemed to set off the function code again. The stack seemed to be limited to 30. I could not see any reason to do that, and in fact it seemed to cause some weird inconsistent ghostly images to appear on my desktop. My function just does the thing that I think it should do once. It appears more stable. I did have a lot of fun braking things before I got the API stuff correct. But since then the code seems to work well without problems in a number of code situations on different computers and different Excel versions.
( In all the situations that I have tried, my code has Exited as I have expected after an If clause detects if the function is on the start of a first recursion run. ( I think it would probably do no harm to unhook directly after the SetWindowPos , just in case the SetWindowPos does not cause recursion. ( It seems to do no harm to unhook a few times) )

Code:

'_-=Rem 4=============  Some hidden function / bookmark / bookmarks / cyber Robot thing was brought into life ("I hung or set a hook"). That monitors events like my message box popping up. When it catches one it starts this finction and passes to it three parameters. The first tells me with a number more excactly what even took place,  the next is the window identifying number of that window doing that particular event , the last parameter is something maybe to do with the mouse god knows what exactly probably even Sid don't know...  but looking at his Avatar I probably wouldn't say that to him  as he I don't know if I would want to mess with him...
Private Function WinSubWinCls_JerkBackOffHooKerd(ByVal lMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long  '    I "set a hook"  which should trigger ( things similar to my Meassage box popping up  , and gave it the AddressOf this function ,  0   ,   and limited it to this "Thread" on my computer that is to say my Excel   )
 Let GlobinalCntChopsLog = GlobinalCntChopsLog + 1 '  The idea of this is that  I add 1 on entering and subtract 1 when leaving the function. So this would be two if I started an other copy of this code before the first had finished. I am expecting that as the SetWindowPosition seemes to trigger it off again.
    If GlobinalCntChopsLog = 2 Then Let GlobinalCntChopsLog = GlobinalCntChopsLog - 1: UnHookWindowsHookCodEx hHookTrapCrapNumber: Exit Function ' If I have 2 then that is an indication that recurtion has taken place, that is to say I started another function run caused by SetWindowPosition triggering it off. So i assume then that SetWindowPosition has done what it should so I can "take the hook off" (as if i did not then the function seems to get triggeredt indefinitely even without recusion), and then I exit the function. So I do expect a second copy of the code to run, but due to this it does not do anything other than take the "hook off". I also reduce the count by 1. It is then at 1. But then the first copy of the function ends from just under SetWindowPosition. So then the count is reduced again and is at the initial 0
    If lMsg = 5 Then Let Booloks = SetWindowPosition(wParam, 0, 10, 50, 400, 150, 40)   '  5 here is the number for a window about to be activated. This is probably the one I want. If I catch it when it is starting , 3, then It might then re set the size and position stuff again to the standard after I have done it
 Let GlobinalCntChopsLog = GlobinalCntChopsLog - 1 '  Every first copy run of the code has the count reduced to 0 so that when it starts again (as the only first copy active) it will be increased to 1 again to indicate it is a run of the function copy 1
End Function

_.________________________________________________ __________________________________________________ _________________________________-

Edit Dec 2018 : check this out http://www.eileenslounge.com/viewtopic.php?f=30&t=31495 If / when I understand it I will post a follow up about this..