excelvbauser-defined-functions

How to integrate User Defined Function with excel formula

The following User Defined Function is referencing previous excel sheet.

Function PrevSheet()
    Application.Volatile
    PrevSheet = Worksheets(ActiveSheet.Index - 1).Name
End Function

The following formula is okey.

=Sheet1!A1

The following formula is NOT okey. The following formula needs to repaired.

=PrevSheet()!A1
Solution

  • Expansion on my comment to do this all in a UDF:

    Function PrevSheetVal(PrevSheetRow As Long, PrevSheetCol As Long) As String
    Application.Volatile
    PrevSheetVal = Worksheets(ActiveSheet.Index - 1).Cells(PrevSheetRow, PrevSheetCol).Value
End Function

    Used like:

    =PrevSheetVal(1,1)

    Which will return the value in cell A1 of the "Previous sheet" as defined by the sheet with an index 1 less than the Activesheet