Calling a Relative Sheet Referencing udf - #NAME? Error

[masterkemist]

Hello.

I have been pulling my hair out trying to get this to work. I only have two hairs left and they're fighting with eachother. I want to call a udf to reference the sheet before my active sheet. Their are twelve sheets (one for each month). Here is what I want to accomplish and the code I am using in VB.

I want to do this with relative sheet referencing:

=IF(Jan!G9=Feb!L9,IF(Jan!$H9="","",Jan!$H9),"")

as well as this:

=IF($K9>0,Jan!G9,"")

So, I searched, learned, and searched some more. I tried this:

=IF($K9>0,PrevSheetName()&"!G9","")

...and the code...


code:
--------------------------------------------------------------------------------
'this will return the name of the previous sheet'
Function PrevSheetName() As String
Application.Volitile True
With Application.Caller.Parent
If .Index = 1 Then
PrevSheetName = .Parent.Worksheets(.Parent.Worksheets.Count).Name
Else
PrevSheetName = .Previous.Name
End If
End With
End Function
--------------------------------------------------------------------------------





I cannot seem to get aroung the #NAME? error that keeps occuring. VERY FRUSTRATING. Any help would be greatly appreciated. I am running Windows XP with Excel 2002.

 

[macleanb]

Two points:

1: Where have you placed the code for this UDF? I think it will need to be in a module - not on a worksheet code tab.

2: When you get it working I think you will need to use indirect(PrevSheetName()&"!G9") as prevsheetname returns a string not a range.

p.s. I didnt check the actual code of your function for errors.

OK I did now - you cant spell volatile!

 

[masterkemist]

Thanks for the help... I did find the spelling error on my own as I was looking over it. I am a relative newby, so I hope this doesn't sound too lame, but how do I open up a module instead of a worksheet code tab?
Thanks again

 

[macleanb]

You need to insert one from the Insert menu -> Module.

 

[Zack Barresse]

ADVERTISEMENT

Hi there,


If you wanted to add some functionality to it, you could adapt it to the following ...



<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Function</SPAN> PrevSheetName(<SPAN style="color:#00007F">Optional</SPAN> celRef <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">With</SPAN> Application.Caller.Parent
        <SPAN style="color:#00007F">If</SPAN> celRef <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">If</SPAN> .Index = 1 <SPAN style="color:#00007F">Then</SPAN>
                PrevSheetName = .Parent.Worksheets(.Parent.Worksheets.Count).Name
            <SPAN style="color:#00007F">Else</SPAN>
                PrevSheetName = .Previous.Name
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Else</SPAN>
            <SPAN style="color:#00007F">If</SPAN> celRef.Parent.Index = 1 <SPAN style="color:#00007F">Then</SPAN>
                PrevSheetName = .Parent.Worksheets(.Parent.Worksheets.Count).Name
            <SPAN style="color:#00007F">Else</SPAN>
                PrevSheetName = celRef.Parent.Previous.Name
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
</FONT>



This will allow you to put a cell reference inside of the function when you call it. That cell reference must be a range and can be from anywhere in the workbook. Note that the sheet reference must be complete. Some examples:

=PrevSheetName()
Will use the current sheet, as you have now.

=PrevSheetName(A1)
Will also use the current sheet as the Indexed.

=PrevSheetName(Sheet2!A1)
Will use Sheet2 as the Indexed sheet.

Effectively you can use this anywhere and use it specifically for target areas now. I believe this will help you if you'd like to do a Summary sheet of sorts (is the current application I foresee).


As for navigating the Visual Basic Editor (VBE) a good link can be found here: http://www.vbaexpress.com/training.htm . The first lesson is on that, it's free. Also, our featured articled, written by dcardno, "Why use Personal.xls?" An excellent into.


HTH

 

[masterkemist]

Darn it... I am still getting that #name? error in my cell. When I opend the formula editor, the prevsheetname() function is returning volatile. I know we are close and am very appreciative. Hopefully the next guy who has this problem will be saved.

 

[masterkemist]

Thanks to all that helped out. I simply had to use your tips as well as make sure the security for macros was set to medium. This forum is a God send. And I'm out!