How to register library/reference from vba code?

[Albert D. Kallal]

I have used zip files that includes runtimes for installs, but only if the target has no Access installed.

Well, I do have some interop apps that have constants, so do you suggest hardcoding magic values as a workaround?

Yes, 100% correct in terms of hard coding such values.

And a simple example?
While I used the Office "FileDialog"?
It does require the Office 16 library?
You can over the years have used late binding, and thus that file dialog code would become:

' Original posted code:
Dim f    As FileDialog
Set f = Application.FileDialog(msoFileDialogFilePicker)
f.Show
MsgBox "file choose was " & f.SelectedItems(1)

'You can late bind if you wish:
'If you remove the reference to the 16.0 object library, then the following
' code will work without any references:

   Dim f    As Object
   Set f = Application.FileDialog(3)
   f.AllowMultiSelect = True
   f.Show

   MsgBox "file choosen = " & f.SelectedItems.Count

So, yes, those constants (like msoFileDialogFilePicker in this example), do require changes.

As noted, you can during development do a "regasm", and use early binding.

In fact Visual Studio has a option to do a regasm for you during the build.
(thus, this feature/option ONLY applies to your dev computer - it does NOT change the .dll one bit).
That option is this one in VS:

So, if you tick above, then .net will do a regasm for you. This VERY much suggests then that you MUST force the project to x86, or x64, and ANY CPU will not work. But, if you do check the above box, and build? Then you launch Access, and under VBA->tools->references?
You will indeed find the COM object.

So, the above can be handy during development, but that Register option as noted, is ONLY for development - it does not change the project or .dll in anyway.

So, often during development, we often do use early binding. And I suppose the REALLY cool question?
Is how to debug the .net code when it called from VBA? (and again this is really easy, and really cool).
If you wish, do ask, and I'll post how to do this trick - but, probably in the next day or so....


R
Albert

 

[Albert D. Kallal]

Yes, that FileDialog requires a office reference. To be fair, that is one reference that RARE breaks.

>>My code is clearly using early binding, so you're saying I cannot call the msoFilePicker if I use late binding because that office picker is dependent on a specific Office library version?

Actually, I'm saying you can!

And the reason is that at least ONE office library will be working and registered on your computer if you have office programs installed.

So, if you go late binding, you can make a safe bet such code will work!!!

So that library will exist if you have Access (even JUST the runtime installed).

But, with late binding, you don't care if it is 16, 13, or who knows what!!!

But, it will work with late binding.....

I thus suggest that you still risk a broken reference (with early binding).

As noted, the above example is not the best, since office on startup will fix and re-set that reference in near all cases.

The basic concept here?
Well, the less references you have, the less chance of something breaking and going wrong......

But, like so many things? I don't want to say this is a 0 or 1 type of issue (ONLY one way, or ONLY the other way)!

As developers, we should always have some flexibility in preaching such choices.

So, while I suggest less references = better?

Well, sure, but some references are better then others, and the FileDialog one (requires the Office 16, or Office xx library in tools->references)?

It almost for sure will exist anyway, and it rare breaks or goes wrong....

I as a general rule have used late binding for the FileDialog, and thus not setup/used the Office reference.
However, the filedailog example is one that I would not much worry about....

R
Albert

 

[Josef P.]

Note: late/early binding
If you create a tlb file and link it via the file path, you can use early binding without having to register the dll. This is useful for development.

 

[isladogs]

If you have the excellent Office add-in VBE_Extras, you can use 'special binding' to get all the benefits of early binding whilst using late binding i.e. you get the best of both worlds! For more details, see

Special binding

If you develop your VBA code using early binding but switch to late binding when deploying, then 'special binding' may be useful to you.

www.thevbahelp.com

 

[RonPaii]

When using Albert's loader, I wrap the external dll inside a VBA class. This allows the class to do lazy loading and handle broken references giving me intel-sense in VBA.

On a side note, I have always had an issue were the 1st call of LoadLibrary returning a LastDllError = 203 "The system could not find the environment option that was entered". Albert has never been able to duplicate it. The fix is simple, trap the error, if 203 retry LoadLibrary on the 1st attempt. All other calls to LoadLibray work on the 1st attempt.

    Private Declare PtrSafe Function LoadLibrary _
        Lib "kernel32" _
        Alias "LoadLibraryA" _
            (ByVal lpLibFileName As String) _
        As LongPtr

    Private Declare PtrSafe Function MyCreateObject _
        Lib "nloader64.dll" _
            (ByVal strDll As String, _
             ByVal strClass As String) As Object

 

[sonic8]

So, late binding is a REALLY nice approach

Does your .NetLoader also support connecting the even sinks?
If not, you should mention that one will lose the ability handle events with Late Binding. - With many objects/libraries this can be tolerated but with some it is a complete deal breaker.


I did a presentation on using .Net libraries, including User Controls, with Access/VBA at the AEK10 conference. The slides and script included with the downloadable files are in German but you probably can push it though an auto-translator.

 

[Josef P.]

You still have to register the underlying COM DLL on the target machine where the app is going to run. Windows needs to know how to locate and instantiate the COM component at runtime. The type library file only provides the interface definition, it doesn't replace the need for COM registration for runtime execution.

The type library is only for types (interface).
The .net COM DLL types do not need to be added to the registry if you load them with mscoree.CorRuntimeHost & co, for example.

Steps:
1. Reference to tlb => VBE can check the interface
2. Load com dll class instance via mscoree.CorRuntimeHost / mscorlib.AppDomain
3. Use COM instance

 

[Albert D. Kallal]

The type library is only for types (interface).
The .net COM DLL types do not need to be added to the registry if you load them with mscoree.CorRuntimeHost & co, for example.

Steps:
1. Reference to tlb => VBE can check the interface
2. Load com dll class instance via mscoree.CorRuntimeHost / mscorlib.AppDomain
3. Use COM instance

Well, if you add a reference to the runtime host, then that's another reference that can break.
So, with my .net loader, then zero additional references are required.
So, if the .net .dll is written to support regasm, (exposes a com interface) then it should work with my loader.
Only difference is that you just have to drop the .dll in the same folder as the Access front end - nothing more is required
(well, the .net loader module in Access is required).

R
Albert

 

[Albert D. Kallal]

Does your .NetLoader also support connecting the even sinks?

I not tested nor attempted to sink events into such .dll's. But, few require this ability.

So, for last 10+ years of building interfaces to accounting packages, PDF merging, and a whole lot more in .net?

Never needed or wanted to sink events into such external library code anyway.

However, while ActiveX, COM, oleDB are all actually the same underlying "COM" interface?

The ActiveX road is COM again, but it also includes a UI part - and right now, I would say that events, or using UI controls?

Probably would not work, since ActiveX controls need additional registry and setup - something not being done here....

I should also point out that the nloader.dll's I use?

They are .net code, but using "dll exports", they appear as a win32 standard .dll (unmanaged code) to applications with entry points.

I did this, since then I can build win 32 .dll's with .net (and they appear as standard un-managed .dlls - the first type I note above, and ones that never did require to be registered - but then again, those are not COM objects....).

So, I not really addressed the ActiveX issue as of yet (more R&D reqiured).

Of more need right now? I want to get COM+ working. This would allow interface from x32 code from x64, (or reverse) when building COM objects.

Hence, I would like to run Access x64, but use a COM interface to a x32 bit Accounting package.

And as my posted example shows? It's super easy to build a .net COM object - not even required to write interface code!

So, this example is a clean, dead simple, and allows one to use .net code from VBA - it's a "kiss" example.

In fact, all those .net examples with writing all that complex interface code etc.? Ones that go on for pages and pages?
One's that have/show interface code, but don't explain why you need as such? (and in 99% of cases you do NOT!!!).

Well, the Access community just passed on that stuff - too much work, too complicated for new users.....

R
Albert