none
VBA-Excel: Function defined by user gives error / Personal.xlsb weird RRS feed

  • Question

  • hi everyone,

    I think I have a common problem. I read a lot of forums but I haven't the solution yet.

    I defined a VBA function for excel. A very simple one, just to test it.

    The problem is that when I try to use it, excel gives me the #NAME error. It only works if in the formula wizard I explicitly writes " PERSONAL.XLSB.name of function". I tried the following:

    1) enabling the use of macros at the hhighest level;

    2) i put the code in the personal.xlsb section of vba

    3) i defined it as a public function

    4) i tried to use the command application.volatile and the command Application.Calculation = xlCalculationAutomatic

    One thing that is weird - on top of the fact that I can't properly use this custom function defined by me unless i explicitily write personal.xlsb.name of the function when keying the formula - is the name of my personal file of excel. I have never seen a file named personal.xlsb

    I have to add that I often use other personal.xls (coming from my previous company experience) files. Given my company restrictions, I am not allowed to copy and replace the current personal.xls with my previous one. So, when I need one old macro I just click and open the personal.xls coming from my previous company. Could this be the problem? if so, how can I work around it?

    Hope I have been clear.

    Thanks in advance!

    Thursday, March 21, 2013 10:18 PM

Answers

  • The code has to be copied into a module in the Visual Basic Editor, not into a worksheet! See the very detailed instructions I provided...

    Regards, Hans Vogelaar

    • Marked as answer by rangillo Monday, March 25, 2013 4:15 AM
    Friday, March 22, 2013 8:47 PM

All replies

  • Personal.xlsb is your personal macro workbook for Excel 2007 and later versions.

    Macros and user-defined functions stored in Personal.xlsb will be available in all your workbooks. But, as you have found, you must prefix the name of user-defined functions in cell formulas with Personal.xlsb!, for example =Personal.xlsb!MyFunction(...)

    This was the same in earlier versions of Excel, where the personal macro workbook was named Personal.xls. You had to use =Personal.xls!MyFunction(...) in formulas.

    There is no way to omit the Personal.xlsb!

    If you want to be able to use a user-defined function in all workbooks without prefixing it with Personal.xlsb! you have to store it in an Excel add-in (.xlam) and install that add-in.


    Regards, Hans Vogelaar

    • Proposed as answer by Grasor Friday, January 8, 2016 12:09 AM
    Thursday, March 21, 2013 10:28 PM
  • Hi Hans,

    Thank you so much for answering.

    Right now, I am looking for the info you are telling me. 

    Thanks again,

    Matteo

    Friday, March 22, 2013 1:58 AM
  • Personal.xlsb is your personal macro workbook for Excel 2007 and later versions.

    Macros and user-defined functions stored in Personal.xlsb will be available in all your workbooks. But, as you have found, you must prefix the name of user-defined functions in cell formulas with Personal.xlsb!, for example =Personal.xlsb!MyFunction(...)

    This was the same in earlier versions of Excel, where the personal macro workbook was named Personal.xls. You had to use =Personal.xls!MyFunction(...) in formulas.

    There is no way to omit the Personal.xlsb!

    If you want to be able to use a user-defined function in all workbooks without prefixing it with Personal.xlsb! you have to store it in an Excel add-in (.xlam) and install that add-in.


    Regards, Hans Vogelaar

    Hi Hans,

    Would you be so kind to explain how to save the VBA function code into a .xlam.?

    I found a lot of webpages in which they explain the process, but I don't get the part when I have to Save As an Add-in the excel part..??

    I am referring to the help page of office.

    My question is: I got the code in vba store in a personal.xlsb module. How can I transfer that and save it as an Add-in?

    Sorry, but I am really not getting it!

    Thanks in advance!

    Matteo

    Friday, March 22, 2013 4:53 PM
  • Create a blank new workbook (press Ctrl+N in Excel). It will be named Book1, Book2 or similar.

    This will become the add-in later on, but not yet.

    Activate the Visual Basic Editor (press Alt+F11).

    If you want to copy an entire module with all macros and functions from Personal.xlsb to the new workbook:

    • If necessary, expand Personal (PERSONAL.XLSB) in the project explorer pane on the left hand side, then expand Modules under it.
    • Drag the module that you want to copy to the new workbook and drop it there. This copies the module.

    If you want to copy one or more individual macros/functions from Personal.xlsb to the new workbook:

    • Click on the new workbook in the project explorer pane on the left hand side.
    • Select Insert > Module.
    • If necessary, expand Personal (PERSONAL.XLSB) in the project explorer pane on the left hand side, then expand Modules under it.
    • Open the relevant module.
    • Select a macro or function, then copy it (Ctrl+C).
    • Switch to the module in the new workbook (Ctrl+F6).
    • Paste the code (Ctrl+V).
    • Repeat as necessary.

    When you're done, switch back to Excel.

    Make sure that the new workbook is the active workbook. We're going to save it as an add-in now.

    • If you use Excel 2007, click the Office button; in Excel 2010 or 2013, click File.
    • Click Save As.
    • In the 'Save as type' dropdown, select 'Excel add-in (*.xlam)'.
    • Excel will probably switch to the AddIns folder automatically, if not, you can navigate to it yourself. Under Windows XP, it is C:\Documents and Settings\<username>\Application Data\Microsoft\AddIns. Under Windows Vista and later it is C:\Users\<username>\AppData\Roaming\Microsoft\AddIns.
    • Save your new add-in there.
    • Quit and restart Excel.

    Finally, we'll install the add-in.

    • In Excel 2007, click Office button > Excel Options. In Excel 2010 and later, select File > Options.
    • Click Add-Ins.
    • Make sure that 'Excel add-ins' is selected in the Manage dropdown, then click Go...
    • If all is well, your add-in should be listed in the dialog that appears. Tick its check box.
    • If it isn't listed, you can click Browse..., then locate and select the add-in.
    • Click OK.

    You should now be able to use custom functions defined in the add-in without adding a prefix.


    Regards, Hans Vogelaar

    Friday, March 22, 2013 5:20 PM
  • I did exactly what you wrote. It does not work....

    I copied the text of the vba function sub into the excel sheet and saved it as xlam.

    And then I followed exactly what you suggested me to do.

    it does not work...

    Does it have to be copied in a special way into the excel spreadsheet? like starting from A1?

    Friday, March 22, 2013 6:25 PM
  • The code has to be copied into a module in the Visual Basic Editor, not into a worksheet! See the very detailed instructions I provided...

    Regards, Hans Vogelaar

    • Marked as answer by rangillo Monday, March 25, 2013 4:15 AM
    Friday, March 22, 2013 8:47 PM
  • ahhhhhhh! Now I got it!

    It works!! Thanks a lot and sorry for my misunderstanding!

    Cheers!

    Monday, March 25, 2013 2:57 AM
  • Hi Hans,

    Thanks - I now understand how it works and made my file work as it should.

    However wouldn't this be an improvement item for the Excel development team? It is illogical that macros can be run from PERSONAL.XLSB, and functions can't!

    Keep up the good work!

    Tinus

    Sunday, January 3, 2016 2:26 PM
  • If you wish, you can propose this at http://excel.uservoice.com

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, January 3, 2016 3:24 PM