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!

Monroy

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".

Monroy

Yeah, it's an unfortunate thing about excel, you won't get the populate unless you are in the book of the function or you create an add-in – Animosity

That's weird, because in my mind, the Personal.xlsb would be where you would create/store macros that you would use "Excel wide". Is that not perhaps Microsoft's purpose for Personal.xlsb? (And especially in Personal.xlsb if you have a Public Function, I would naturally think it's available across all workbooks without having to specially call it with =Personal.xlsb![whatever]. – Monroy

It's a good place to store subroutines and functions, but not public functions. – Animosity

Hm, then I need to read up on what Public means then for functions - I thought it just meant it's available A) to see in the macro list (as opposed to Private ...., and B) available across Excel. – Monroy

Well I meant functions that you can call from a sheet, rather than functions that return something within a macro. Poor wording on my part. – Animosity

Could someone please add a link with more instructions or usage example for the =Personal.xlsb![whatever] syntax, I can't make it work. This page shows a similar thing, but here the syntax is Application.Run "Personal.xlsb![whatever]" and is about Subs rather than Functions (is that an important distinction in this case?) – Toothpaste

@Toothpaste - Are you trying to call the macro via a formula within an Excel sheet (like you would with =SUM(1,2), or =VLOOKUP()? Or are you calling your personal macro with VBA? – Monroy

I am calling with/from within VBA code belonging to another workbook – Toothpaste

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

Anarchic

This is great! I have always known about the second option, but didn't realize I could create a reference to my Personal project too. Thanks a lot for your answer! – Monroy

Amazing stuff here. Option1 is very convenient as @Monroy stated. I have always just copied and pasted my personal functions to my current project as needed. – Prop

Beware that these option are only valid if 1) you're the only user 2) you always use the same computer. – Elsaelsbeth

Answer 3

Option 3 (extending @Egalth's answer above):

I think this may be the best solution to avoid prefacing the function with the filename Personal.xlsb! in external on-sheet calls

In VBA Properties Window, set the Personal.xlsb ThisWorkbook's "IsAddin" property to True. Now you don't have to type the Personal.xlsb! prefix when you use it's functions on external sheets. This behavior isn't documented.

https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.isaddin

Magically, the prefix will disappear in existing on-sheet calls to it's worksheet functions. Existing on-sheet calls don't break when you toggle IsAddin between True and False.

Advantages

• You don't have to save the file as add-in, so you won't have the confusion of the add-in and the original Personal.xlsb both open at the same time.
• You don't have to set a reference to it.

Cautions

• You won't be prompted to save the workbook if changes are made while the workbook is open. So be sure to manually save changes.
• On re-opening Excel, IsAddin will be reset to False, and Personal.xlsb's worksheet will be visible. Do the following to fix this issue:

1. add the following code to ThisWorkbook module:

Private Sub Workbook_Open()

ThisWorkbook.IsAddin = True

End Sub

2. With IsAddin set to FALSE, Go to Excel front-end, ensure you're looking at Personal.xlsb sheet (and not some other workbook), and click View ribbon > Hide. When you close Excel, accept prompt to "Save changes to Personal.xlsb?" Now it will look and act like a normal Personal.xlsb.

3. To save future edits to Personal.xlsb:

• If staying in the editing session, then ensure Personal.xlsb is selected in the VBA Project Explorer, and click Save.
• If closing Excel, perform Step 2 above.

Termagant

This was the unvalued answer. I posted a similar question, and this was incredibly helpful and honestly a very clean solution to having the function effectively seamless in my workbooks. – Cad

Answer 4

Simply typing PERSONAL.XSLB! before the function name will return an error.

The only way it will work is if you:

• click the fx icon
• select your function from the User Defined category list.

Using Win10 and Excel 365

Underprop

Answer 5

Note with Option 3 - Personal.xlsb when loaded with IsAddIn = True is no longer visible directly as visible or hidden workbook. It is accessible through the VBA editor. Obvious, since Personal.xlsb is generally used for macros & other VBA. But it can catch you off guard at first if you stash reference odds & ends in Personal.xlsb worksheets.

Septet

Always an idea to look how long ago the question was posted. :) – Kenyettakenyon

@JMarineau what version of Excel are you using? – Ingravescent

@DarrenBartrup-Cook - No worries, I'm always willing to find new ways to accomplish this...even checks original post .... nine years later! – Monroy

Answer 6

1. From Visual Basic Application Editor: Click on Tools, then References.

2. Add "Personal" from list of available references. Note: if Personal does not appear, ensure that the workbook is not protected or open the Visual Basic project for "Personal" and try again.

3. Add this code to your function or procedure: a = Personal.MyFunction(MyParm)

Your sub or function should branch to the sub or function indicated.

Uralian

How does this differ from the answer? Just that I can do Personal.MyFunction instead of Personal.xlsb!MyFunction? – Monroy

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Steal