+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    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
    Please Login or Register  to view this content.
    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
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

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

    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)?
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    Quote Originally Posted by xlnitwit View Post
    -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 )

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

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

    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.

  5. #5
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    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.

    Quote Originally Posted by xlnitwit View Post
    .... 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 )

    Please Login or Register  to view this content.
    _.________________________________--

    _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”
    Please Login or Register  to view this content.

    _.____________________

    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
    Quote Originally Posted by xlnitwit View Post
    ... 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… )
    _._______________________________-
    Quote Originally Posted by xlnitwit View Post
    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…

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

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

    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.

  7. #7
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    Hi Don…
    Just to be sure that we are on the same track …I meant to suggest something like this_.... This_....
    Please Login or Register  to view this content.

    _....is possibly a more direct way of doing something similar to this
    Please Login or Register  to view this content.
    _... 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…”


    Quote Originally Posted by xlnitwit View Post
    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… )

  8. #8
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

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

    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.

  9. #9
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    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.
    Quote Originally Posted by xlnitwit View Post
    ...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
    Please Login or Register  to view this content.
    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
    Please Login or Register  to view this content.
    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 ..
    Last edited by Doc.AElstein; 01-17-2018 at 06:45 PM.

  10. #10
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

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

    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.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  11. #11
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

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

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

  12. #12
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

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

    Quote Originally Posted by Leith Ross View Post
    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?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Function to Declare Yes = 100 and No = 0
    By akwishestofish in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2013, 02:30 PM
  2. Validation list from Mac Excel 2011 fails to function on Windows 7 Excel 2010
    By lhlevasseur in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 12-17-2012, 07:08 PM
  3. [SOLVED] How do I create a company wide Excel function library?
    By DoctorG in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2006, 08:21 AM
  4. alias for the INDIRECT function
    By Takeadoe in forum Excel General
    Replies: 10
    Last Post: 06-30-2006, 02:45 PM
  5. How do I declare a function whose output is an array?
    By Schizoid Man in forum Excel General
    Replies: 1
    Last Post: 05-24-2006, 05:30 PM
  6. How can use LONGLONG type of C++ Dll in 'Declare Function' statement?
    By Zoo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-10-2006, 07:10 AM
  7. gcTCPObjLib.dll use as Excel function library
    By indigobrad in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-10-2005, 04:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1