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

[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…

[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.

[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

[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...]

[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.
_.____

...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

[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;

[DocAElstein]

Hi
Back here again, 6 years later, as I am revising some of my API related stuff.
I was not quite sure what or how to word the question back then. Probably something with more emphasis on something like VBA Windows API or VBA Excel Windows API is better: I am finding with hindsight that is better for searching the internet for what I was after.
That Dan Appleman book seemed to be something of a bible for a lot of people, so I forced myself to have it on the bed time reading list for a few months. I spent many hours / days on it, but sadly got next to nothing out of it, :( - Perhaps partly due to my failings, but also I expect it might be one of those fantastic reference books for once you have some knowledge and experience with API stuff, but it doesn’t really help much in early learning in the area of VBA Windows API. I could not navigate that book easily to find any useful info either. But if I find it, I will take a fresh look in the book. (I think I recall it finding its way into doing something structurally useful, either propping something up, or as a well-fitting Spacer somewhere)

In the meantime a few things like YouTube tutorials have popped up. I have gleaned a bit of info from them reecently, but I did not find any good enough that I would recommend them further for a list of learning materials for Excel or VBA.

ActiveVB ( .de )
I did stumble on something just recently, ( a free software to download and install ), that I thought could be a good addition to this Thread. I missed it at the time. It is German, and quite old. It pretty well gets the sort of list I was looking for. I say sort of as I am thinking it may not be complete because: it is 20 years old***; does not seem to have been maintained or updated much; and I think it has mostly for those, "user__.dll" things, which typically on your computer are in some user__ Folder, where most of them API things are, but I think a few are in some other obscure places?
( *** I think its old VB6 stuff or similar, so might be one of the things Hans mentioned )


Perhaps because it is so old, I found it initially a bit quirky in downloading and installing, especially on my newer machines. After a bit of playing around, I ended up with something that seemed usually to do the job across all my computers…….
To get this software, there is a couple of files to download and run to get the thing installed on your computer: (I think only the second file is important):
(_1) If you download and install from this , you get some small help file, with technical detail relevant to the thing, but that means nothing to me. )
_2) If you download and install from this , and run* it you should get something looking like this

ApiViewer2004 Main menu.JPG

( * If you can’t find it after install, then look somewhere like this,
C:\Program Files (x86)\ApiViewer 2004
C:\Program Files\ApiViewer 2004
Then double click on something like ApiViewer2004.exe )

If you get the thing finally running, then
_The main menu should come up
_ Use the top left drop down explorer thing to get a file , such as, for example, the user32 dll stuff ( Note: I think its getting the info from a data bank that comes with the install - I don't think it grabs them from anything on your computer, but I am not 100% sure, perhaps it does during the install??? Maybe I will know for sure as time goes on and I am using it in anger on different computers)

_ Click on something , then you get the line you need typically at the top of a code module, and you can copy that*!!.

Click on something, to get the stuff.JPG

_ when you are selecting it, like in that last step, if you hit key F2 , a window intended to give some info about the thing comes up. So far I have only seen that empty. Shame. My guess is that it was intended to update to fill those in, but perhaps for some reason the whole thing has been forgotten and abandoned since about 2008. There is in that Help window an extra tab, and after selecting it you can add your own notes . I have not seen any specific way to save those, but so far, it seems they stay there, that is to say, they reappear if you close and restart the software. I am not sure if I would rely on that for anything other than a few temporary notes, ….

_.___________________-

…… I am thinking that as a VBA or VBA Excel person, if you want to get into these API things then the only option is probably just to collect working examples, then you might get some instinct for guessing how to tweak them and/ or cobble them together to do other things. My guess is that the VBA API Gurus do that and they don’t have any real knowledge to be able to develop coding. I might be wrong, and I definitely don’t want to insult them, - they will all know infinitely more API and VBA API stuff then I ever will. I am just suggesting perhaps that you would need to be a computer expert across the whole Windows area to have real knowledge about these things, so as to develop a coding with them from scratch, or easily modify an existing coding in any way other than trial and error, ( and if you were a full computer expert across the whole Windows area, then that book probably would be great for you. )


Alan


*!!P.S. The Declareations you get from that old software might need the PtrSafe thing added between the words Declare and Function.
It might be betetr so as to keep backward compatibility, to be taken care of with something like this at the top of your code module, instead of the single Declareation line

Code: Select all

    #If VBA7 Then '    The next  line turns red for Excel versions under 2010, - nothing to worry about
     Declare PtrSafe Function ……………………………..
    #Else
     Declare Function …………………………
    #End If

[DocAElstein]

Here is a quick addition / ( an edit I can’t add to the last post as it is full )
The APIViewer/ActiveVB I mentioned in the last post, seems to originate from
http://allapi.mentalis.org/apilist/apilist.php ( https://i.postimg.cc/kM2VV2fN/Api-Viewe ... n-site.jpg ,
and the descriptions, which so far seem to be missing on the APIViewer, are there at the origination site also !! https://i.postimg.cc/y8HWrvBn/allapi-me ... iption.jpg )



Ref
https://eileenslounge.com/viewtopic.php ... 18#p322118
https://www.microsoft.com/en-us/downloa ... px?id=9970
https://web.archive.org/web/20120623064 ... px?id=9970