I have code for an excel add-in I wrote over 20 years ago that I find myself needing again. It works fine in Windows XP and Excel 2003 in a virtualbox. In the VB6.0 IDE, I can load the code and make the dll which I can register as an excel add-in and use it in Excel 2003 within the virtual box. I can also use the VB6 IDE to debug it. I am looking to do the minimum to rewrite this for a short-term need to share with a small set of people in an organization using a current 64 bit version of Excel. They are in a locked-down environment so having them all run an old 32 bit version of Excel for this functionality is not an option.
Is there a version of Visual Basic which allows compiling for 64bit? I find references to what sounds like a VB7 IDE that would allow this. If it does exist, is it possible to get ahold of that anywhere? And will it actually make an add-in that will work with a current 64-bit version of Excel?
You can set registry entries to allow x64 to load x32 libraries.
So COM ignores bitness in EXE files. So any EXE file can be loaded by any COM client (and vice versa though no one cares). By setting registry entries you force your DLL into DLLHOST.exe.
COM loads a 64 bit stub into Excel and communicates to the stub. As far as Excel can tell it has loaded a 64 bit dll.
One problem is that one registry key you don't have permission to change. You must take ownership and give yourself permission to add an entry. There is no inbuilt tool to do this automatically, unlike for files.
This following reg file enables the MSScript Control, which allows you to execute VBScript and JScript as macro languages. Microsoft wants to kill VBScript and JScript and MS Script Control. So it is only available as a 32 bit dll for compatibility. It takes under a minute to add macro languages to any program. Contrast to VBA which you have to pay to use. Note the one line that requires TakeOwnership.
Windows Registry Editor Version 5.00
;MSScript.reg
[HKEY_CURRENT_USER\SOFTWARE\Classes\CLSID\{0E59F1D5-1FBE-11D0-8FF2-00A0D10038BC}]
@="ScriptControl Object"
"AppID"="{0E59F1D5-1FBE-11D0-8FF2-00A0D10038BC}"
[HKEY_CURRENT_USER\SOFTWARE\Classes\Wow6432Node\CLSID\{0E59F1D5-1FBE-11D0-8FF2-00A0D10038BC}]
@="ScriptControl Object"
"AppID"="{0E59F1D5-1FBE-11D0-8FF2-00A0D10038BC}"
;This one needs to be done manually
[HKEY_CURRENT_USER\SOFTWARE\Classes\Wow6432Node\AppID\{0E59F1D5-1FBE-11D0-8FF2-00A0D10038BC}]
"DllSurrogate"=""
[HKEY_CURRENT_USER\SOFTWARE\Classes\AppID\{0E59F1D5-1FBE-11D0-8FF2-00A0D10038BC}]
"DllSurrogate"=""
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{0E59F1D5-1FBE-11D0-8FF2-00A0D10038BC}]
@="ScriptControl Object"
"AppID"="{0E59F1D5-1FBE-11D0-8FF2-00A0D10038BC}"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Wow6432Node\CLSID\{0E59F1D5-1FBE-11D0-8FF2-00A0D10038BC}]
@="ScriptControl Object"
"AppID"="{0E59F1D5-1FBE-11D0-8FF2-00A0D10038BC}"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Wow6432Node\AppID\{0E59F1D5-1FBE-11D0-8FF2-00A0D10038BC}]
"DllSurrogate"=""
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\AppID\{0E59F1D5-1FBE-11D0-8FF2-00A0D10038BC}]
"DllSurrogate"=""
The reference is here but only DLLSurrogate is of interest to you - https://learn.microsoft.com/en-us/windows/win32/com/appid-key.
A more of a background reference - https://learn.microsoft.com/en-us/windows/win32/winprog64/process-interoperability.