How to Use an External ActiveX Library in VBA within Excel

You can use an ActiveX control in VBA (Visual Basic for Applications) within Excel to interact with external components such as DLLs, COM objects, or external libraries.

Enable ActiveX Controls in Excel

Before using an ActiveX control, ensure that macros and ActiveX are enabled:

  1. Open Excel and go to File → Options.
  2. Select Trust Center → Trust Center Settings.
  3. Navigate to ActiveX Settings and select "Prompt me before enabling all controls with minimal restrictions".

Reference an External ActiveX Library in VBA

To use an external ActiveX component (e.g., Chilkat, MSXML, or a third-party DLL):

Step 1: Add a Reference to the ActiveX Component

  1. Open Excel and press "ALT + F11" to open the VBA Editor.
  2. Go to Tools → References.
  3. Scroll down to find the ActiveX library (e.g., "Chilkat ActiveX", "Microsoft XML 6.0", etc.).
  4. Check the box next to it and click OK.
    Excel VBA Reference

Step 2: Create an Object and Use It in VBA

After adding a reference, you can create and use an ActiveX object in VBA.

Example: Using an ActiveX Component (Early Binding)
Sub UseActiveXObject()
Dim http As New ChilkatHttp ' Example using Chilkat ActiveX
Dim html As String
' Make an HTTP GET request
html = http.QuickGetStr("https://www.chilkatsoft.com/helloWorld.html")
' Display the result
If (Http.LastMethodSuccess = 0) Then
    MsgBox Http.LastErrorText
    Exit Sub
End If
MsgBox html
End Sub
  • Early Binding: Requires adding the reference manually ("Tools → References").
  • Benefit: IntelliSense and better performance.

Step 3: Use Late Binding (No Reference Needed)

If you don't want to add a reference manually, you can use late binding with "CreateObject".

Example: Using Late Binding (No Reference)
Sub UseActiveX_LateBinding()
Dim http As Object
Dim html As String
' Create the ActiveX object
Set http = CreateObject("Chilkat.Http")
' Make an HTTP GET request
html = http.QuickGetStr("https://www.chilkatsoft.com/helloWorld.html")
' Display the result
If (Http.LastMethodSuccess = 0) Then
    MsgBox Http.LastErrorText
    Exit Sub
End If
MsgBox html
' Release object
Set http = Nothing
End Sub
  • Late Binding: Uses "CreateObject", so no manual reference is needed.
  • Benefit: More flexible but no IntelliSense/autocomplete.

Handling ActiveX Errors in VBA

If you encounter runtime errors, try the following:

  1. Ensure the ActiveX is Registered
  2. Check 32-bit vs. 64-bit Compatibility
    • If you're using 64-bit Excel, ensure your ActiveX control is 64-bit.
    • If you're using 32-bit Excel, ensure your ActiveX control is 32-bit.
    • Chilkat provides both 32-bit and 64-bit ActiveX DLLs
  3. Enable ActiveX in Excel Trust Center
    • File → Options → Trust Center → Trust Center Settings → ActiveX Settings
    • Select Enable all controls.

Conclusion

✔ You can use ActiveX in VBA by adding references ("Tools → References") or using "CreateObject".

✔ Early binding provides IntelliSense, while late binding is more flexible.

✔ Ensure ActiveX controls are registered and compatible with 32-bit or 64-bit Excel.