Calling Win API functions in VBA

FreeMan

Mar 14, 2018 11:55

VBA 32- v 64-bit question:

I've been developing in 32-bit Office 2010 on 64-bit Win7 for a while, while the rest of the company defaults to 32-bit Win7. (Yes, we're behind the times, don't ask.)

I've had no issues whatsoever with a few OS DLL calls (ADVAPI32.DLL and Kernel32).

Now, however, we're finally starting to upgrade to Win10 64-bit and Office 2016 64-bit, and these calls have failed.

I've added the #If Win64 Then Private Declare PtrSafe... to make these calls work in either environment.

the question is why has it worked in the past? By all appearances (and the depth of my understanding) kernel32 is a piece of the OS, so why were the calls working on both my 64-bit system and others' 32-bit systems, where now they've failed between Win10 & Win7?

Was there some implicit conversion going on in Win7 that's been removed in Win10? Or is my tenuous grasp of the situation totally wrong?

Quack Exchange

0

Q: Excel VBA: Looking for advice on optimizing the following code

I'm self taught and hoping that people with more experience and knowledge can look over the following code and provide me with some pointers to optimize and streamline the process. Things such as 'rather than rebuild the arrays why don't you create global arrays', or 'you're really not accomplish...

FreeMan

Also, what the heck have they done with Help|About so I can find out the exact version, product key, etc? Why, MS, why must you change things just to change them?????

SlowLearner

@FreeMan Um... were you writing in 64 Bit App or only 64 Bit OS with 32 bit App (default for Office 2007 I think)?

M.Doerner

@Mat'sMug The use case I see is that one just wants to do something a specific number of times. Still, the inspection is current then.

FreeMan

It was 32-bit Office on 64-bit Win7, @SlowLearner. But isn't kernel32 part of the 64-bit OS, not the 32-bit app?

SlowLearner

Mar 14, 2018 12:11

I couldn't say about the kernel, but I've had to change some longs to longlongs in the past to get things working...

FreeMan

yeah, I've got it working, now I'm just trying to figure out why it worked in a mixed 32/64 bit environment before, but doesn't seem to be working in a 64/64 bit environment now. S-/

M.Doerner

@FreeMan Do you have to supply pointers in the calls? I have the vague recollection that you will need long pointers in 64bit.

FreeMan

@M.Doerner - in this case, no, just a String and a Long, no pointers.

I'd had just the #Else portion in my mixed Win7/Offcie10 environment, now I've got the whole thing in my Win10/Office16 environment:

#If Win64 Then
    Private Declare PtrSafe Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
                             (ByVal IpBuffer As String, nSize As Long) As Long
    Private Declare PtrSafe Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" _
                             (ByVal lpBuffer As String, nSize As Long) As Long
#Else
    Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
                             (ByVal IpBuffer As String, nSize As Long) As Long

Also, interestingly, in Office 2016, the #Else portion is highlighted in red as an error, while in 2010, the #If portion is not highlighted as an error.

Again, that code works just fine in either environment, I'm just trying to figure out why it wasn't necessary previously.

this

Mar 14, 2018 12:49

Because I'm nitpicky.....

I really don't like using long when API uses BOOL

The return value should be Boolean. VB knows what to do with a BOOL and you get to avoid the weird If Not 1 Then bug

and I don't like using Win64. Why use the non-PtrSafe version just because you're running 32-bit? Better to use VBA7 flag instead.

and it's not clear from the comments whether adding PtrSafe was all that was done and whether one literally use LongLong. I really hope that wasn't the hope.

All variables that represents a pointer should be consistently converted to LongPtr; not to LongLong; it should be only used if the API actually needs a 64-bit integer which is very rare.

Oh, FWIW.... there is a small Wiki that tries to enumerate common VBA7 declaration here: utteraccess.com/wiki/Category:API

they also link to the MSDN page, which is Very Important™ for validating that the declaration make sense. Otherwise, you're going to GPF.

</soapbox>

FreeMan

Mar 14, 2018 13:08

@this thanks for the pointers. (heck, everything you post here comes with a pointer :) ). The code was copy/pastad from the interwebz a couple of years back with minimal understanding on my part, and since it just worked™, I didn't ask further questions.

The Win64 piece was also a copy pasta from the series of tubes and so far has just worked™.

I'll do some reading - I appreciate the tips.

this

Friends don't let friends copy pasta. ;)

2

You're welcome!

Quack Exchange

0

Q: Dynamically changing chart's location and size to line up with last row of data

This is a snippet of my code that automatically moves 4 charts to line up with my lastrow of data (declared and value assigned like normal). This code works fine, but there are a few things I hate about it... The worksheet I'm changing the charts on has to be the active worksheet or the charts ...

vba excel

FreeMan

Mar 14, 2018 13:29

@this - stupid question time... the utteraccess (I keep reading that as "udderaccess". Not sure why) page you linked to doesn't contain either of the functions I'm calling GetUserName or GetComputerName (which should be in a kernel32 listing somewhere). Thoughts?

getUserName is listed on the linked page at AllAPI.net as a VBA6 option

Also, for the WNetGetUser function, which seems to me to be the closest (utteraccess.com/wiki/WNetGetUser), it indicates "Pre-VBA7" is pre-Access2010. Since I'm coding in 2010 (Access & Excel) doesn't that mean I've been using VBA7 all along?

this

well, not that far-fetched. A former admin had the handle of Cybercow.... so.... ;)

That said, yeah it's not exhaustive and apparently we didn't need it :p

But the reason why I prefer this over AllAPI.net or whatever is if you look at some sample, they include direct link to the MSDN. Therefore i'ts easier to learn how to translate C++ notation to the correct VBA

FreeMan

Mar 14, 2018 13:44

It appears to me based on MS docs that the parameters should be long pointers, but I'm no c++ expert...

this

click few and compare to what you see on the linked MSDN page

FreeMan

however, it seems to work when passed a string an a Long.

Is that because of the way VBA makes the call it passes the pointer to the string/long by default? (more implicit magic?)

this

yes, - will explai9n later

FreeMan

coolio

this

Mar 14, 2018 14:03

so i'm a bit limited on the time but generally.... VB does some magic WRT strings and BOOL. That is one of reason why you always use the ANSI version, not unicode, in the spite of the fact that VBA uses Unicode internally with its string and why you pass the string "ByVal" even though the MSDN doc will say they expect a pointer to the string.

For now, if you see BOOL, you should cast it to a Boolean.

If you see any LP*STR (e.g. LPCSTR and few more variations), you should use ByVal String

For anything else, you should use compatible data types.

Now, the confusing thing is that C++ people loves to redefine types. So you see all those stuff like HWND, LPDWORD, INT, whatever.

what isn't clear is that they are just aliases to aliases to aliases.

therefore, when you're looking at MSDN docs and scratching what the heck is HWND

you consult this: msdn.microsoft.com/en-us/library/windows/desktop/…

look up the HWND, and you see this:

> A handle to a window.

This type is declared in WinDef.h as follows:

typedef HANDLE HWND;

the HANDLE is SCREAM_CASE, so you can presume it's still another alias. Your princess is in another castle. So search (on the same page) for the HANDLE....

> A handle to an object.

This type is declared in WinNT.h as follows:

typedef PVOID HANDLE;

Still in another castle, your princess is.

> A pointer to any type.

This type is declared in WinNT.h as follows:

typedef void *PVOID;

Finally, we have a type that's not in SCREAM_CASE, that's the void, and you know that it's a pointer, and therefore the correct data type to use is LongPtr

Hosch250

Oh wow.

this

Granted, all handles are pointer, so you don't have to go down all the alias chain, but I made you do that so that you can understand how to map the correct VB data type to C++ data type.

VBA Rubberducking

Participants