Vba Function Sheet References

  • How do I select different sheets from within a function?

    I have a function that takes the parameter of a cell, and then looks up and down from that cell, and returns a value based on those values. This works fine until I select a cell on a different sheet from the cell the function is written in.

    In outline it is as below;


  • Re: Vba Function Sheet References

    You need to reference the .Cells of the sheet that the target is on rather than the active sheet.

    [vba] '// Test Function
    Public Function testfunction(targetcell As Variant, par1 As Integer, par2 As Integer) As Double
    Dim row%, column%
    Dim shtTemp As Worksheet

    Set shtTemp = targetcell.Parent

    row = targetcell.row
    column = targetcell.column

    testfunction = shtTemp.Cells(row, column) / (shtTemp.Cells(row - par1, column) + shtTemp.Cells(row + par2, column))

    End Function


  • Re: Vba Function Sheet References

    I don't think this is good programming practice, but here you go:

    Public Function testfunction(targetcell As Range, par1 As Integer, par2 As Integer) As Double
        testfunction = targetcell / (targetcell.Offset(-par1) + targetcell.Offset(par2))
    End Function

    Excel can't tell that the offset cells affect the calculation, so making the function volatile causes it to recalculate whenever anything changes.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!