This is a new post 24. It appeared after a copy of full page 2 gave me a full page 3. It is #post24897
It came from Original post #13, it got shifted down to post #15 when posts 11 12 and 13 where copied on 31 Oct 2024 #post10485
This is almost identical to post #24
In fact we only really need 1 change, in the signature line of the routine first set off/Called by the main coding, but two other changes will be made to aid in the explanation
Code:
Set RSel = Selection ' To aid in the explanation of the ByVal example
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 & "" & vbCr & vbLf & "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.
'
' Other stuff
RSel.Select ' To aid in the explanation of the ByVal example
End Sub ' Typically this is your main program End Sub
'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.....
Private 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
The full coding is in the code module, AlansInputBoxComnutsByVal , in the uploaded file, InputBoxMessageBoxPopUpBugsAPI.xls
Explanation of important difference to the ByRef example.
Since the main change is so minimal, it is probably more useful to explain the important effects of the difference rather than repeat most of what was in the previous explanation :
We have filled/assigned the variable RSel to the current selection ** in the main sub to the existing selection, with the first extra line - Set RSel = Selection - added to aid in the explanation, and we do this just before the coding goes off into the API related things.
The first thing that then is done in the initial API coding is that a code line - Set RcelsToYou = Selection - does effectively very similar to our first extra code line. However, the main point that will be emphasised many times in such explanations, is that the second time we are doing something important a bit different: RcelsToYou is no longer referring to the variable RSel. It will at this point, point to the same range object assigned to RSel, because it is a copy of the pointer. But as we proceed through selections via our pop up in the course of running normally all the coding, we will no longer be actually changing the range object to which RSel refers/points to.
Just before the end of the main coding , the other extra code line - RSel.Select - will now select our very first selection, regardless of any selections we made during the course of our pop up in the course of running normally all the coding.
The end result rather goes against the main purpose of this thread, which is to have the range object referred to/ pointed to by the variable RSel changed as a result of a selection. But it emphasises the need to have ByRef in the coding to achieve our main original requirement
( ** Note that if we had not had the first extra code line, then the final extra code line would actually have errored, since RSel would not have been assigned a range object. For the same situation in the previous ByRef coding example, there would be no error as the variable RSel was effectively being referred to and assigned one or more times course of our pop up in the course of running normally all the coding. )
Reply With Quote
Bookmarks