How to register library/reference from vba code? (3 Viewers)

[Albert D. Kallal]

Ok, so let's put all the steps together as to what you need for this to work.

First up, you need the code module (and 2 .dll's) from the sample PDF merge utility I posted here.

Assuming you have the above?

Ok, then let's from scratch create a .net .dll for use from VBA.

As noted, the .net world has near un-limited features, and often such features are built into .net.
And when they are not? Well, there is a gazillion NuGet libaries you can add to .net.
(this is how the PDF merge sample was made - I used iTextSharp - a common well known PDF library for .net).

Ok, so, let's do somthing simple. We will in an Access form, display a image. Then use .net to rotate the image.

In fact, you can Google, or even GPT how to do this in vb.net.

The .net code looks like this to rotate a given image (file).

' Load the image from file
        Dim imagePath As String = "C:\path\to\your\image.jpg"
        Dim img As Image = Image.FromFile(imagePath)

        ' Rotate the image 90 degrees clockwise
        img.RotateFlip(RotateFlipType.Rotate90FlipNone)

        ' Save the rotated image to a new file
        Dim outputPath As String = "C:\path\to\your\rotated_image.jpg"
        img.Save(outputPath, ImageFormat.Jpeg)

        ' Clean up
img.Dispose()

So, really, just a few lines of vb.net.

So, now let's create a .net .dll for use from Access.

So, assuming one has Visual Studio installed (the free community edition works just fine for this.

So, the first step is to create a new project in VS. We want to create a SUPER simple project, and the correct program is to create a .net class project.

Perhaps only trick is to make sure you choose/create the correct kind of project. One "challenge" in .net is that over the years, .net has become the ultimate Swiss army knife - and there are BOATLOADS of projects too choose from. This is what happens when a company keeps throwing money at a particular platform - it just grows and grows!!! It would be un-kind of me to state that VS now has too many project types, ranging from web, to desktop to Android development, and much more!!!

So, we need to choose + create a .net framework (not .net standard, or what was/is called .net core).

So, in create new project, choose this project type:

Ok, so now we have this:

And then we now have this:

(but, let' right click on the class, and rename it, like this:

Now, add a reference to System.Drawring to use the image stuff in .net.

Hence this:

Ok, so let's add a simple method (sub) to this code, based on that vb.net code to rotate that image.

So, our class becomes this:

Imports System.Drawing
Imports System.Drawing.Imaging
Imports System.Runtime.InteropServices


<ClassInterface(ClassInterfaceType.AutoDual)>
Public Class clsRotate

    Public Sub RotatePic(imagePath As String, intRoate As Integer)

        ' Load the image from file
        Dim img As Image = Image.FromFile(imagePath)

        ' Rotate the image 90 degrees clockwise

        Select Case intRoate
            Case 1
                img.RotateFlip(RotateFlipType.Rotate90FlipNone)
            Case 2
                img.RotateFlip(RotateFlipType.Rotate180FlipNone)
            Case 3
                img.RotateFlip(RotateFlipType.Rotate270FlipNone)
        End Select

        ' Save the rotated image
        img.Save(imagePath, ImageFormat.Jpeg)

        ' Clean up
        img.Dispose()

    End Sub


End Class

So, you can adopt the above cookie cutter design over and over.
You need to add (as per above), the following:

Imports System.Runtime.InteropServices

And this just before the class:
<ClassInterface(ClassInterfaceType.AutoDual)>

And only one more step, and we have our .net dll.

In the project settings, under assembly, check the box "Make assembly COM-Visible"

this one:

If you build this project, and want to use regasm? Then I suggest you force the project to x86, or x64 if using x64 bit Access/office.

However, as noted, with our .net loader, we don't need (nor care) to use regasm.

So, I'll reply a bit later as to what the VBA code looks like, but really, after anyone done the above, you are now on the road to freely building .dll'ss for use with Access. And EVEN if you don't want to use my .net loader, the above will work just fine using regasm.exe, and setting a reference from VBA .....

R
Albert

 

[BlueSpruce]

Ok, a great question!

>Could you give us some more details on how to create an external library, usable in Access

The short answer? (with some caveats attached?)

If you have some existing .net .dll’s and were/are using regasm?

Well, in theory, you don’t have to change anything at all using my .net loader

(except that you don’t need regams.exe anymore!!).


Now, when I said “sort of”?

Well, you don’t have to change any of the .net code, but on the VBA side?


Well, you do have to adopt what we call late binding.

However, I’m hoping that over time, like most developers, one moved towards “late binding” anyway.

Thus, we are NOT using a tools->reference(s) in VBA.

Why?

Well, with late binding, then you don’t have to reference a “specific” version of Outlook, word, Excel etc., or in this case the .net dll.


On my site, I posted a really cool word merge utility. I wrote that word merge utility about 20 years ago. And today, if you go and grab that word merge code, and drop it into your existing Access application?

It will just work, and the reason of course is that I adopted late binding.

So, late binding is a REALLY nice approach if you going to deploy Access applications in say a corporate environment, since what happens if they upgrade office?

Well, now your tools->references etc. in VBA will in near all cases break. But, with late binding, they don’t break, and continue to work!!!

So, what do I mean by late binding?


Well, code like this:

      Dim MyWord as new Word.Application

Becomes:
      ‘Dim MyWord as new Word.Application     ‘ early bind – for devlopment
       Dim MyWord as Object
       Set MyWord = CreateObject(“Word.Application”)

I'm hoping all have done the above.

So, when I suggested/hinted that you don’t have to change “any thing” for current code you have, and my .net loader?

Well, you do have some minor steps:

First, tools->references, and remove the reference to the COM library.

We not going to use tools->references anymore, and as noted, this is good practice to adopt, since you then (often) eliminate broken references.

Note that some developers will during first development round and testing?

Sure, use early binding, and use tools->references from VBA.

This gives nice error checking, and gives intel-sense in VBA.

But, once you have everything all nice and fuzzy warm and working?

Then (hopefully) most development changes are down to a dull roar, and you don’t require much change in code (say in the future). So, then you change the code to late binding, and thus avoid deployment issues.

So, second step (after removing the references in VBA->tools->references?

So, for non .net stuff, you would use CreateObject().

However, for the custom .net loader, then you use this code:

(this example is for my PDF merging library I use in VBA)

Dim MyPdf as New Pmerge.Pmerge

Above now becomes this:

Dim MyPdf As Object

Set MyPdf = CreateObjectNET("Pmerge.dll", "Pmerge.Pmerge")

So, just like for late binding, you in place of CreateObject, use CreateObjectNET, and ALSO have to give the name of the .dll you want to load.

(and of course you need my .net loader code added to your application).

So, really, if you “already” were using late binding, then only one line of code change is required.

Note that the name of the .dll in above is assumed to be in the same directory as the current Access front end.


So, above is a quick summary.

Now, to answer the question in full?

How do you create these objects (.dlls) in .net?

Well, there is the easy way, and the hard way.

I’ll start another post here on how to create such .dll’s in vb.net, and it REALLY easy!
(I'll post in a bit - later this evening - stay tuned, since everyone will be surprised HOW easy it is to create such .dll's in .net)

R
Albert

Thanks for the explain on why to use late binding and not having to register assemblies on target machines. I have been using early binding in my dotnet interface code. So to use late binding I have to change:

Public Function QBOTest()
 
    ' Our .NET interface
    Dim qbo As QBOInterface.QBOInterface
    Set qbo = New QBOInterface.QBOInterface
    ...

to

Public Function QBOTest()
 
    ' Our .NET interface
    Dim qbo As Object
    Set qbo = CreateObjectNET("QBOInterface.dll", "QBOInterface.QBOInterface")
    ...

so that it loads the DLL dynamically when creating the object?

I created DLL's and other vb.net console programs under 4.0 and older dotnet versions. Does the Just_In_Time compiler auto recompile them when I deploy to other boxes with newer versions? I need to make my Access/DotNet interop apps as portable possible, and easy for users to install. But then there's also the issues of Access versions, bitness, runtimes, trusted locations, etc. so can I incorporate the dotnet dependncies into Inno scripts, or other deployment tools, like SSE, Office Deployment Tool, SageKey Wizard?

 

[Albert D. Kallal]

Yes, they should just "work", and no need to re-compile, assuming say .net 4.x.

So, lets complete this example. We will build the above, and then copy the .dll into the same folder where our front end is.

Our Access form looks like this:

And the code behind for this simple form?
There is the button to select a file, (show the picture), and then the button to rotate the picture.

This code behind:

Option Compare Database
Option Explicit

Private Sub cmdFileSel_Click()


    Dim f       As FileDialog
    Set f = Application.FileDialog(msoFileDialogFilePicker)
    f.Filters.Clear
    f.Filters.Add "jpg picture", "*.jpg"
    
    f.Show
    
    If f.SelectedItems.Count > 0 Then
    
        Text1 = f.SelectedItems(1)
        Image1.Picture = Text1
            
    End If
    

End Sub

Private Sub cmdRotate_Click()

    Dim picObj        As Object
    Set picObj = CreateObjectNET("VBPicRotate.dll", "VBPicRotate.clsRotate")
    
    picObj.RotatePic Me.Text1.Value, 1
        
    Image1.Picture = Text1      ' this will re-load (refresh) the picture..
    
End Sub

So, kind of simple. There are "more" issues to address, but as you can see, it's not really hard to create that .net .dll.
And, without having to register it (regasm)? Then this allows adopting such .net code with a lot less work and hassle.

As noted, you will require the 2 .dll's (nloader.dll, and nloader64.dll). And you want the code module from that merge pdf example.
With above, then you can now just "drop in" the .net .dll, and you off to the races.
In fact, if you x32, or x64 (known ahead of time), then you only need one of the above .dll's (nloader = x32 bits, and nloader64 = x64 bits). However, they are small, so I usually just dump/include both .dll's.

So, in theory, while we can address the "really big" topic of using an installer?
Well, you can even deploy say using a zip file, since no registration of the .net .dll's are required......

Your above code samples look 100% correct for late binding.
Only got-ya is to watch for some cases in which a constant is used, as late binding will not include those constants.....

So, all in all?
It's not all that hard to create the .net class library. And while most examples are long and wordy code, including that of building a interface?
You really don't need all that jazz in most cases - just a simple .net class.

R
Albert

 

[BlueSpruce]

So, in theory, while we can address the "really big" topic of using an installer?
Well, you can even deploy say using a zip file, since no registration of the .net .dll's are required......

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

Your above code samples look 100% correct for late binding.
Only got-ya is to watch for some cases in which a constant is used, as late binding will not include those constants.....

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

 

[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

 

[BlueSpruce]

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.
. . .

I developed an app with Ac2010 that references the 14.0 Office Library, it will work with newer Ac versions without me having to change that ref because it auto refs the newer libraries.

I have the following code behind a command button, in an app I plan to soon deploy, for users to pick a file and externally open it with whatever viewer is associated with the selected filetype. 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?

Do I have to replace my call to msoFilePicker with

 Set fDialog = Application.FileDialog(3)

Private Sub cmdGetFile_Click()

 Dim fDialog As Object
 Dim varFile As Variant
 Dim S As String
 
   'Set up the File Dialog.
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    fDialog.InitialFileName = Environ("USERPROFILE") & "\Documents\"
  
    fDialog.AllowMultiSelect = False
 
   'Show the dialog box. If the .Show method returns True, the
    'user picked at least one file. If the .Show method returns
    'False, the user clicked Cancel.
    If fDialog.Show = True Then
        SelectedFileName = fDialog.SelectedItems(1)
    Else
       MsgBox "You clicked Cancel in the file dialog box."
  
    End If

'used to open file
    If Not IsNull(Me.SelectedFileName) Then
      Call OpenFileBringToFront(Me.SelectedFileName)
    End If
 
End Sub

Public Sub OpenFileBringToFront(FilePath As String)
    Dim ret As LongPtr
    Dim winTitle As String
    Dim waitTime As Single
 
    If Len(Dir(FilePath)) = 0 Then
        MsgBox "File not found: " & FilePath, vbExclamation
        Exit Sub
    End If
 
    ' Open the file with its default application
    ret = ShellExecute(0, "open", FilePath, vbNullString, vbNullString, 1)
 
    ' Pause ~2 seconds (replacement for Application.Wait)
    waitTime = Timer + 2
    Do While Timer < waitTime
        DoEvents
    Loop
 
    ' Try to activate the app window based on the filename
    winTitle = Dir(FilePath)   ' e.g. "Report.pdf"
    On Error Resume Next
    AppActivate winTitle
    On Error GoTo 0
 
    DoCmd.GoToControl DocumentDateOfService.Name
 
End Sub

 

[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

 

[BlueSpruce]

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

Yes, I had edited my previous post that mentions what you just said.

 

[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

 

[BlueSpruce]

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.

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.

 

[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