SOLVED

Saving my function to PERSONAL workbook.

Occasional Contributor

I made a function and it saved to the workbook that I had open then.  I wanted it saved to my PERSONAL workbook, so it would be available in all the workbooks I use.  Even when closed that workbook and had only PERSONAL open, VBA has the function showing and it stays saved in the original workbook.  I can't seem to tell it I want to create that function in any other workbook.

 

I think I'm having trouble understanding how the VBA worksheet relates to the Excel worksheets.  I can select different projects from the pane on the left, but that doesn't seem to change what's available in the main pane.  It's getting to be a pain, that pane.

7 Replies

Jaqi-

 

You need to access the Personal Workbook to be able to store macros in it.  Sometimes it can be a little elusive.

 

Try doing the following steps to complete your task:

 

1. Open a new Excel File

2. Click Record Macro (On the Developer tab)

3. Select Store in Personal Macro Workbook

4. Click OK

5. Click Stop Recording

6. Click Visual Basic (Far Left on Developer tab)

7. Once in the Visual Basic Editor (VBE), Click on the project labeled VBAProject(Personal.XLSB)

8. Click on Module 1

9. Where the current macro code is delete it all

10. Paste your function code

11. Click the save icon in the VBE

12.  Your function is now available in all workbooks

 

 

 

PERSONAL is already showing.  I even had focus on PERSONAL when I created the function.  It still saved in the other workbook.  I've tried closing that workbook so PERSONAL is the only open workbook and saving it then, but it's not moving it to PERSONAL.  There doesn't seem to be a "save as" option for the VBA editor.

Can you take a couple of screenshots so I can help diagnose your issue:

 

1. Function Code

2. VBE Code Module you are storing the function (With Project Explorer and Header Visible)

3. Developer > Macros > (Screenshot of Pop Up)

 

 

 

 

I'm having trouble figuring out the VBA project navigation.  I now can't find where the function was stored.  I can use it, but not find where I defined it.

 

By the way, this is a custom function, not a macro.  I can't seem to figure out how custom functions are filed in the VBA navigation pane.  I can find my macros through the developer tab, but I can't find any way to access the custom function.  I don't even see where I would try to enter another custom function.

best response confirmed by Jaqi Hegland (Occasional Contributor)
Solution

Jaqi-

 

You need to store the Function in your Personal Workbook.  Should look something like this:

 

Personal Workbook Function.png

OK, I found my function and saved it to PERSONAL, and it seems to work.  It's a shame I have to include the PERSONAL.XLSB! before the function name, it could be a lot easier to use, but I'm not going to save it in every workbook I want to use it in.  Especially since there are so many empty modules already from my poke-and-guess efforts to figure out how this works.  There doesn't seem to be a way to delete modules after they are created, is there?  This file system is an ugly mess.

Jaqi-

 

Glad you were able to get your issue resolved.  If you remove the VBA Function from the file you're working in it will no longer be necessary to reference the Personal Workbook.  I believe that since you have this function in the file you're working with and you're personal workbook Excel needs you to further qualify the reference to identify which one you are trying to use.

 

You just need the function in the personal workbook.  In order to find where the code is your current workbook you can use Ctrl + F and search for the function in the Visual Basic Editor.  Once you have found the function in the workbook just delete it.  (just be careful not to accidentally remove it from your personal workbook).

 

Please let me know if you need further assistance.  Always happy to help.