Hello,

I am trying to make a Function in VBA. If I put the function in a Module in the Personal Macro Workbook I can't get it to show up on the custom function list. I wanted it to be in the personal workbook so I could use it in several spreadsheets without having to insert it in each one. I am also having problems trying to run it the way I want to when I do put it in a module in the open workbook. If I give the function a range on the same sheet as the formula such as putting "=MyFunc(B9)" in a cell it will work as expected, but if I try to use is like this, =MyFunc('Sheet2'!B9) it will just give me an error "#VALUE!"

So, is there a way to put the function in the personal macro workbook and is there a way to make it able to use cells on another worksheet in the same workbook as the input to the function without getting the error ?

I have tried setting up the function as "Public Function MyFunc(ByVal Txt as string) as String"
I even tried setting up the function as "Public Function MyFunc(ByVal RR as Range) as String"

And it will only work if the function and the input to the function is on the same worksheet, I need it to work with the formula in a cell on one worksheet but getting it's value from a cell on another worksheet in the same workbook.

This is Excel 2000 SP3 with all updates installed running on Windows XP SP3 with all updates installed. I also have VB6 SP6 installed.

Thanks for any help you can provide.

David.

Configuration: Windows XP
Internet Explorer 7.0
Excel 2000 SP3