can't see macro moved from workbook into personal.xlsb

[whburling]

I am using W10 and excel 365 home subscription

I created an excel vba module in a workbook, A, and tested it out.
I could test it out in debug, but had no idea how to make the spreadsheet ="see" the code module.

I then wanted to move the module into personal.xlsb as I could use this code in other spreadsheets once i learn how to make the spreadsheet see the macro.


I created the personal.xlsb (as i did not have one) by creating a null macro.

I can see the personal.xlsb in my Project VBA editor within workbook A

So...I left clicked and dragged the module from A into the Personal.xlsb
folder under the modules folder.

But if i get out of the VBA editor and go to the excel spreadsheet and
click on "macro" in the code section of the developer ribbon, I do not see the vba macro macro when I click on the drop down list box and select macros in Personal.xlsb

I must be doing something incorrectly. any ideas?

 

[mole999]

PRIVATE Sub Maybe? thats supposed to hide its presence from casual users

 

[MARK858]

Another possibility...
What is in the Macro's in box when you click Macros?

 

[whburling]

Thank you for responding.

The module I am talking about contains private subroutines and functions and public functions. The custom function i want to be able to use in my worksheet is a public function.

 

[whburling]

the macro box allows me to select which file contains the macro. It does show personal.xlsb as well as others.
HOwever, when a file is selected (such as personal.xlsb) it does not reveal any of my public functions in my code.

I am mystified.
I suspect I need to let the worksheet know i have custom functions and where they are located. i have no idea how to do that.

 

[MARK858]

If it is a function and not a sub then it won't appear in the macro list.

You reference them in a worksheet as

=Personal.xlsb!SAYHELLO()

where SAYHELLO is the name of the function.