Use Personal.xlsb function in new workbook?

Question

I've searched around and I know how to call a function from Personal.xlsb from a VB macro, but how can I call the function to be used in a new workbook?

Here's my function, saved in 'Module1' in my Personal.xlsb:

Public Function GetColumnLetter(colNum As Integer) As String
    Dim d As Integer
    Dim m As Integer
    Dim name As String
    d = colNum
    name = ""
    Do While (d > 0)
        m = (d - 1) Mod 26
        name = Chr(65 + m) + name
        d = Int((d - m) / 26)
    Loop
    GetColumnLetter= name
End Function

I have created a new workbook and thought I could call that just by =getcolumnletter(1), but the function doesn't "populate" when I start typing =...

Am I overlooking something?? How do I use this function in other workbooks, without VBA?

Thanks for any advice!

Answer 1

Ah, it was more simple than I thought. Just use the workbook name before the macro - so =Personal.xlsb![macroname]. So in my case, I just put this into the cell: =Personal.xlsb!GetColumnLetter(2) to return "B".

Answer 2

As you've already discovered, you can prefix the function with the filename Personal.xlsb!. But note also that there are two options available if you want to avoid prefixing your functions:

Option 1
Create a reference in every workbook that will call the function. Open the workbook where you want to use the function and go to the VBA Editor. On the menu, click Tools --> References.... In the dialog that appears, tick the box of the VBA project of the Personal.xlsb. Note that it will be listed with its project name ("VBAproject" unless you've changed the default name) rather than the filename; if other workbooks are open there might be more than one entry with the default name "VBAproject", so you might want to rename it first. More details can be found in this article, which was published after the OP: http://www.myonlinetraininghub.com/creating-a-reference-to-personal-xlsb-for-user-defined-functions-udfs

Option 2
If you want a truly general-purpose UDF, always available without either prefix or reference, you can install it as an add-in. This is done by saving the file with the UDF as an .xlam file (this would obviously be a separate file than personal.xlsb.) Additional details in a seperate article from the same source: http://www.myonlinetraininghub.com/create-an-excel-add-in-for-user-defined-functions-udfs