Search code examples
excelvbaclasslate-bindingearly-binding

Excel VBA: How to use early binding with class module in Personal workbook

I have a class module in my project that I want to make available to other projects. I moved the class module to my Personal workbook. Following this Microsoft document, I added this function to the Personal workbook to be able to to create an instance of the class in other projects.

Option Explicit

Public Function new_video() As cVideo
    Set new_video = New cVideo
End Function

I can now create an instance in my project. However, to do so, I have to use late binding.

Dim Video As Object
Set Video = Application.Run("'personal.xlsb'!new_video")

This means I don't get the list of methods & properties once I type "Video."

According to the Microsoft doc linked above, it says

Remember that to use the early binding code, the client project must include a reference (Tools | References…) to the Class Provider.xls file.

I have set a reference to PersonalMacros (the project name of the Personal workbook), but I don't see the workbook name itself (Personal.xlsb) in the list.

Is there something else I need to do or do I have to settle for late binding?

Solution

  • First, in the Project Explorer window (Ctrl+R), make sure that you select the workbook in which you've added the reference.

    Then select Tools >> References, and you should see PersonalMacros under the available references.

    Then you can declare and create an instance of your class object using early binding as follows...

    Dim video As PersonalMacros.cVideo
Set video = PersonalMacros.new_video

    Also, make sure that the Instancing property in your class module is set to 2 - PublicNotCreatable.