CODEWITHSUNDEEP
excelvba
Flamingo
Flamingo

Reputation: 31

Macro added PrevSheet function returns #NAME error

I added a simple Previous Sheet macro to file and it worked normally, until it started to return #NAME error, and i can't figure out why.

I meant to use it with named single cell ranges which are consistent across the workbook, I'd used it successfully before naming the ranges and didn't think it would have any impact on the fuction. After naming the ranges though it no longer works, not even for regular non named ranges.

I have tested this by creating a new workbook, filling some sheets and trying it out, and it still returns a #NAME error. When i evaluate the function, the error appears at the very first step: recognizing the function. However, when i type into the formula bar, the programs offers me the formula normally.

I have also tried referring to the named cells by its cell, and even adding the worksheet name before the cell (eg "prevsheet(previoussheetname!a1), or prevsheet(thissheetname!a1)). I have even, in a last ditch effort, tried adding double quotes before the cell name.

For full disclosure, i have also another macro subroutine that uses references to previous and next sheets, but as it wouldnt recognize the function itself (which should have been an early sign), it makes use of relative referencing (ie activesheet(index - 1, activesheet(index + 1)). At the time i didn't think it would mess up the function, but as i grow ever more desperate and confused, maybe thats a possibility.

the PrevSheet() code i was using:

Function PrevSheet(RCell As Range)

    Dim xIndex As Long

    Application.Volatile

    xIndex = RCell.Worksheet.Index

    If xIndex > 1 Then _
        PrevSheet = Worksheets(xIndex - 1).Range(RCell.Address)

End Function

And as it is now, as suggested by Chris Neilsen

Function PrevSheet(RCell As Range) As Variant

Application.Volatile

PrevSheet = RCell.Worksheet.Previous.Range(RCell.Address).Value

End Function

As suggested by Chris Neilsen i have edited the named ranges to look like this:

!(nothing)$column$row with its scope set to Workbook

name manager

The named range is not available at the range browser. range browser

Only cell B1 is named. It is called "name" sheet1

PrevSheet() does not work with either range. sheet2

Macros are enabled

macros are enabled

Anyone with a better understanding of vba, macros and excel can tell me why this is happening and how do i fix it so it returns the value of the specified cell in the first sheet to the left of sheet the function is typed in? (ie, in sheet4, =prevsheet(A1) will return the value of cell A1 in sheet3)

I hope my question is clearer now!

Upvotes: 0

Views: 1826

Answers (3)

Ferdz R.
Ferdz R.

Reputation: 1

This happened to me too. In my case I uninstalled KuTools. and upon reponening the excel file containing =prevsheet, all is fixed!

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53126

This will work, if you define your Named Ranges correctly. There are several ways this can be done, but here's one that is IMO simplest.

Since you say ...use it with named single cell ranges which are consistent across the workbook. you can create a single Named Range, Workbook scope, that will refer to a cell (or cells) on the sheet which references the name.

Lets say you want to refer to cell A1. In Name Manager, create a Name, lets say YourNamedRange workbook scope, Reference =!$A$1 (Note the ! without a sheet reference).

When you add a formula to a sheet (eg =YourNamedRange) it will refer to cell A1 on the sheet containing the formula.

Applying it to your UDF, just use =PrevSheet(YourNamedRange)

enter image description here

Your UDF works (mostly) as is, but will fail if a different Workbook is active. To fix that, use

Function PrevSheet(RCell As Range)
    Dim xIndex As Long
    Application.Volatile
    xIndex = RCell.Worksheet.Index
    If xIndex > 1 Then
        With RCell.Worksheet.Parent 'The workbook containing RCell
            PrevSheet = .Worksheets(xIndex - 1).Range(RCell.Address)
        End With
    End If
End Function

There is also a WorksheetProperty called Previous that does much the same thing, so you can refactor as

Function PrevSheet(RCell As Range) As Variant
    Application.Volatile
    PrevSheet = RCell.Worksheet.Previous.Range(RCell.Address).Value
End Function

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96773

Your code appears to work if it is placed in a Standard Module:

Public Function PrevSheet(RCell As Range) As Variant
    Dim xIndex As Long
    Application.Volatile

    xIndex = RCell.Worksheet.Index
    MsgBox xIndex
    If xIndex > 1 Then
        PrevSheet = Worksheets(xIndex - 1).Range(RCell.Address)
    End If
End Function

enter image description here

For example, in the worksheet:

enter image description here

I have assigned cell A7 the Name junk and the 666 is the value in the previous sheet's cell A7.

Upvotes: 0

Related Questions