List XL VBA Function Method API Windows User32.dll Alias Dec

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

List XL VBA Function Method API Windows User32.dll Alias Dec

Post by Doc.AElstein »

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/la ... x-function" onclick="window.open(this.href);return false; )
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

Code: Select all

 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 ' 


' MessageBoxA   http://www.tek-tips.com/faqs.cfm?fid=4699   
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/development- ... ost4822070" onclick="window.open(this.href);return false; .
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 those Methods and Functions 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/ex ... thod-excel" onclick="window.open(this.href);return false; ), ( and possibly the VBA Input Box Function ( https://msdn.microsoft.com/en-us/vba/la ... x-function" onclick="window.open(this.href);return false; )
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 this question in this Excel Forum here https://www.excelforum.com/excel-progra ... ost4822879" onclick="window.open(this.href);return false; , as 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. I was not sure if there might be a separate shorter list specifically for Excel VBA that someone there might know about. I will feedback any info that I get from there.


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 am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: List XL VBA Function Method API Windows User32.dll Alias

Post by HansV »

Unfortunately, most documentation of the Windows API functions is for C#, not for VBA. See for example API Index (Windows).
There used to be a good VB6/VBA reference for API functions, but I can't find it anymore.

Some examples: 10+ of my favorite Windows API functions to use in Office applications.
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: List XL VBA Function Method API Windows User32.dll Alias

Post by Doc.AElstein »

Hi Hans
Thanks for the quick response and Info.
I found the first link before, but at first glance could not find anything resembling the methods and Functions I was interested in. I expect I may need to delve very long and hard through all the stuff there and I might end up with something. Probably “hidden” at some sub link there… I had this problem before with Microsoft documentation ( https://www.excelforum.com/excel-progra ... ost4821420" onclick="window.open(this.href);return false; ). It is almost as bad as trying to get through to them on the Phone , but at least not as expansive .. lol.

I forgot to check VB and VB6 stuff. I have found sometime the documentation there better than more recent VBA documentation. I will try that.

There is some good stuff at the other link you gave, thanks. For example the “5: FindWindow” – I was trying that FindWindow yesterday and could not figure out how to get it to work. I will have another look at that now.

Maybe I need to ask a lot of people to give me the Window API Functions that they know and use.

I guess I need to build up my own list. I have a start now

Thanks again
Alan
Last edited by Doc.AElstein on 18 Jan 2018, 15:17, edited 2 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
Jay Freedman
Microsoft MVP
Posts: 1313
Joined: 24 May 2013, 15:33
Location: Warminster, PA

Re: List XL VBA Function Method API Windows User32.dll Alias

Post by Jay Freedman »

Almost anything you would want to know about the Windows 32 API can be found in this 1550-page book: Visual Basic Programmer's Guide to the Win32 API. For each function in the API, the book shows the required Declare statement, discusses the parameters and return values, and mentions common pitfalls.

What the book does not do is explain which Excel (or other Office) methods use which API functions. You can generally work that out for yourself, since the book groups the API functions by the kinds of things they do.

You should also understand that the VBA methods don't necessarily correspond one-to-one to API functions. In some cases they do, but in others the VBA combines several API calls plus VBA-specific user interface stuff to make a method more useful for specific things you do in Office programs. That's especially true of file management methods (Open, Save As, etc.) and printing. Even when there is a direct correspondence, the VBA functions often are easier to use; for example, API functions don't allow "optional" parameters with default values, and you can't use named arguments in API calls as all arguments are positional.

On the other side of that coin, there are lots of things you can do by calling API functions that you can't do at all with just VBA methods. [Caution: One of the things you can do, in lots of ways, is really damage your files...]

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: List XL VBA Function Method API Windows User32.dll Alias

Post by Doc.AElstein »

Thanks Jay
That sounds like a very useful book. ( I have ordered it ) It looks at first glance as that could go some way to getting the sort of List that I was after.
_.____
Jay Freedman wrote:...API functions don't allow "optional" parameters with default values, and you can't use named arguments in API calls as all arguments are positional.
I assume this is somehow saying something about the arguments in the Declareing .. “ Excel VBA Function Method API Windows Function User32.dll Alias Declare Library “ thingy code line , - I notice that if I change the order around in those initial lines in my code in next post , then either my Excel crashes or the APIssinUserDLL_MsgBox ( aka Alias MessageBoxA ) code lines does not work
I am not quite clear of the rest of what you are saying there. The last code line in my code ( in next post ) seems to me , in my naivety, to be not giving all options to the API call . ( I changed my arguments somewhere along the way to Optional in that Declareing .. “ Excel VBA Function Method API Windows Function User32.dll Alias Declare Library “ thingy code line …. Possibly in this case that is possible because, ( untypical perhaps ) , there is a 1 to 1 relationship…between the .. MsgBox and the MessageBoxA … even in the case – a non given first argument is possibly taken as a special sort of a Null Long ….but this is just a wild uneducated guess on my behalf ###

_.___________________-

I have got, I think this far now, from what you and others have told me in the last day or so.. ..
Take again the (seemingly to me) simple case of the VBA MsgBox example
The VBA MsgBox might be described in computer jargon as a WRapper Function for the one of the shipped with Microsoft standard programs which work particularly efficiently due to some dynamic way of linking them.. In other words, in English, within a VBA code, it takes info and passes that in an appropriate way to another program somewhere else. – Something like a Function

There are a couple of subtleties.
_ The particularly efficient way ( the “dynamic link technology stuff ) might mean that the other program might be “call” able simultaneously by other things other than the VBA code ..
_ It is not necessarily a “simple 1 to 1” process. In my VBA MsgBox case I have tried to get at / understand and then at least to some extent mimic what the VBA MsgBox does. I note for example that the Window that the message box is “locked to” is not specifies by the VBA MsgBox , or rather you cannot give it.( Or maybe sort of … ### ) You can give this parameter in the MessageBoxA. I am guessing based on reading up on this that part of the “non” 1 to 1 is that the MsgBox will do something additional to fix the message box that it produces into the Excel Window. In the code in the next post I have gone some way to doing this mimicking . It uses a second .. “ Excel VBA Function Method API Windows Function User32.dll Alias Declare Library “ thingy … I am thinking / assuming that the MsgBox while it is doing it is Rapping away it can shout / call out to another of the ddl programs as they are very dynamic apparently and so don’t get all twisted and messed up if they are a few being called/ used here and there at close to simultaneously…

I think I might be slowly getting somewhere……. But I am still not quite sure I know where I am going…. But I think I know or am getting clear on where I have been…….. I hope/ think I am not wasting time …. http://listenonrepeat.com/watch/?v=WyF8RHM1OCg" onclick="window.open(this.href);return false; ….… Here I go again…:-)

_.. It has been suggested to me at the cross link https://www.excelforum.com/excel-progra ... ost4823071" onclick="window.open(this.href);return false; that the 2 in VBA available Input boxes ( Input Box , Application.Input Box ) , may not have similarities in the ways that they work to the MsgBox. In particular the returning of a range option in the Application.Input Box makes the API route a bit iffy…
But then ,
_ if it’s a wild dynamic Wrap, then maybe it can sing a reference / pointer or whatever to the range object as VBA does that… as far as I know VBA cannot do more that refer/ point to objects anyway….. a VBA Function only takes pointers … so maybe the API Windows Function do also….
_ a wild thought … Maybe the MsgBoxes derive somehow the InputBxes… the input/output that is retuned for the message box is part of a Wrapper function inside the shipped with Microsoft standard programs… the MsgBox is a Wrapper for the MessageBoxA that is a a WRapper for the Input Boxes or an Input Box and amongst other things they probably use the "MessageBoxTimeoutA" which is apparently used in all these things.. somehow… possibly.. with the time out set to something very large…


I think maybe the long term objective of what I am doing here is to get some confidence in the User interfacey / pop up stylio stuff in Excel. I have just started adding those sort of things to my “Tool Box”….


Thanks again for the replies.
If no other input comes in .. in the meantime, then I will certainly be in touch when I have scoured that book….. :-)
Alan
Last edited by Doc.AElstein on 18 Jan 2018, 17:55, edited 1 time in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: List XL VBA Function Method API Windows User32.dll Alias

Post by Doc.AElstein »

Code referred to in last post…

Code: Select all

Option Explicit
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 '
Public Declare Function FindWndNumber Lib "user32" Alias "FindWindowA" (Optional ByVal lpClassName As String, Optional ByVal lpWindowName As String) As Long
Sub TestWndBreaksAtEileensLoungeMakeAMsgBox()    '    http://www.eileenslounge.com/viewtopic.php?f=18&t=28885#p223583   Mimicking a MsgBox  …  could be said to be….
' Get a number for hWnd to "lock" the Message box to a window
MsgBox Prompt:="Q_- Where am I, the MsgBox" ' A_- Middle in Excel spreadsheet '
Dim WndNumber As Long, Response As Long, hWndParent As String
 Let WndNumber = FindWndNumber(lpClassName:=vbNullString, lpWindowName:=vbNullString): Debug.Print WndNumber ' 1770562  -- must be vbNullString not ""  ??
 Let Response = APIssinUserDLL_MsgBox(hWnd:=WndNumber, Prompt:="Q_- Where am I, the MessageBoxA", Title:="Microsoft Excel", buttons:=vbOKOnly) ' A_- Middle in VB Editor
 Let hWndParent = FindWndNumber(lpClassName:="XLMAIN", lpWindowName:=vbNullString): Debug.Print hWndParent ' 5113846  6161668
 Let Response = APIssinUserDLL_MsgBox(hWnd:=hWndParent, Prompt:="Q_- Where am I, the MessageBoxA", Title:="Microsoft Excel", buttons:=vbOKOnly) ' A_- Middle in Excel spreadsheet
 Let Response = APIssinUserDLL_MsgBox(Prompt:="Q_- Where am I, the MessageBoxA") ' A_- Middle in VB Editor
End Sub



References
https://stackoverflow.com/questions/297 ... lass-names" onclick="window.open(this.href);return false;
https://stackoverflow.com/questions/427 ... e-from-vba" onclick="window.open(this.href);return false;
https://www.excelforum.com/excel-progra ... ost4822438" onclick="window.open(this.href);return false;
https://www.excelforum.com/excel-progra ... -list.html" onclick="window.open(this.href);return false;
http://www.tek-tips.com/faqs.cfm?fid=4699" onclick="window.open(this.href);return false;
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also