Vba Function Sheet References

nb- · May 29th 2007

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;

Code

Option Explicit 
 '// Test  Function
Public Function testfunction(targetcell As Variant, par1 As Integer, par2 As Integer) As Double 
    Dim row%, column% 
     
    row= targetcell.row
    column = targetcell.column   
     
     
    testfunction =  Cells(row, column) / (Cells(row - par1, column) + Cells(row + par2, column)) 
     
End Function

Display More

Thanks

**Andy Pope** · May 29th 2007

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]

shg · May 29th 2007

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.

nb- · May 30th 2007

Re: Vba Function Sheet References

Thanks

Dave Hawley · May 30th 2007

Re: Vba Function Sheet References

Just so you know, a Custom Function cannot select Objects such as Sheets.

Vba Function Sheet References

Participate now!

Share