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;



    Thanks

  • 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
    [/vba]

    [h4]Cheers
    Andy
    [/h4]

  • Re: Vba Function Sheet References


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


    Code
    Public Function testfunction(targetcell As Range, par1 As Integer, par2 As Integer) As Double
        Application.Volatile
        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!