Geek Answers Handbook

Use the Personal.xlsb function in a new book?

I searched, and I know how to call a function from Personal.xlsb from a VB macro, but how can I call a function to be used in a new book?

Here is my function stored 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 created a new book and thought that I could call it simply =getcolumnletter(1) , but the function does not "fill" when I type =...

Am I missing something? How to use this function in other books without VBA?

Thanks for any advice!

+5
source share
2 answers

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

+9
source

As you have already discovered, you can prefix the function with the file name Personal.xlsb! . But note also that there are two options if you want to avoid the prefix of your functions:

Option 1
Create a link in each book that calls the function. Open the book in which you want to use this feature and go to the VBA editor. From the menu, select Tools --> References... In the dialog box that appears, check the VBA Personal.xlsb project field. Note that it will be listed with its project name ("VBAproject" if you have not changed the default name), not the file name; if other books are open, there may be more than one entry with the default name "VBAproject", so you can rename it first. More information 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 universal UDF that is always available without a prefix or link, you can install it as an add-in . This is done by saving the UDF file as a .xlam file (it will obviously be a separate file than Personal.xlsb .) Additional information in a separate article from the same source: http://www.myonlinetraininghub.com/create-an -excel-add-in-for-user-defined-functions-udfs

+7
source

More articles:


All Articles