Normal.dot and Personal.xlsb

Question

For using Word I have many public subs and functions stored in Normal.dot. I can use all of them in any module I write in any other Word project. For instance this elementary function stored in Normal.dot,

Public Function BooleanString(b As Boolean) As String
    If b Then BooleanString = "Yes!" Else BooleanString = "No!"
End Function

can be used from other projects as

Sub TestNormal()
    Debug.Print BooleanString(True)
End Sub

Using Excel I cannot duplicate this behavior: Public subs and functions I have stored in Personal.xlsb (among then the one above) seem not to be visible when called from a module in the VBAProject belonging to any other spreadsheet than Personal.xlsb, resulting in “Sub or Function not defined”.

I have made sure that the Excel option box “Have trust in the VBA project model” (translated from the Danish version) is checked. Also, using “View”, I have made sure that Personal.xlsb is not hidden; it shows up every time I open Excel.

What am I doing wrong or missing here?

Best regards Holger Nielsen

Does this answer your question? How to call function from another specific workbook in VBA? — Absinthe

FaneDuru FaneDuru · Accepted Answer · 2020-06-28T12:00:37

In Excel, using Functions/Subs of Personal.xlsb is a little different...

In order to use a UDF function, let us say, testMultiplication:

    Function testMultiplication(x As Long, y As Long) As Long
         testMultiplication = x * y
    End Function

it can be called from the (other) workbook sheet cell in this way:

    =Personal.xlsb!testMultiplication(3, 4)

In order to call Personal.xlsb Subs and Functions, a reference to the hidden workbook VBAProject is necessary...

To do that, follow the next steps, please:

Firstly give to the Personal.xlsb VBAProject an elocvent name (also to avoid name conflict error, keeping it like VBAProject):

a) In VBE - Project Explorer, right click on its VBAProject, choose VBAProject Properties... and change the standard name in, let us say, 'PersVBAProject`.

b) Then click on a module of the workbook you want to add the reference and use Tools -> References. Find 'PersVBAProject, check it and press OK`.
Starting from that moment the subs/functions of Prsonal.xlsb can be directly called from the specific workbook
In order to programmatically add the reference, use the next code, please:

    Sub AddPersonalXLSBRef()
      'It needs a reference to 'Microsoft Visual Basic for Applications Extensibility ...'
        Dim VBe As VBIDE.VBe, vbPr As VBIDE.VBProject
        Dim objRef As VBIDE.Reference, boolFound As Boolean
    
        Set VBe = Application.VBe
        Set vbPr = ActiveWorkbook.VBProject
    
        'check if reference to Personal.xlsb exists:
        'Please, take care to have its VBAProject name modified in `PersVBProject`
        'or something else and, in such a case, adapt the following lines accordingly
        For Each objRef In vbPr.References
            If objRef.Name = "PersVBProject" Then
                MsgBox "Reference to Personal.xlsb already exists"
                Exit Sub
            End If
        Next
         'add the reference, if not exist:
         vbPr.References.AddFromFile Application.StartupPath & "\Personal.xlsb"
    
         MsgBox "Reference to Personal.xlsb added Successfully"
    End Sub

It adds the reference to the active workbook project. The code can be adapted to add it to all open workbooks (not having it added, yet)...

Normal.dot and Personal.xlsb

1 Answers