VBA - Visual Basic for Applications

If you have to register or unregister dynamic link libraries (dll) or OLE Custom Controls (ocx) via VBA you will find the following two methods helpful. OLE Custom Controls are actually dlls implementing a certain interface, which allows them to be utilized as OLE objects in programming languages.

Source Code

Below methods utilize an elevated command line (cmd executed as administrator) in order to register dlls using the windows program regsvr32.exe. The following syntax is used to elevate the command line.

1
objShell.ShellExecute "cmd", "/cregsvr32.exe " & Chr(34) & dllPath & Chr(34), "", "runas"

The part after the first comma can be replaced with any command you want to run in an elevated command line. This is potentially dangerous as you could do nasty stuff with an elevated command line. However, at least the user will be asked starting with Windows Vista, if he / she wants to allow the command to modify the system.

In order for the function ShellExecute to run as expected we will need to pass the following parameters.

Param Value Comment
Handle "cmd" The cmd.exe, which path should be available from a systems path environment variable.
Command
"/cregsvr32.exe " & Chr(34) & dllPath & Chr(34)
"/c" must be placed at the beginning of the command String to be executed.
File "" We do not have a file to execute an operation on.
Parameters
"runas"
This is an undocumented feature of the method ShelExecute, which tells it to run the speciffied executable as Administrator.

If you want the executable regsvr32.exe to display a message dialog every time a dll has been registered or unregistered successfully, you will have to remove the parameter "/s" from the command line String to be executed.

The call to regsvr32.exe will work fine for 32 Bit systems. For 64 Bit operating systems this will not work, unless the dll to be registered is placed in the correct directory. The directories to place a dll in are as follows.

  • 32 Bit dll on 32 OS can be registered anywhere. You will not have to copy it to "C:/Windows/System32/".
  • 32 Bit dll on 64 Bit OS must be registered in "C:/Windows/SysWOW64/".
  • 64 Bit dll on 64 Bit OS should be registrable anywhere, but I did not test this. If anything you might have to copy it to "C:/Windows/System32/".

The call to regsvr32.exe is the same on 64 Bit and 32 Bit OS although there are two versions (32 Bit and 64 Bit) available on a 64 Bit OS. Windows will automatically determine if a dll is of 32 or 64 Bit and call the corresponding version of regsvr32.exe. However this only works, if the dll to be registered is placed in the correct directory (see above list).

Funny enough, the call of method ShellExecute does not work as expected, if the command is passed in a String variable. Maybe an issue to do with type conversion as it works fine if I pass the command variable declared as Variant.

The moethod is actually supposed to return a numeric value, but no matter if the call succeeds or fails it always seems to return 0, which would mean my system is out of resources. For now I will have to live without a proper error handling on this one.

Register DLL

The following method registers a dll or ocx.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
' @Author - Alexander Bolte
' @ChangeDate - 2016-03-25
' @Description - Registers a dynamic link library (dll) available under provided path.
' The executable regsvr32.exe used to register the dll is set to silent, so no messages in case of failure or success are presented to a user.
' The method will return true, if calling the elevated command prompt succeeded.
' If registering the dll actually worked cannot be checked by this method,
' since this task is actually left to the elevated command prompt.
' @Param dllPath - a String providing the full file path to a dll or ocx.
' @Returns true, if registering the dll succeeded, else false.
Public Function registerDLL(ByVal dllPath As String) As Boolean
    Dim ret As Boolean
    Dim objShell As Object
    
    On Error GoTo errHandle:
    
    Set objShell = CreateObject("Shell.Application")
    objShell.ShellExecute "cmd", "/cregsvr32.exe /s " & Chr(34) & dllPath & Chr(34), "", "runas"
    
    ret = True
    
errHandle:
    If Err.Number <> 0 Then
        Err.Clear
        ret = False
    End If
    
    registerDLL = ret
End Function

Unregister DLL

The following method unregisters a dll or ocx.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
' @Author - Alexander Bolte
' @ChangeDate - 2016-03-25
' @Description - Unregisters a dynamic link library (dll) available under provided path.
' The executable regsvr32.exe used to unregister the dll is set to silent, so no messages in case of failure or success are presented to a user.
' The method will return true, if calling the elevated command prompt succeeded.
' If unregistering the dll actually worked cannot be checked by this method,
' since this task is actually left to the elevated command prompt.
' @Param dllPath - a String providing the full file path to a dll or ocx.
' @Returns true, if unregistering the dll succeeded, else false.
Public Function unregisterDll(ByVal dllPath As String) As Boolean
    Dim ret As Boolean
    Dim objShell As Object
    
    On Error GoTo errHandle:
    
    Set objShell = CreateObject("Shell.Application")
    objShell.ShellExecute "cmd", "/cregsvr32.exe /u /s " & Chr(34) & dllPath & Chr(34), "", "runas"
    
    ret = True
    
errHandle:
    If Err.Number <> 0 Then
        Err.Clear
        ret = False
    End If
    
    unregisterDll = ret
End Function

Unit Tests

Below two simple unit tests.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Sub testRegisterDll()
    Dim f As FileDialog
    
    Set f = Application.FileDialog(msoFileDialogFilePicker)
    With f
        .Filters.Clear
        .Filters.Add "OLE Custom Control", "*.ocx"
        .AllowMultiSelect = False
        
        If .Show Then
            Call VBATools.registerDll(.SelectedItems(1))
        Else
            MsgBox "Registration of dynamic link libraries aborted.", vbExclamation, cDialogTitle
        End If
    End With
End Sub
 
Sub testUnregisterDll()
    Dim f As FileDialog
    
    Set f = Application.FileDialog(msoFileDialogFilePicker)
    With f
        .Filters.Clear
        .Filters.Add "OLE Custom Control", "*.ocx"
        .AllowMultiSelect = False
        
        If .Show Then
            Call VBATools.unregisterDll(.SelectedItems(1))
        Else
            MsgBox "Registration of dynamic link libraries aborted.", vbExclamation, cDialogTitle
        End If
    End With
 End Sub

References

SheelExecute

https://msdn.microsoft.com/en-us/library/windows/desktop/bb762153%28v=vs.85%29.aspx

Powershell and manual registration of dll, however the options provided in the methods at below URL will only work, if you are logged in as administrator and since I am admin on my machine, I am not too sure even then it will work without an elevated command prompt. I ran into the error code 0x80040201, which basically says that I have insufficient rights to register or unregister the dll (see second URL).

My solution still prompts the user with a confirmation message but at least it works as long as the user is allowed to elevate the command prompt as admin.

http://www.myengineeringworld.net/2013/06/register-unregister-dll-file-through-vba.html

https://blogs.msdn.microsoft.com/rajakedar_ganta/2012/06/06/dllregisterserver-failed-with-the-error-code-0x80040201/

The following URL finally gave me the hint I needed in order to get things running.

http://stackoverflow.com/questions/5944180/how-do-you-run-a-command-as-an-administrator-from-the-windows-command-line

Rarely I've come across the following problem in Excel VBA. 

Some source code is definitely correct, but it just won't compile. I could one time confirm different Excel versions to be the root cause of the issue. Even though I slightly adjusted the source code in the target version of Excel, the compiler still showed funny error messages.

The trick that did solve the issue in the end was to cut the whole source code, compile and save the empty source code objects (modules, classes, etc..), paste the source code into target objects again and then compile as well as save in the target version of Excel.

From my understanding the file still held bits of the older version of Excel. However, I experienced this also using the same version of Microsoft Access on the same machine. Cutting and then pasting the source code with saving in between did the trick again. 

If you have to create and send an email automatically, the following method might help.

I have not tested it with HTML yet, but will do that at some point.

The method requires MS Outlook to be installed, since it is automating Outlook  in order to create an email with attachment.

Subcategories

This category will hold articles regarding developement in Excel VBA. It will serve as a wiki and an Excel VBA Framework for myself.

Some development tasks reoccur for every customer. Since I am a lazy bum it will be nice to have a central source where I can reuse source code from.

This category holds articles regarding general things in MS Office VBA independent from the MS Office application.  

This category holds articles regarding Access VBA, but also general things I come accross Access and its usage in companies.