Results 1 to 32 of 32

Thread: [RESOLVED] VB6 to VBA

  1. #1

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Resolved [RESOLVED] VB6 to VBA

    Do people mind if I have a discussion here about the LongPtr stuff?

    I'm converting a few procedures from VB6 to VBA, and a few of them make API calls. I need for this stuff to run on both VBA 32-bit and VBA 64-bit. I'm well aware of the VB7 and Win64 compile-time constants, and I'm using them. That's not exactly the problem.

    For the most part, I've just been googling my API call names (along with "PtrSafe") to see if I can find the 64-bit declarations. However, there's lots of garbage out there.

    I suppose, I'd just like to nail-down a good understanding of the Microsoft MSDN types, and understand which ones need the LongPtr declaration. Here's the start of a list of the types I'm seeing:

    Code:
    HANDLE
    HMODULE
    HINSTANCE
    DWORD
    LPDWORD
    HWND
    BOOL
    UINT
    LPWSTR
    LPTSTR
    LPCWSTR
    INT
    Actually, I think I'm ok on most of them. If I understand correctly, this is how it should go:

    Code:
    HANDLE     LongPtr
    HMODULE    LongPtr
    LPDWORD    LongPtr
    HWND       LongPtr
    LPWSTR     LongPtr
    LPTSTR     LongPtr
    LPCWSTR    LongPtr
    
    
    DWORD      Long
    BOOL       Long
    UINT       Long (careful with VB6/VBA sign-bit)
    INT        Long
    I haven't researched out all the VBA 64-bit stuff, but I believe that form and control hWnd properties return 64-bits. Also, I believe StrPtr also returns 64-bits. And this is important to know when making these API calls.

    And, just as an example, I'm a bit uncertain how to declare EnumProcessModules when VBA7=True.

    Any comments on this would be greatly appreciated.

    EDIT: And I'm also well aware of this page: https://docs.microsoft.com/en-us/win...ows-data-types

    But that doesn't exactly answer all the questions, or maybe it does, and I'm just not clear.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  2. #2

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: VB6 to VBA

    Basically, here's my understanding. All memory pointers and handles are 64-bit if we're running 64-bit VBA. And, all functions that return those will return 64-bit numbers. I guess that'd include any properties returning a handle (of any kind), as well as things like StrPtr, VarPtr, and the AddressOf operator. At the moment, that's all I can think of.

    Another thing that's a bit confusing to me is that the API library names still have the "...32" on them, like user32.dll or kernel32.dll. I'm not sure what's going on there.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  3. #3
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: VB6 to VBA

    When needed, like you, I tend to google and hope for the best. But beware, not all parameters are changed. For example, enum resource API use Integer for language and in the 64bit declarations, they are still 2-byte integer. That leads you to believe that only 4+ byte vartypes are changed; but not willing to bet the house on it.

    And UDTs can be a nightmare too trying to get their correct declaration along with any padding. Other things need to be taken into consideration like Variants that are no longer 16 bytes.

    I wish MSDN had 64bit pages for APIs like the do for 32bit. If MSDN ever changes those pages from 32bit to 64bit "overnight", we may all be scrambling to preserve many of those older pages.

    Edited: Have you scanned the Office portion of the forums? This may be addressed there
    Last edited by LaVolpe; Aug 16th, 2020 at 01:44 PM.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: VB6 to VBA

    Welcome to my world.
    Since my company started switching to Office-64-Bit i had to rewrite a lot of API-Calls
    First thing: The VBA7-Constant: You only need it, if the code has to run in Pre-Office2010
    The Win64 is needed to check if Office is 64-Bit, not the OS! I know that for a fact, since we had some users running Win-64-Bit, but Office-32-Bit. All my "old" API-Calls worked unchanged (no PtrSafe, no LongPtr etc.)
    I've found a nice webpage with the most used API-Calls. Will have to look it up at my office.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: VB6 to VBA

    Quote Originally Posted by Zvoni View Post
    Win-64-Bit, but Office-32-Bit.
    Yes, that's precisely the environment I've got on my development machine, and it's exactly the environment that my primary client has on most of their machines (world-wide). They're transitioning most of their boxes to Windows 10 64-bit, but they've got a site license for Office 32-bit.

    So, everything works fine right now. However, every now and then, someone brings in a new laptop with Office 64-bit, wanting to run some software with VBA code, and all the API calls blow up.

    We're working on a new initiative where a big chunk is going to be done via (what they call) Office macros. (I tend to just call it the VBA.) And I've warned them that, if (or, more like, when) they go to Office 64-bit, that everything is going to blow up. Also, it's an entirely different department that makes the decisions about upgrading Office than the one involved in this new initiative. (Typical corporate stuff.)

    I'm just trying to get in front of it all.

    Zvoni, when you get a chance, I'd love to have a link to that website.

    ------------

    EDIT: Also, here's my understanding of the VBA7 constant vs the Win64 constant.

    VBA7 - This goes true when the VBA version is 7 or beyond. And, strictly speaking, has nothing to do with whether or not we're on Office 32-bit or Office 64-bit. I'm not sure what version of Office started with VBA version 7 though, but it doesn't really matter. So, what does this give us? It tells us that (if we're on VBA7) we've got the new keywords: PtrSafe, LongPtr, & LongLong. If we're on an earlier version of the VBA, trying to use these keywords will be a syntax error.

    Win64 - I believe this is a badly named constant. It's my understanding that this tells us whether or not we're on Office 64-bit (if true). And, if we're on Office 32-bit (even if it's Windows 64-bit), it returns false.

    So, in theory, testing for either of these (or both, if we like), and then making our API declarations accordingly, would keep us out of trouble. And, if done correctly, so long as we're on VBA7 or beyond, we can make API declarations that will work on either 32-bit Office or 64-bit Office. That's the whole idea behind the LongPtr type. It's 32-bit on 32-bit applications, and it's 64-bit on 64-bit applications.

    What the "PtrSafe" keyword is for is a bit of a mystery to me. The MSDN says:

    Code:
    Required on 64-bit. The PtrSafe keyword asserts that a Declare statement is safe to run in 64-bit versions of Microsoft Office.
    I guess this is just forcing people to think about their API declarations. I can't think of any other reason for it. I mean, we've either (correctly) got the LongPtr types in there (in which case they should work), or we don't (in which case they should crash).

    -----

    Also, I've done a bit of reading about the DLLs that still have the ...32 name on them. Apparently, on Windows 64-bit, these DLLs actually have both 32-bit code and 64-bit code within them. A 32-bit program will call the 32-bit versions of the API, and a 64-bit program will call the 64-bit versions of the API. That's the best I can make out.
    Last edited by Elroy; Aug 16th, 2020 at 03:43 PM.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  6. #6

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: VB6 to VBA

    And yes, I'm correct about that Win64 constant. The following code reports "false" (in a Word macro). And just as an FYI, Intellisense is changing the case of it, so it's finding the constant somewhere:

    Code:
    
    Sub test()
        #If Win64 Then
            MsgBox "true"
        #Else
            MsgBox "false"
        #End If
    End Sub
    
    And under Settings/System/About, I've got the following:

    System type: 64-bit operating system, x64-based processor

    EDIT: Ahh, Zvoni, I just re-read your post, and this is actually what you said.
    Last edited by Elroy; Aug 16th, 2020 at 03:58 PM.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  7. #7
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: VB6 to VBA

    Somewhere there are probably Microsoft "white papers" or KB "How To" articles on this topic. If so I can't find anything comprehensive. Maybe they existed once but are now long gone?

  8. #8

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: VB6 to VBA

    See? I'm trying to get my head around this stuff, and then run across MSDN stuff like this (for ShellExecuteW):

    Return value
    Type: HINSTANCE

    If the function succeeds, it returns a value greater than 32. If the function fails, it returns an error value that indicates the cause of the failure. The return value is cast as an HINSTANCE for backward compatibility with 16-bit Windows applications. It is not a true HINSTANCE, however. It can be cast only to an int and compared to either 32 or the following error codes below.
    I'm assuming I declare the return as a Long, but I'm not certain that it shouldn't be a LongPtr.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  9. #9

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: VB6 to VBA

    Quote Originally Posted by dilettante View Post
    Somewhere there are probably Microsoft "white papers" or KB "How To" articles on this topic. If so I can't find anything comprehensive. Maybe they existed once but are now long gone?
    I couldn't find them either, but I'm not sure why they'd be long gone. The VBA is still a very active part of what Microsoft sells and distributes.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  10. #10

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: VB6 to VBA

    Hmmm, ok, this continues to be tricky. For instance, I'm struggling with EnumProcessModules. Specifically, I'm struggling with the last passed argument: LPDWORD lpcbNeeded

    It's a pointer, but it's a pointer to a Long, and it's passed ByRef (so we can get it back). So, when I pass a Long ByRef, it's putting the address to the Long on the stack. Therefore, the actual memory data should stay a Long, and the internal mechanism for pushing the address to the stack should figure out if we're 32-bit or 64-bit, pushing the correct length onto the stack.

    So, in the API declaration, this should be a LongPtr, but when passing data, the variable being passed should just be a Long. I think. Or, do I just declare this as a Long in the API declaration as well? But it says it's a pointer, so I'm still thinking LongPtr in the declaration.

    EDIT: I've changed my mind. I think anything ByRef should be declared as the original type. When we create our own functions and create, say a Byte as an argument, and pass it ByRef. We know a Long (pointer to the Byte) is going on the stack, but we still declare as Byte.

    So, even though the MSDN calls this a pointer (LPDWORD), the fact that it's ByRef means I declare it as the actual type of data I expect, and not as a pointer.
    Last edited by Elroy; Aug 16th, 2020 at 05:12 PM.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  11. #11
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: VB6 to VBA

    Want to have your mind blown a bit? Here I'm talking 32bit architecture, x86 systems. Double the sizes for 64bit.

    If you pass a Byte ByVal to an API, 4 bytes are placed on the stack. That is because all stack parameters, less than 4 bytes, are promoted to 4-byte values. When ByRef, for example an Integer is the parameter vartype, 4 bytes are placed on the stack as a pointer to that Integer. When the value is returned due to ByRef, the called code would write just 2 bytes at that pointer. Yes, you can pass a Long ByRef when an Integer is required; only 2 bytes of that Long will be used anyway.

    When ByRef, you are always passing pointers. VB does this for us when we declare ByRef parameters. If we were to declare them ByVal instead, we would need to pass them via VarPtr(theVariable).
    Last edited by LaVolpe; Aug 16th, 2020 at 05:33 PM. Reason: clarification, added underlined text
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  12. #12
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: VB6 to VBA

    Caveat: can't test this because I don't have 64bit Office @ home.

    So, even though the MSDN calls this a pointer (LPDWORD), the fact that it's ByRef means I declare it as the actual type of data I expect, and not as a pointer.
    If the value within 64bit dll function actually is looking for a pointer to a 4-byte value, then I'd imagine, you would declare the parameter one of two ways

    1. ByRef param As Long. You would then pass the value simply as: myLong, which is declared as Long.
    2. ByVal param As LongPtr. You would then pass the value as VarPtr(myLong)
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  13. #13

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: VB6 to VBA

    Quote Originally Posted by LaVolpe View Post
    If the value within 64bit dll function actually is looking for a pointer to a 4-byte value, then I'd imagine, you would declare the parameter one of two ways

    1. ByRef param As Long. You would then pass the value simply as: myLong, which is declared as Long.
    2. ByVal param As LongPtr. You would then pass the value as VarPtr(myLong)
    Yes, your #1 is the conclusion I had come to. However, your #2 is a pretty good idea. If I just declare everything as ByVal, and use VarPtr when it's actually ByRef, at least I'll have the LongPtr declaration on all the API declaration arguments that are prefixed with "lp...". Otherwise, I'm going cross-eyed looking at the API declarations trying to figure out if they're correct or not.

    EDIT: And yeah, I've got the same "testing" problem. But I'll work that out. Next week, I'll find someone who can test who has Office 64-bit.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  14. #14
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: VB6 to VBA

    Quote Originally Posted by Elroy View Post
    If I just declare everything as ByVal, and use VarPtr when it's actually ByRef, at least I'll have the LongPtr declaration on all the API declaration arguments that are prefixed with "lp...". Otherwise, I'm going cross-eyed looking at the API declarations trying to figure out if they're correct or not.
    Be careful with that. I also do it for some API declares that I'm not 100% familiar with. If so, I'll add a comment that the param is actually ByRef so 6 months down the road, should I need to debug/tweak, I'll remember that the param is actually ByRef even though declared ByVal. I typically don't add those comments to APIs that I'm 100% familiar with when I change param declarations. The comments help when adding new code down the road that calls the API. Obviously, you could put 2 & 2 together by looking at existing calls since they'll have the VarPtr(variable) format.

    edited:
    And yeah, I've got the same "testing" problem. But I'll work that out. Next week, I'll find someone who can test who has Office 64-bit
    If this conversion of yours will take some time to finish and you have a 64bit system at home, you can always get Office365 for a year: $100 - not outrageous and gives you plenty of time for testing your conversions.
    Last edited by LaVolpe; Aug 16th, 2020 at 06:59 PM.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  15. #15

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: VB6 to VBA

    Quote Originally Posted by LaVolpe View Post
    If this conversion of yours will take some time to finish and you have a 64bit system at home, you can always get Office365 for a year: $100 - not outrageous and gives you plenty of time for testing your conversions.
    Yeah, it wouldn't be a problem at all to bill that to my client. If I can't easily find someone who's got it early next week, I'll do that.

    Quote Originally Posted by LaVolpe View Post
    Be careful with that. I also do it for some API declares that I'm not 100% familiar with. If so, I'll add a comment that the param is actually ByRef so 6 months down the road, should I need to debug/tweak, I'll remember that the param is actually ByRef even though declared ByVal. I typically don't add those comments to APIs that I'm 100% familiar with when I change param declarations. The comments help when adding new code down the road that calls the API. Obviously, you could put 2 & 2 together by looking at existing calls since they'll have the VarPtr(variable) format.
    Yeah, I'm already on top of that.

    -----------------------------------

    I'm feeling pretty good about things. Right now, I'm only dealing with 11 API declarations, but they're all called on this little test I've put together. I just tested on Office 32-bit (Win10 64-bit) and I didn't seem to break anything.

    Someone else is doing the bulk of the actual work, but they'll be using functions I've provided (with those API calls) to get their work done. So, I'd really like my part to be bullet-proof.

    Also, I've made an executive decision. I've decided that none of this stuff is going to work on Office versions prior to Office 2010. That's when VBA7 (along with an Office 64-bit option) was introduced. That allows me to completely forget about the VBA7 and WIN64 conditional compilation constants, and allows me to have only one version of the VBA source code.

    Right now, here are the API "conversion" criteria I'm using.

    1) Any of the MS types on this page that start with H (except for HALF_PTR, which is just weird), I change their declarations to LongPtr. This is inclusive of API function return values.

    2) Any of the MS types on this page that start with LP, I change their declarations to LongPtr. This is inclusive of API function return values.

    3) Any API declared arguments that are declared as LongPtr must also be declared as ByVal. If they're actually a ByRef argument, the actual data argument (when calling the API) must use VarPtr as a wrapper.

    ----------

    I'm tempted to call this resolved, but I'll wait until I actually perform an Office 64-bit test. Also, I'm still quite open to any other comments that others might have.

    ---------

    EDIT: Just for grins, here are my converted API declarations:

    Code:
    
    Private Declare PtrSafe Function GetDesktopWindow Lib "user32" () As LongPtr
    Private Declare PtrSafe Function GetWindow Lib "user32" (ByVal hWnd As LongPtr, ByVal wCmd As Long) As LongPtr
    Private Declare PtrSafe Function GetWindowTextW Lib "user32" (ByVal hWnd As LongPtr, ByVal lpString As LongPtr, ByVal cch As Long) As Long
    Private Declare PtrSafe Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As LongPtr
    Private Declare PtrSafe Function EnumProcessModules Lib "psapi" (ByVal hProcess As LongPtr, ByVal lphModule As LongPtr, ByVal cb As Long, ByVal lpcbNeeded As LongPtr) As Long
    Private Declare PtrSafe Function GetModuleFileNameExW Lib "psapi" (ByVal hProcess As LongPtr, ByVal hModule As LongPtr, ByVal lpFilename As LongPtr, ByVal nSize As Long) As Long
    Private Declare PtrSafe Function GetModuleBaseNameW Lib "psapi" (ByVal hProcess As LongPtr, ByVal hModule As LongPtr, ByVal lpFilename As LongPtr, ByVal nSize As Long) As Long
    Private Declare PtrSafe Function CloseHandle Lib "kernel32" (ByVal hObject As LongPtr) As Long
    Private Declare PtrSafe Function GetClassNameW Lib "user32" (ByVal hWnd As LongPtr, ByVal lpClassName As LongPtr, ByVal nMaxCount As Long) As Long
    Private Declare PtrSafe Function GetWindowThreadProcessId Lib "user32" (ByVal hWnd As LongPtr, ByVal lpdwProcessId As LongPtr) As Long
    Private Declare PtrSafe Function ShellExecuteW Lib "shell32" (ByVal hWnd As LongPtr, ByVal lpOperation As LongPtr, ByVal lpFile As LongPtr, ByVal lpParameters As LongPtr, ByVal lpDirectory As LongPtr, ByVal nShowCmd As Long) As Long
    
    
    EDIT: There are other pointers on that Windows Data Types page that don't start with LP... It's my understanding that basically anything that's a memory pointer should be declared as LongPtr.
    Last edited by Elroy; Aug 16th, 2020 at 08:43 PM.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  16. #16
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: VB6 to VBA

    Found it: https://jkp-ads.com/Articles/apideclarations.asp
    That site was the most helpful for me, since it had all the API's i needed (incl. UDT's)
    And it actually has the Example for ShellExecute.
    Last edited by Zvoni; Aug 17th, 2020 at 01:10 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  17. #17

  18. #18
    Fanatic Member
    Join Date
    Aug 2016
    Posts
    597

    Re: VB6 to VBA

    Quote Originally Posted by wqweto View Post
    https://gist.github.com/wqweto/39822...aeff1e2e06e630

    Notice the custom preprocessor consts. Make your code future proof and don’t get bitten by VBA8 etc.
    Is mdCollection being compiled in Office VBA? In VB6, there're a lots of red lines.

  19. #19
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: VB6 to VBA

    Quote Originally Posted by DaveDavis View Post
    Is mdCollection being compiled in Office VBA? In VB6, there're a lots of red lines.
    There are red lines in VBA too. Unfortunately both editors are not respecting conditional compilations.

    cheers,
    </wqw>

  20. #20
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,375

    Re: VB6 to VBA

    I certainly only use the VBA7 constant and never actually the Win64 constant.

    Code:
    #If VBA7 Then
    Private Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr
    #Else
    Private Declare Function GetActiveWindow Lib "user32" () As Long
    #End If
    The IDE display the non VBA7 part always as red but does not cause any harm. This way any Pre-Office 2010 is supported.
    Regarding the 64bit or 32bit the LongPtr declaration takes care.

    So why using Win64 ? For me it advises only if LongLong variable is available or not..

    Another example(s):

    Code:
    #If VBA7 Then
    Private Function ProcPtr(ByVal Address As LongPtr) As LongPtr
    ProcPtr = Address
    End Function
    #Else
    Private Function ProcPtr(ByVal Address As Long) As Long
    ProcPtr = Address
    End Function
    #End If
    Code:
    #If VBA7 Then
    Dim IDList As LongPtr
    #Else
    Dim IDList As Long
    #End If
    @ Elroy,
    so when doing every detail properly there is no obstacle in actually supporting Office prior to 2010.

    It's some work though.. but once done you are safe for every VBA version.

  21. #21

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: VB6 to VBA

    @wqweto: Thanks for the tips and code. I haven't had to mess with Variants yet (with the API), and I haven't had to look under the hood of Collections yet (with the VBA). But you've put me on notice that both of those will require consideration when porting from VB6 to the VBA.

    @krool: I hear ya about the VBA7 constant, but I'm truly quite comfortable making the Office 2010 or greater a mandated requirement. It also makes "looking at" the code much easier on the eyes.

    I know I can do it, but looking at something like the following just starts bringing on a headache:

    Code:
    
    #If VBA7 Then
    Private Function hWndOfNextTopLevelWindow(hWnd As LongPtr) As LongPtr
    #Else
    Private Function hWndOfNextTopLevelWindow(hWnd As Long) As Long
    #End If
        ' if hWnd=0 then the first window is returned.
        '
        Const GW_HWNDNEXT   As Long = 2&
        Const GW_CHILD      As Long = 5&
    #If VBA7 Then
        Dim h As LongPtr
    #Else
        Dim h As Long
    #End If
        '
        '
        If hWnd = 0& Then
            ' The desktop is the highest window
            h = GetDesktopWindow()
            ' It's first child is the 1st top level window
            hWndOfNextTopLevelWindow = GetWindow(h, GW_CHILD)
        Else
            hWndOfNextTopLevelWindow = GetWindow(hWnd, GW_HWNDNEXT)
        End If
    End Function
    
    
    Whereas, the following is so much more pleasant:

    Code:
    
    Private Function hWndOfNextTopLevelWindow(hWnd As LongPtr) As LongPtr
        ' if hWnd=0 then the first window is returned.
        '
        Const GW_HWNDNEXT   As Long = 2&
        Const GW_CHILD      As Long = 5&
        Dim h As LongPtr
        '
        '
        If hWnd = 0& Then
            ' The desktop is the highest window
            h = GetDesktopWindow()
            ' It's first child is the 1st top level window
            hWndOfNextTopLevelWindow = GetWindow(h, GW_CHILD)
        Else
            hWndOfNextTopLevelWindow = GetWindow(hWnd, GW_HWNDNEXT)
        End If
    End Function
    
    
    IDK, just a decision I made that I'm comfortable with.

    I do appreciate the information though.

    -----------------

    EDIT:

    Quote Originally Posted by Krool View Post
    So why using Win64 ? For me it advises only if LongLong variable is available or not.
    I wasn't aware of that either. That's curious too because (in a Variant) it's available in VB6. And also, with the Currency type, we're already doing the 64-bit arithmetic. It seems really lazy on Microsoft's part that the LongLong isn't available in the VBA 32-bit. But, I tested, and it's not.
    Last edited by Elroy; Aug 17th, 2020 at 09:46 AM.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  22. #22

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: VB6 to VBA

    Ok, bought a copy of Office 2019 64-bit and everything's working. So I'm feeling pretty good about being able to convert these API declarations.

    I did wind up breaking several Word macros that I use (with the Office 64-bit installation). Apparently, when running the VBA in 64-bit, any API declarations must have the PtrSafe keyword. Now that I know how to do it, it wasn't a big deal to convert them.

    I'm calling this one resolved.

    EDIT: Also, everything I've learned is basically in this Microsoft article. However, as is often the case for me, I read an article like that before I start and it doesn't sink in, I beat my head against the wall learning it, and then I go back and re-read the article saying "of course" to myself as I see everything laid out.

    All Declare statements must now include the PtrSafe keyword when running in 64-bit versions of Office.
    need to locate and modify all data types within these Declare statements that reference handles or pointers to use the new 64-bit compatible LongPtr type
    Last edited by Elroy; Aug 17th, 2020 at 07:43 PM.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  23. #23
    Fanatic Member
    Join Date
    Aug 2016
    Posts
    597

    Re: VB6 to VBA

    Quote Originally Posted by Elroy View Post
    Ok, bought a copy of Office 2019 64-bit and everything's working. So I'm feeling pretty good about being able to convert these API declarations.

    I did wind up breaking several Word macros that I use (with the Office 64-bit installation). Apparently, when running the VBA in 64-bit, any API declarations must have the PtrSafe keyword. Now that I know how to do it, it wasn't a big deal to convert them.

    I'm calling this one resolved.

    EDIT: Also, everything I've learned is basically in this Microsoft article. However, as is often the case for me, I read an article like that before I start and it doesn't sink in, I beat my head against the wall learning it, and then I go back and re-read the article saying "of course" to myself as I see everything laid out.
    VB6 is still alive, VBA play a big role.
    Can you please make a summary on a new thread in CodeBank? thanks.

  24. #24

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: VB6 to VBA

    Quote Originally Posted by DaveDavis View Post
    VB6 is still alive, VBA play a big role.
    Can you please make a summary on a new thread in CodeBank? thanks.
    Hi Dave,

    My summary is really in post #15 of this thread. And I didn't really cover all the bases. I only covered the basis I needed for the project I'm working on. Two big areas I neglected (because I just don't need them right now) are pointers and/or handles in UDTs, and Variants. LaVolpe mentioned both of those above. Variants are actually 24 bytes in a 64-bit environment (opposed to 16 bytes in 32-bit environments). Also, any use of CopyMemory will probably have to be closely examined when trying to make something both 32-bit and 64-bit compatible.

    Also, I'm sort of in the wrong forum, and should be in the Office Development forum. I just know the folks over here better (and the procedures are being brought over from VB6).

    Dave, you're more than welcome to take anything I've posted in this thread, and re-post it anywhere you like ... no attribution needed.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  25. #25
    Hyperactive Member
    Join Date
    Feb 2015
    Location
    Colorado USA
    Posts
    261

    Re: VB6 to VBA

    I try to make all my code run on VB6 and VBA for the most part and I have dealt with the 32 vs 64-bit VBA thing for 10 years since Office 2010 came out. I have converted hundreds of API calls to work with Win64/VBA7 so if you need some help, drop me a line.

    I have never figured out the PtrSafe thing. The only thing that makes sense is that it more or less forces you to at least pay attention to it.

    I use both VBA7 and Win64 a lot. In fact, I switch back and forth a lot as I try to ensure both API declarations work.

    I don't get hung up on the use of Long's in Win64. Other than handles essentially all of the Win32 calls (32 or 64 bits) are Longs. In 64-bit world, a Long is actually returned as a 64-bit value but the top 4 bytes are zeroed out. VBA takes care of converting the 64-bit value to whatever you have specified such as a Long. This is similar to returning an Integer in 32-bit calls where the top 2 bytes are zeroed and cut back to an Integer for your code.

    Perhaps one thing that is escaping most users is that it appears Microsoft is strongly veering away from COM automation in Office 365, 32 and 64-bit. It used to be a simple thing to code with early binding and then switch to late binding for distribution but that's getting increasingly difficult. I have a copy of Office 365 on a laptop and it is hard just finding where the EXE's are located. Neither early or late binding works and I had to come up with some interesting workarounds to be able to automate Excel from VB6. I can still get automation to work but I have to shell to Excel and then find the new Excel instance and then I can connect via late binding to it. It's a hassle but at least I can get it to work. I'll clean up my code and put it in the CodeBank.

  26. #26

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: VB6 to VBA

    Thanks for the comments, MountainMan.

    Quote Originally Posted by MountainMan View Post
    I have never figured out the PtrSafe thing. The only thing that makes sense is that it more or less forces you to at least pay attention to it.
    That's about the best I can determine as well. If running in 64-bit, it just forces you to take a look at the API declarations.

    Quote Originally Posted by MountainMan View Post
    I don't get hung up on the use of Long's in Win64. Other than handles essentially all of the Win32 calls (32 or 64 bits) are Longs. In 64-bit world, a Long is actually returned as a 64-bit value but the top 4 bytes are zeroed out. VBA takes care of converting the 64-bit value to whatever you have specified such as a Long. This is similar to returning an Integer in 32-bit calls where the top 2 bytes are zeroed and cut back to an Integer for your code.
    Yeah, for the most part, we don't need to worry about that stuff. However, if it's a LongPtr, we do. I haven't had the need just yet, but I'm sure I'll eventually get into some CopyMemory stuff (or GetMem# calls). Regarding copying these pointers, I thought about doing something like the following:

    Code:
    
    #If Win64 Then
        Public Declare PtrSafe Function GetMemPtr Lib "msvbvm60" Alias "GetMem8" (ByVal SourcePtr As LongPtr, ByVal DestPtr As LongPtr) As Long   ' Always use VarPtr.
    #Else
        Public Declare PtrSafe Function GetMemPtr Lib "msvbvm60" Alias "GetMem4" (ByVal SourcePtr As LongPtr, ByVal DestPtr As LongPtr) As Long   ' Always use VarPtr.
    #End If
    
    
    But I'm not sure that would work, as I'm guessing there's not 64-bit code in the msvbvm60 library like there is in the other libraries. To copy pointers in either 32 or 64-bit, it's probably going to take some "shuffling" with CopyMemory each time it's done. (Or just do it with a simple Let statement.)

    Quote Originally Posted by MountainMan View Post
    I can still get automation to work but I have to shell to Excel and then find the new Excel instance and then I can connect via late binding to it. It's a hassle but at least I can get it to work. I'll clean up my code and put it in the CodeBank.
    I'd really appreciate it if you could do that, as I'm sure I'll eventually run into Office 365, and my primary VB6 application (with tons of Word and Excel automation) isn't going anywhere for quite some time.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  27. #27
    Hyperactive Member
    Join Date
    Feb 2015
    Location
    Colorado USA
    Posts
    261

    Re: [RESOLVED] VB6 to VBA

    There are several nifty little memory routines in msvbvm60 that I use a lot including GetMem1, GetMem2, GetMem4, GetMem8, PutMem1, PutMem2, PutMem4, PutMem8 and the aliased function GetMemPtr and PutMemPtr. Uncfortunately there is no 64-bit version of msvbvm60.dll so I have had to re-create these in Win64 versions using RtlMoveMemory:

    Code:
    #If Win64 Then
    Public Sub PutMem1(ByVal Addr As LongPtr, ByVal newValue As Byte)
    RtlMoveMemory ByVal Addr, newValue, 1
    End Sub
    
    Public Sub PutMem2(ByVal Addr As LongPtr, ByVal newValue As Integer)
    RtlMoveMemory ByVal Addr, newValue, 2
    End Sub
    
    Public Sub PutMem4(ByVal Addr As LongPtr, ByVal newValue As Long)
    RtlMoveMemory ByVal Addr, newValue, 4
    End Sub
    
    Public Sub PutMem8(ByVal Addr As LongPtr, ByVal newValue As Currency)
    RtlMoveMemory ByVal Addr, newValue, 8
    End Sub
    
    Public Sub GetMem1(ByVal Addr As LongPtr, ByRef dstValue As Byte)
    RtlMoveMemory dstValue, ByVal Addr, 1
    End Sub
    
    Public Sub GetMem2(ByVal Addr As LongPtr, ByRef dstValue As Integer)
    RtlMoveMemory dstValue, ByVal Addr, 2
    End Sub
    
    Public Sub GetMem4(ByVal Addr As LongPtr, ByRef dstValue As Long)
    RtlMoveMemory dstValue, ByVal Addr, 4
    End Sub
    
    Public Sub GetMem8(ByVal Addr As LongPtr, ByRef dstValue As Currency)
    RtlMoveMemory dstValue, ByVal Addr, 8
    End Sub
    
    Public Sub Mem1Any(ByVal Addr As LongPtr, ByVal dstLoc As LongPtr)
    RtlMoveMemory ByVal dstLoc, ByVal Addr, 1
    End Sub
    
    Public Sub Mem2Any(ByVal Addr As LongPtr, ByVal dstLoc As LongPtr)
    RtlMoveMemory ByVal dstLoc, ByVal Addr, 2
    End Sub
    
    Public Sub Mem4Any(ByVal Addr As LongPtr, ByVal dstLoc As LongPtr)
    RtlMoveMemory ByVal dstLoc, ByVal Addr, 4
    End Sub
    
    Public Sub Mem8Any(ByVal Addr As LongPtr, ByVal dstLoc As LongPtr)
    RtlMoveMemory ByVal dstLoc, ByVal Addr, 8
    End Sub
    
    Public Sub Put1Any(ByVal Addr As LongPtr, ByVal dstLoc As LongPtr)
    RtlMoveMemory ByVal Addr, ByVal dstLoc, 1
    End Sub
    
    Public Sub Put1Any(ByVal Addr As LongPtr, ByVal dstLoc As LongPtr)
    RtlMoveMemory ByVal Addr, ByVal dstLoc, 2
    End Sub
    
    Public Sub Put1Any(ByVal Addr As LongPtr, ByVal dstLoc As LongPtr)
    RtlMoveMemory ByVal Addr, ByVal dstLoc, 4
    End Sub
    
    Public Sub Put1Any(ByVal Addr As LongPtr, ByVal dstLoc As LongPtr)
    RtlMoveMemory ByVal Addr, ByVal dstLoc, 8
    End Sub
    
    Public Sub GetPointer(ByVal Addr As LongPtr, ByVal dstValue As LongPtr)
    RtlMoveMemory ByVal dstValue, ByVal Addr, 8
    End Sub
    
    Public Sub PutPointer(ByVal Addr As LongPtr, ByVal newValue As LongPtr)
    RtlMoveMemory ByVal Addr, ByVal newValue, 8
    End Sub
    
    Public Sub PutMemLL(ByVal Addr As LongPtr, ByRef newValue As LongLong)
    RtlMoveMemory ByVal Addr, ByVal newValue, 8
    End Sub
    
    Public Sub GetMemLL(ByVal Addr As LongPtr, ByVal dstValue As LongLong)
    RtlMoveMemory ByVal dstValue, ByVal Addr, 8
    End Sub
    #End If
    Thank goodness RtlMoveMemory is available is 64-bit VBA via kernel32.dll which despite its name is available in 64-bit Windows.

    BTW, that's anther reason to use Wn64 instead of VBA7 in most cases. If you tested for VBA7 then these substitute functions would be used on 32-bit VBA even though the 32-bit msvbvm60.dll is available. Using Win64 (at least for these) makes it where these substitutes are only used when using 64-bit VBA.

    I'll get the automation code cleaned up and posted in a few days.

  28. #28

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: [RESOLVED] VB6 to VBA

    Hmmm, actually, I just tested some automation, and mine works fine. And all I've got installed on my machine right now is Office 2019 64-bit. So I think I'm in good shape. I did see a recent VBForums post of someone else who's having problems, but I think that's another issue.

    The aliasing of GetMem# is a good idea though. I'll probably use that when I get to that point. Using the Optional declaration, it could probably be done right in the API declaration (although source and dest would still be reversed).
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  29. #29
    Hyperactive Member
    Join Date
    Feb 2015
    Location
    Colorado USA
    Posts
    261

    Re: [RESOLVED] VB6 to VBA

    Office 2019 is a one-time purchase that I believes installs and behaves the same as earlier versions of Office. However, Office 365 (now called Microsoft 365) is a subscription-based service and although I only have a sample population of 1, it sure seems to me that it installs differently. It is not installed in the normal location. This copy is a 32-bit version so you would think it would be installed in the Program Fules (x86) folder with the rest of the 32-bit programs.

    Microsoft actually put it here:

    C:\Program Files\WindowsApps\Microsoft.Office.Desktop.Excel_16051.13029.20344.0_x86__8wekyb3d8bbwe\Office16\exc el.exe

    along with the rest of Office 365 and I can't get to that location through Explorer or anything else I have tried. They clearly want it hidden away. I also have had no success at all automating to it via early or late binding but I can get it to be automated if I first get it running (strangely enough it is not part of the Path but if you just run Excel.exe without a path either from a CMD prompt or a Shell command from VB6/VBA, lo and behold it runs! But automation doesn't work in the normal sense. I am not sure how they do this but I guess if you write the operating system you know all of the workarounds. I also don't know why they have done this.

    It is clear that they are trying to get everybody off of VBA and onto Python or something else that just works via the Excel object model. I am convinced that's why they never gave any real support for forms and ocx's now for the past 10+ years and it doesn't look like that's ever going to change. They went through the hassle of making the Office VBA code run as 64-bit so it wouldn't have been that big of a deal to convert some of the ocx's as well. I think they had to do the 64-bit VBA or they would have lost their huge industrial base as it moved slowly to 64-bits over the years but they sure aren't making it easy for any new VBA stuff to emerge in the 64-bit world.

    Since they converted the base VBA to 64-bits it doesn't seem to have been too difficult to convert VB6 to 64-bit as well and we could convert or write wonderful things for 64-bit VBA but they chose not to and since it has been over 10 years since 64-bit VBA came out I don't think they ever will. Oh well.

  30. #30

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: [RESOLVED] VB6 to VBA

    Quote Originally Posted by MountainMan View Post
    Office 2019 is a one-time purchase that I believes installs and behaves the same as earlier versions of Office. However, Office 365 (now called Microsoft 365) is a subscription-based service and although I only have a sample population of 1, it sure seems to me that it installs differently. It is not installed in the normal location. This copy is a 32-bit version so you would think it would be installed in the Program Fules (x86) folder with the rest of the 32-bit programs.

    Microsoft actually put it here:

    C:\Program Files\WindowsApps\Microsoft.Office.Desktop.Excel_16051.13029.20344.0_x86__8wekyb3d8bbwe\Office16\exc el.exe

    along with the rest of Office 365 and I can't get to that location through Explorer or anything else I have tried. They clearly want it hidden away. I also have had no success at all automating to it via early or late binding but I can get it to be automated if I first get it running (strangely enough it is not part of the Path but if you just run Excel.exe without a path either from a CMD prompt or a Shell command from VB6/VBA, lo and behold it runs! But automation doesn't work in the normal sense. I am not sure how they do this but I guess if you write the operating system you know all of the workarounds. I also don't know why they have done this.

    It is clear that they are trying to get everybody off of VBA and onto Python or something else that just works via the Excel object model. I am convinced that's why they never gave any real support for forms and ocx's now for the past 10+ years and it doesn't look like that's ever going to change. They went through the hassle of making the Office VBA code run as 64-bit so it wouldn't have been that big of a deal to convert some of the ocx's as well. I think they had to do the 64-bit VBA or they would have lost their huge industrial base as it moved slowly to 64-bits over the years but they sure aren't making it easy for any new VBA stuff to emerge in the 64-bit world.

    Since they converted the base VBA to 64-bits it doesn't seem to have been too difficult to convert VB6 to 64-bit as well and we could convert or write wonderful things for 64-bit VBA but they chose not to and since it has been over 10 years since 64-bit VBA came out I don't think they ever will. Oh well.
    -----------------

    Hmmm, yes, I'm quite aware that Office 2019 is a one-time purchase. However, I didn't think there was much difference in that and 365, with Office 365 just being Microsoft's attempt to continue making money each year, with us all paying a subscription (which many business are perfectly willing to do).

    In fact, my wife works from home, but her employer pays for her version of Office (which is 365 32-bit). I just looked on her machine, tracing back the Start button Word icon, and found it went here:

    "C:\Program Files (x86)\Microsoft Office\root\Office16\WINWORD.EXE"

    That looks like the same ole Word program I've used for years, just the latest version.

    Admittedly, I don't keep up with the VBA politics of where Microsoft is trying to head. But it seems like all my VBA stuff works fine on Office 365. Also, the VBA IDE seems to be there in all its glory.

    So I'm a bit confused.

    But anyway, take care,
    Elroy

    EDIT: Maybe part of the differences you and I are having are that I do all my automation using late-bound objects. I just use something like the following to get Word started for automation:

    Code:
    
    Dim wrd As Object
    Set wrd = CreateObject("Word.Application")
    
    I've got no recent experience at all with attempting to use any component of Office via early-bound. On rare occasion I'll do it, just to have Intellisense for the object and method hierarchy, but I never put that into production, and even doing the Intellisense thing is rare for me. I just use the MSDN to figure out what I need (or possibly record a Macro to let the VBA show me).

    ------------

    EDIT2: I'll wholeheartedly agree that it's a shame that Microsoft won't upgrade the VB6 IDE (and compiler) to compile a 64-bit version. In the MSDN for the VBA, they talk about getting OCX upgrades (for 64-bit) from the suppliers. But, if we're trying to stay in pure VB code, how are we suppose to do that? AFAIK, there's no VB 64-bit OCX compiler anywhere.
    Last edited by Elroy; Aug 19th, 2020 at 10:59 PM.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  31. #31
    Hyperactive Member
    Join Date
    Feb 2015
    Location
    Colorado USA
    Posts
    261

    Re: [RESOLVED] VB6 to VBA

    I don't think early-bound has anything to do with the automation problem. In fact, a few programs I have been developing that are still early-bound won't even compile with the weird Office situation. Neither does late-bound. At least with late-bound the program runs until it gets to the CreateObject line and with error handling yo could recover from it.

    Attached is a very small little project that show the code for the Excel assignment. It should work on all versions of Office/Excel.

    Perhaps for some reason I got a very strange installation of Office on my laptop. I don't think so but it is possible. I will be curious what others have to say, especially those with Office 365.
    Attached Files Attached Files

  32. #32

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: [RESOLVED] VB6 to VBA

    Quote Originally Posted by MountainMan View Post
    I don't think early-bound has anything to do with the automation problem. In fact, a few programs I have been developing that are still early-bound won't even compile with the weird Office situation. Neither does late-bound. At least with late-bound the program runs until it gets to the CreateObject line and with error handling yo could recover from it.

    Attached is a very small little project that show the code for the Excel assignment. It should work on all versions of Office/Excel.

    Perhaps for some reason I got a very strange installation of Office on my laptop. I don't think so but it is possible. I will be curious what others have to say, especially those with Office 365.
    Ok, I just tried your little program on my computer and my wife's computer, with your early-bound flag turned off.

    On my computer (Office 2019 64-bit) it worked fine.
    On my wife's computer (Office 365 32-bit) it also worked fine.

    Both cases, I got this message:
    Name:  ExcelTest.png
Views: 1055
Size:  18.5 KB

    Maybe that'll help you run down your problems.

    ------------

    EDIT: And say, MountainMan, if you'd really like to continue pursuing these problems, maybe it'd be better if you started a new thread. We're really no longer on the topic of converting API declarations from VB6 to VBA.
    Last edited by Elroy; Aug 20th, 2020 at 09:20 AM.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

Posting Permissions

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



Click Here to Expand Forum to Full Width