Can't use 2 sheets with a custom function. Closed

Question

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.

Jono · Answer

I share your pain... I have been trying to do the same thing, but with a command button calling a common function.

Ill tell you what I did to get it to work in case it helps you. Basically, I had 6 or 7 very similar sheets in the same workbook, that all have the same buttons on them. Each of the buttons are doing the same function. I had a copy of the function for each button on each sheet - yuk!

This was a pain to maintain. I wanted to make only once copy of each function. So, I used the following wrapper functions in each sheet's VBA holder to call the global function that was in a VBA "module":

Private Sub Paper_Click()
    Run "PaperCopy_Items"
End Sub

... then you have in the module:

Function PaperCopy_Items()
...
End Function

That worked for me... But, I can tell you it was painful. If you are anything like me you are a programmer and expected it to be simple right? Like someone should have thought of this before and it would be something that many people want to do? Gee how wrong was I...

Cheers,
Jon

Can't use 2 sheets with a custom function.

1 response

Similar discussions

Subscribe To Our Newsletter!