VBA UDF #Name?

[•Daniel•]

Hi,

I'm learning VBA and creating some User defined Functions, the first time i wrote one I could go to the spreadsheet type de name of my recently created function and see how it went, now I reopen excel the UDF still stored in module1 at personal.XLSB but I cannot type just the name of my UDF's I have to go trough the functions menu and the name of the function has the PERSONAL.xlsb before the name?.

How can I solve this an let those UDF's easy available for any workbook?

Thanks

 

[Ruddles]

Did you declare it as Public?

 

[•Daniel•]

I Wasn't, just did, still the same


Public Function myname() As String
myname = ThisWorkbook.Name
End Function

the function is stored at personal.xlsb, it worked one time I don't know what happend but the function messed up also, if I search it trough insert function and run it myname = PERSONAL.xlsb while it should be the name of the workbook I'm standing in

 

[•Daniel•]

Surely changing thisworkbook for activeworkbook solve the last problem but still I have trough insert function menu. or type =PERSONAL.XLSB!myname()

 

[shg]

Change the name of the VBA project in Personal to something recognizable (mine is projPersonal).

Then in the workbook where the formulas appear, do Tools > References, and set a reference.

If you always want a reference in new workbooks, add to the template Book.xltx (or create one) in your XLSTART folder.

 

[•Daniel•]

sorry i'm quite a noob, so I change the personal.xlsb for something familiar and then what do you mean in the workbook........., do I search for my xlsb file in reference?

 

[Hamburgler]

I think that user defined functions must always contain the workbook name prefix unless:

1) The function code is contained in a module of the the workbook that you are calling it from

or

2) The function code is contained in an Excel AddIn file (XLA*) file.

I keep all of my macros in personal.xlsb and all UDFs in MyFunctions.XLAM.


[Edit]

or 3) What shg said...

 

[shg]

so I change the personal.xlsb for something familiar and then what do you mean in the workbook........., do I search for my xlsb file in reference?

In the Project Explorer window, click on the VBA project in Personal.

Tools > VBAProject properties, change the name to projPersonal, or anything else memorable. Click the Save button.

In the Project Explorer window, click on the VBA project in the workbook of interest.

Tools > References, tick the box for projPersonal.

Now you don't need to prefix function names with Personal.xlsb!

 

[•Daniel•]

Got it! thx, now I'm trying to do the template thing in the xl star folder