Excel VBA Function Method API Windows Function User32.dll Alias Declare Library List . :)

Excel VBA Function Method API Windows Function User32.dll Alias Declare Library List .. thingies

Hello,
I am sorry if the Thread title does not quite match what it is that I want. As usual I am not quite sure myself what it is that I am talking about….

Maybe an example will help get across which List I am trying to get hold of.
In Excel VBA there is a message box pop up thingy MsgBox, the VBA Message Box Function ( https://msdn.microsoft.com/en-us/vba...sgbox-function )
This apparently uses a “Windows API software code thingy”, ( "MessageBoxA" )
Yesterday I found out that is quite easy to “use that standard code more directly”. All you need is a simple single code line like

Rem                                                                                     MessageBoxA       http://www.tek-tips.com/faqs.cfm?fid=4699   I am going to use a Windows , application programming interface, API, instead of using it via the VBA interface. The handle (a special identifier which uniquely identifies a window) of the Window which will own this message box.; if this is null, no window will own it. The returned number follows the rules here https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/msgbox-function. API are typically standard programs that other applications, such as Excel VBA can use. Windows API are typically shipped as standard with Windows.   
Pubic Declare Function APIssinUserDLL_MsgBox Lib "user32" Alias "MessageBoxA" (Optional ByVal hWnd As Long, Optional ByVal Prompt As String, Optional ByVal Title As String, Optional ByVal buttons As Long) As Long '

After adding that line at the top of a code module, you use the “APIssinUserDLL_MsgBox” in VBA codes very similarly to how you use the VBA Message Box Function, MsgBox https://www.excelforum.com/developme...ml#post4822070 .
So I want a good list of those Pubic Declare thingys code lines
So Ideally what I would like is a list of Excel Methods and Functions and alongside each Method or Function the “ Excel VBA Function Method API Windows Function User32.dll Alias Declare Library “ thingy code line.
If, in addition, I can get an explanation of all the parameter arguments as well then so much the better.


Currently, for example, I am trying to find the “ Excel VBA Function Method API Windows Function User32.dll Alias Declare Library “ stuff for the VBA Application Input Box Method ( https://msdn.microsoft.com/en-us/vba...x-method-excel ), ( and possibly the VBA Input Box Function ( https://msdn.microsoft.com/en-us/vba...utbox-function )
Possibly, they use the same “ Excel VBA Function Method API Windows Function User32.dll Alias Declare Library “ thingy. I don’t know



I have also asked the question in this Windows Forum here http://www.eileenslounge.com/viewforum.php?f=18 , as maybe it is a Windows thingy . But I am mainly interested in using those API Windows Function User32.dll Declare Library Alias thingys as they apply to Excel VBA Methods and Functions.


Thanks
Alan

P.s. If anyone knows what it is that I am talking about, could they possibly explain it to me in simple terms what that is.
My guess is that I am talking about …….a “Declaring” code line that gives my code access to a set of standard programs shipped with Windows that are available for use in various things, such as Excel, Word, Access etc…

I have only one question to start- why?

Actually, make that two- what makes you think that Excel is calling Windows API functions for all its methods (it is not)?

Originally Posted by xlnitwit

-1 why?
...2 what makes you think that Excel is calling Windows API functions for all its methods (it is not)?

Hi Don, that was quick, …. I put the Kettle on and it hasn’t boiled yet...

_1 ) I have read, and also found out yesterday with my own experience, ( https://www.excelforum.com/developme...ml#post4822070 ) that sometime there are Bugs that Microsoft never get around to fixing with Methods and Functions when using them in Excel. When that happens it seems that they usually then work OK by the “API call route”. I confessed that I do not quite know exactly what I or others mean when they use the word “API call route”. ( Some people I asked who used that term also confessed they did not know what they meant, all be it not always publically )
I was surprised how easy it is to use these “things” … just a simple line at the top ( and a very ‘pretty one at that )
So I thought why not use them-…
_ 1(i) They are less likely to go wrong
_1(ii) They are less likely to be abandoned by Microsoft if they are being used for many applications.( They may then be a bit more likely to fix them if they go wrong )
_1(iii) You can tailor them possibly better to more to suit what you want, as I did yesterday.
( _1(iv) I can make my codes even more ‘ prettier )

_2) This is the impression I got from lots of reading. But I did not actually mean to suggest that all its Methods and Functions do “API call stuff”. I expect it read like that. That was my mistake. Sorry. I meant to say …. A List please for those Methods and Functions that do make use of the available “API stuff User32.dll wot ever it is”

Alan

( I told you I don’t know what I am talking about )

OK, a few observations.

1. I am not sure that the VBA MsgBox function actually calls the MessageBox API function, but that's not really critical.
2. a) API calls are usually far from simple.
b) When you get them wrong, Excel is liable to terminate with no warning at all- and that is if you are lucky.
3. There is no list anywhere of Excel functions that call APIs. I'd imagine most of them involve an API call at some point, since the API includes functions for memory management, Window drawing, Cursors etc. So I don't think the question makes sense as asked.
4. There is no Inputbox API call that I have ever seen.

OK, thanks Don.
Yesterday I hit two specific examples that seemed to make my question make sense as asked.
Possibly by chance I hit the exceptions to the rule.

Originally Posted by xlnitwit

.... I am not sure that the VBA MsgBox function actually calls the MessageBox API function, ....

I am even more not surer .. I have no idea.. Only what I started reading and used in sone recent posts….
I referenced the posts.. here again the codes from those referenced Threads ….
_1 instead of message box , MsgBox , you call “MessageBoxA” – This article http://www.tek-tips.com/faqs.cfm?fid=4699 seemed to suggest it uses the “MessageBox API function” ( It makes the final seen message box pseudo not modal by not “locking” it to a window )

Option Explicit
Rem -2 MessageBoxA   http://www.tek-tips.com/faqs.cfm?fid=4699   I am going to use a Windows , application programming interface, API, instead of using it via the VBA interface. The handle (a special identifier which uniquely identifies a window) of the Window which will own this message box.; if this is null, no window will own it. The returned number follows the rules here https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/msgbox-function. API are typically standard programs that other applications, such as Excel VBA can use. Windows API are typically shiped as standard with Windows.  
Public Declare Function APIssinUserDLL_MsgBox Lib "user32" Alias "MessageBoxA" (Optional ByVal hWnd As Long, Optional ByVal Prompt As String, Optional ByVal Title As String, Optional ByVal buttons As Long) As Long '
Sub TestWndBreaks()
Dim Response 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 Response = APIssinUserDLL_MsgBox(hWnd:=0, Prompt:="Pull my Finger?", Title:="NotModalMsgBox", buttons:=vbYesNo)
    If Response = 6 Then Application.Speech.Speak "faarrrp" ' 6 is variable vbYes
End Sub

_.________________________________--

_2 That above did not work too well, or rather it worked well, but did not do what the OP wanted. So I found a solution involving the “scriptshell.popup”. ( That is a pop up message box that should close itself after a time which you can specify )
But that is full of Bugs , - There are loads of reports on that.. basically it is broke .. usually . It usually does not work. It usually fails to close itself.
This alternative does work. The impression I got was that it “bypasses the Excel Interface” and goes directly to the API thingy … or so I thought….. But once again , I confess I really don’t know what I am talking about. Unfortunately it is one of these subjects that no one seems to remember exactly what it is about or how it works. Possibly for that reason when these things break they never get fixed as no one can remember what they are..
This code works – it keeps telling you something until you acknowledge with a “Yes”

 Option Explicit  '   https://www.excelforum.com/excel-programming-vba-macros/1215431-speech-speak-with-message-box-and-a-reminder-every-2-minutes.html
Rem -2 Functions to use standard Window Libray stuff- Most fundamental Window Message box and the one to find a window number
Public Declare Function APIsinUserDLL_MsgBox Lib "user32.dll" Alias "MessageBoxTimeoutA" (Optional ByVal hWnd As Long, Optional ByVal Prompt As String, Optional ByVal Title As String, Optional ByVal uType As Long, Optional ByVal wLanguageID As Long, Optional ByVal Delay_ms As Long) As Long
'Public Declare Function FindWndNumber Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Dim RunyTime As Date
Public Sub JohnGetUp()
Rem 1 Tell him to get out of bed and, schedule theis pricedure to start again in a 5 seconds
 Application.Speech.Speak "Hey John, Wake up and leave your Plonker alone. I have finished?", SpeakAsync:=False
' Set timer for next reminder
 Let RunyTime = Now + TimeValue("00:00:05")
 Application.OnTime EarliestTime:=RunyTime, Procedure:="JohnGetUp", Schedule:=True
Rem 2 ' I thought this might be Option for Modal .... but does not seem to have any effect...
Dim WndNumber As Long
'Dim WndName As String, WndClass As String ' "Pointer" to a "Blue Print" (or Form, Questionaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular “Value”, or (“Values” for Objects).  There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. A String is a a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks.. But..http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
' Let WndNumber = FindWndNumber(lpClassName:=WndClass, lpWindowName:=WndName)
Rem 3 Give him a chance to acknowledge. If he does do then cancel the Timer
Dim Prmpt As String, CapshenTitle As String
 Let Prmpt = "Acknowledge with Yes you Wonker, or I will keep saying rude things": Let CapshenTitle = "NonModalRepeatingPopUpThingy"
Dim Response 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 Response = APIsinUserDLL_MsgBox(hWnd:=WndNumber, Prompt:=Prmpt, Title:=CapshenTitle, uType:=4, wLanguageID:=0, Delay_ms:=5000)
    If Response = 6 Then Call CancelTimers ' vbYes=6
End Sub
Public Sub CancelTimers() '                                                                                       JeffJazz    https://www.excelforum.com/excel-programming-vba-macros/1215431-speech-speak-with-message-box-and-a-reminder-every-2-minutes.html#post4820382      Hans   http://www.eileenslounge.com/viewtopic.php?f=27&t=25140#p195883
 Application.OnTime EarliestTime:=RunyTime, Procedure:="JohnGetUp", Schedule:=False
End Sub

_.____________________

I expect I may need to ask around a lot and make a list from collecting the “Excel VBA Function Method API Windows Function User32.dll Alias Declare Library” things that they use.
What ever they are , it looks like it might be something useful. I note what you said about

Originally Posted by xlnitwit

... When you get them wrong, Excel is liable to terminate with no warning at all- and that is if you are lucky....

.. I will need to be careful. …But I am feeling a bit adventurous just now ….( I copied some Excel 2010 library program files to my Excel 2007 program files this morning… It broke everything, … but I managed to fix it… )
_._______________________________-

Originally Posted by xlnitwit

4. There is no Inputbox API call that I have ever seen.

Possibly that might be why they call it the Application.Input Box …. But there also is a Input Box …. So who knows …… not me… yet…

It seems to me that you are perhaps inferring from the fact that two codes produce similar results that one simply calls the other, when there is no guarantee that that is true. Anything inherently Excel-related in VBA is extremely unlikely to be a Windows API call, since the Windows dlls have no concept of Excel's object model. Hence, Application.Inputbox would not be a Windows API call, since it can return a Range object. VBA.Inputbox could be an API call but I have been programming for a long time and I have never seen an equivalent API function.

Calling Windows API functions is typically used as a last resort when the built-in functions don't work. I can't see any benefit to defaulting to using them. First you have to remember to include the declarations in every workbook; then you have to make them 32/64 bit agnostic; then if you have to support a Mac, you have to work out the correct library location- which differs between versions incidentally- assuming that the one that you need is even present (and I guarantee you that there is no public list of those).

And having said all that, I appreciate that this seems to be the kind of thing you like to do "just because", so have fun.

Hi Don…
Just to be sure that we are on the same track …I meant to suggest something like this_.... This_....

Option Explicit
Rem -2 MessageBoxA   http://www.tek-tips.com/faqs.cfm?fid=4699   I am going to use a Windows , application programming interface, API, instead of using it via the VBA interface. The handle (a special identifier which uniquely identifies a window) of the Window which will own this message box.; if this is null, no window will own it. The returned number follows the rules here https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/msgbox-function. API are typically standard programs that other applications, such as Excel VBA can use. Windows API are typically shiped as standard with Windows.  
Public Declare Function APIssinUserDLL_MsgBox Lib "user32" Alias "MessageBoxA" (Optional ByVal hWnd As Long, Optional ByVal Prompt As String, Optional ByVal Title As String, Optional ByVal buttons As Long) As Long '
Sub TestWndBreaks()
Dim Response 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 Response = APIssinUserDLL_MsgBox(hWnd:=0, Prompt:="Pull my Finger?", Title:="NotModalMsgBox", buttons:=vbYesNo)
    If Response = 6 Then Application.Speech.Speak "faarrrp" ' 6 is variable vbYes
End Sub

_....is possibly a more direct way of doing something similar to this

Sub TestWndBreaks2()
Dim Response As Long '
 Let Response = MsgBox(Prompt:="Pull my Finger?", Title:="NotModalMsgBox", buttons:=vbYesNo)
    If Response = 6 Then Application.Speech.Speak "faarrrp" ' 6 is variable vbYes
End Sub

_... Also I was suggesting that the second one uses the first one somehow. My suggestion was that the second one somehow adds, for example, the window identifying number to make it “locked” to the Excel Window. In fact as we speak I am trying to get that confirmed based on a “Excel VBA Function Method API Windows Function User32.dll Alias Declare Library ..” that someone in a Blog has Called one of his “Top 10 API calls…”

Originally Posted by xlnitwit

It seems to me that you are perhaps inferring from the fact that two codes produce similar results that one simply calls the other, ...

No I don’t mean that at al. I have no idea. I expect it is not that simple. That is why I am asking. I am very glad to have access to people like you to set me straight.
I only know what I read.
It sounds like you are saying something a bit different to what they say in a lot of stuff that I read…It sounds like you know a bit deeper into these things than some of the authors of articles that I have read… I would have a tendency to believe you for now


It may also be that I do not know exactly what is meant by the term “Windows API call” .. one of the things I have learnt is that many of these terms are used by computer people… and rarely they actual know what it is they mean… some might have known once … but they usually forgot, or are unable to explain what they mean… “
I take some interest in understanding these things for its own sake, I admit it. .. But also a lot of what I do is for a very important personal project, … so I want to be sure I can understand it enough to maintain it and possibly improve it, and importantly repair it when it breaks. It would appear that Microsoft are not always too forthcoming in fixing things when they break. The second code in my previous Post ( Pubic Sub JohnGetUp() ) is a case in question.

Also I want to pass the knowledge on to someone else. I prefer to know what I am talking about before I do that. I fear I have a way to go, lol…


Alan
( P.s.1 – you must admit though , those extra
Pubic Declare Function ……… Lib ………. Alias at the top of a code are very pretty ‘_- , especially if you have a lot of them .. I am building up a collection … seems like no one else has… so… well I thought … someone has got to do it… )

If we assume that VBA.MsgBox calls the MessageBoxA function, then yes, your first code could be said to be more direct than the second. But, unless you have a need for something that the API call can do that the VBA.MsgBox can't (which is not usually the case in my experience), I can't see the point of using the API version, for the reasons I mentioned before. The fact that both produce a message on screen does not necessarily make them related in any way. In a similar vein, you might think that Application.Screenupdating and the LockWindowUpdate function are related since they achieve the same end but as far as I know, they are not.

For a brief overview, API is an Application Programming Interface. It's a means for one program to call code in another.

In this case, we are calling routines (specifically the MessageBoxA function) in the user32.dll executable, which is a standard Windows component- hence the Windows API terminology.

That dll has not been written for COM interop usage, which means that you can't set a reference to it in VBA and call its functions that way, so you have to use a Declare statement.

I'm not suggesting there is not a reason to use API calls in preference to VBA built-in methods, but there ought to be a good reason to add that sort of complexity.

As a couple of asides:
I'd also note that I am not aware of there being any bugs, present or historic, in the MsgBox function.
I don't know what bug you are referring to in your previous post- I didn't observe one.

Hi Don
I would never think that the “look” at things on the screen necessary meant that they use similar stuff… most things in windows look a lot similar to me. All things windows remind me of old penny arcade games things from the 70’s. ...But I do not think that they are, mostly.

Originally Posted by xlnitwit

...That dll has not been written for COM interop usage, which means that you can't set a reference to it in VBA and call its functions that way, so you have to use a Declare statement......

I’ll put that in my ‘comments for now and hope to get struck by some thunderbolt of enlightenment.. or struggle to understand it for a few years.
_..........

MsgBox versus MessageBoxA function
I haven’t read anywhere yet about problems with the MsgBox Function
I think I might experiment a little bit more with the first option ( the hWnd .. “handel” ) thingy in MessageBoxA. It might reveal something useful. It might not. It does not seem to me to much bother to use. I may or may not use MessageBoxA finally.

Bug er Thingy..
It was / is this.
Apparently, a commonly used code ( at least a while back when it still worked mostly ) to get a self cancelling pop up message was something like this:
Code 1

Sub BrokePopUp()
Dim objToDoStuffAroundTheTypicalWindows As Object
 Set objToDoStuffAroundTheTypicalWindows = CreateObject("wscript.shell")
Dim Response As Long
 Let Response = objToDoStuffAroundTheTypicalWindows.popup("This should go away on its own, but Bug er it don't usually", 5, "Shell Popup window", vbYesNo)
End Sub

Most people say that this does not work for them any more. It don’t work for me neither. It should. It don’t. Microsoft said they’ll take a look if you give them lots of money to do so. No one has yet.

Apparently, or so I have read, ( https://www.excelforum.com/developme...ml#post4822070 ) all the documented MessageBox functions call the MessageBoxTimeoutA API and simply pass the timeout period as a very long time.
So that was the point right at the beginning I meant about maybe using some of these thingies, for example this MessageBoxTimeoutA , might be a pretty reliable thing..
Sure enough, in this case it works, at least for me..
Code 2

Rem ’  Functions to use standard Window Library stuff- Most fundamental Window Message box and the one to find a window number
Public Declare Function APIsinUserDLL_MsgBox Lib "user32.dll" Alias "MessageBoxTimeoutA" (Optional ByVal hWnd As Long, Optional ByVal Prompt As String, Optional ByVal Title As String, Optional ByVal uType As Long, Optional ByVal wLanguageID As Long, Optional ByVal Delay_ms As Long) As Long
'Public Declare Function FindWndNumber Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Public Sub CouldBeSaidToBeMoreDirect()
' I thought this might be Option for Modal .... but does not seem to have any effect...
Dim WndNumber As Long
'Dim WndName As String, WndClass As String ' "Pointer" to a "Blue Print" (or Form, Questionaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular “Value”, or (“Values” for Objects).  There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. A String is a a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks.. But..http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
' Let WndNumber = FindWndNumber(lpClassName:=WndClass, lpWindowName:=WndName)
Dim Prmpt As String, CapshenTitle As String
 Let Prmpt = "This seems to work, Don": Let CapshenTitle = "NonModalPopUpThingy"
Dim Response As Long '
 Let Response = APIsinUserDLL_MsgBox(hWnd:=WndNumber, Prompt:=Prmpt, Title:=CapshenTitle, uType:=4, wLanguageID:=0, Delay_ms:=5000)
End Sub

Possibly anyone popping by this post could let us know if those codes ( Code 1 and Code 2 ) work for them. ( The second one, Code 2 , must have the Pubic Declare Function ___ stuff at the top of a code module. )

Theoretically they should both do the same, that is to bring up a pop up, which if you ignore it will cancel itself in 5 seconds. The first one usually does not close itself.. Bug er ..

Oh. I thought you were talking about the Msgbox function. The Shell.Popup does have bugs, yes.

FYI et al.

The VBA MsgBox function is a wrapper for the Windows API MessageBox function. But does implement all of the Windows API functionality.

Originally Posted by Leith Ross

FYI et al.

The VBA MsgBox function is a wrapper for the Windows API MessageBox function. But does implement all of the Windows API functionality.

Is there any documentation to that effect? It would be most enlightening, especially if there are comparable documents for other vba functions. Also, is that only true on Windows versions of Office?