Saving a VBA function in personal workbook

[TM74]

Hi,

Is it possible to save a VBA function in your personal workbook, giving you access to it on all open spreadsheets?

I have tried it with one and it doesn't seem to work. Is there any way of doing this?

 

[MrKowz]

The easiest way to create a personal.xls file is to Record a macro and store it to the "Personal Macro Workbook". You can choose this from the dropbox in the record new macro window.

 

[TM74]

Thanks, I do that with many sub procedures which I have stored in the personal workbook. I added a module and put a function procedure in there.

I was wondering if there is a way to get that to work in all sheets (the same way a sub procedure would). Otherwise I think I wuld nbeed to add the function procedure to whichever sheets I wanted to use it in.

 

[schielrn]

When you say VBA function, do you mean a macro or a user-defined function? What is the code you are trying to save there? Also depending on if you have multiple instances of Excel open you might be trying to save it to a read-only version of your personal.xls file? Also you need to make sure that the code is genereic enough that it is not referencing incorrect sheets or ranges.

Hope that helps.

 

[MrKowz]

Doh... I didn't read your post closely enough... you want a FUNCTION to be available. Yes, this can be done. Create a new workbook, make a module to store all of your functions in, then save the workbook as an addin (.xla) and store it in the AddIns folder for Excel. Be sure to restart Excel after you do this and make sure the addin you created is turned on under Tools>Add-Ins

 

[TM74]

Scliern - yes it is a user defined function:

Function iColour(r As Range) As Integer
iColour = r.Interior.ColorIndex
End Function

I'll give MrKowz's solution a bash.

Thanks

 

[TM74]

That worked perfectly, thanks.