Answered by:
How to ensure that the function is getting its data from the correct Excel worksheet (VBA question)
Question

I have two sheets in my workbook.
Each worksheet is similar...they have about 14 columns of data and then in the 15 column I have a forumula. The forumla is a vba function.
Let's say the sheets are called RegionA and RegionB.
In the Sheet RegionA my function in Column 15 is =RegionACosts('RegionA'!B1:O1)
In the sheet RegionB my function in Column 15 is =RegionBCosts('RegionB'!B1:O1)
The following function is (located in Modules) is called RegionBCosts (RegionACosts is similar with different selects parameters)
Function RegionACosts(thCells As Range) As Double Dim firstRow, firstColumn, numRows, numCols As Integer Dim totalClintonCosts As Double Dim TestValue As Double firstRow = thCells.Rows.Row firstColumn = thCells.Columns.Column numRows = thCells.Rows.Count numCols = thCells.Columns.Count totalPaidBreaks = 0 For r = firstRow To firstRow + numRows  1 For c = firstColumn To firstColumn + numCols  1 TestValue = Cells(r, c).Value 'MsgBox ("Clinton Row " + Str(r) + ", " + Str(testValue)) If Cells(r, c).Value > 0 Then 'MsgBox ("In Cost Calculation  Costs started at " + Str(totalPaidBreaks)) Select Case Cells(r, c).Value Case Is <= 3 totalClintonCosts = totalClintonCosts + 0# Case Is <= 5 totalClintonCosts = totalClintonCosts + 0# Case Is <= 6.5 totalClintonCosts = totalClintonCosts + 0.25 Case Is <= 8.75 totalClintonCosts = totalClintonCosts + 0.5 Case Is > 8.75 totalClintonCosts = totalClintonCosts + 0.75 End Select 'MsgBox ("Now it is " + Str(totalClintonCosts)) End If Next c Next r RegionACosts = totalClintonCosts End Function
This works fine when you are in one of the sheets and you change a row.
However, when the sheet first opens or if you insert a new row....then the whole workbook (both sheets) reclaculates
Then what happens is that the active sheet calculates correct but the other sheet calculates using the data in the rows of the active sheet
I thought that my adding 'RegionA'!B1:O1 would direct the sheet to use the info from the correct sheet
Can someone please help
John
Answers

Hello again John.Feeney,
I thought that you also might also be interested in the following information. When a range is assigned to a variable it is actually similar to a mini worksheet on its own. Both the row and column numbers of the range can be addressed from 1 to the total number of rows/columns in the range. If there is a need to iterate through the rows and columns (rather than the For Each Loop as per my previous post) then it can be done like the following. Remember that because the range contains the embedded information re the worksheet and workbook, it is addressing the range on the correct worksheet from which it is called.
Function RegionACosts_2(thCells As Range) As Double
Dim totalClintonCosts As Double
Dim r As Long
Dim c As Long
For r = 1 To thCells.Rows.Count
For c = 1 To thCells.Columns.Count
If thCells.Cells(r, c) > 0 Then
Select Case thCells.Cells(r, c).Value
Case Is <= 3
totalClintonCosts = totalClintonCosts + 0#
Case Is <= 5
totalClintonCosts = totalClintonCosts + 0#
Case Is <= 6.5
totalClintonCosts = totalClintonCosts + 0.25
Case Is <= 8.75
totalClintonCosts = totalClintonCosts + 0.5
Case Is > 8.75
totalClintonCosts = totalClintonCosts + 0.75
End Select
End If
Next c
Next r
RegionACosts_2 = totalClintonCosts
End FunctionGenerally programmers would use the With statement instead of repeating the range name throughout the code. Example as follows. Note the leading Dot in front of Rows.Count, Columns.Count and Cells(r, c) which ties these commands back to the With thCells.
Function RegionACosts_3(thCells As Range) As Double
Dim totalClintonCosts As Double
Dim r As Long
Dim c As Long
With thCells
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count
If .Cells(r, c) > 0 Then
Select Case .Cells(r, c).Value
Case Is <= 3
totalClintonCosts = totalClintonCosts + 0#
Case Is <= 5
totalClintonCosts = totalClintonCosts + 0#
Case Is <= 6.5
totalClintonCosts = totalClintonCosts + 0.25
Case Is <= 8.75
totalClintonCosts = totalClintonCosts + 0.5
Case Is > 8.75
totalClintonCosts = totalClintonCosts + 0.75
End Select
End If
Next c
Next r
End With
RegionACosts_3 = totalClintonCosts
End FunctionRegards, OssieMac
 Marked as answer by John.Feeney Wednesday, September 19, 2012 6:09 PM
All replies

You probably do not set worbook in range function.
If you using range() or cells() that is a way to work with activeworkbooks / activesheet.name
Take look on this one:
Sub RangeTest() Dim wks As Worksheet Set wks = ActiveWorkbook.Sheets(1) '< or replace 1 to "Sheet1" or other Dim r As Range Set r = wks.Range("a1") Call RegionACosts(r) End Sub Function RegionACosts(thCells As Range) As Double Debug.Print "Workbook = " & thCells.Parent.Parent.Name & "  " & _ "Worksheet = " & thCells.Parent.Name & "  " & _ "Address = " & thCells.Address End Function
Turn on Immediate window [Ctrl+G]
Oskar Shon, Office System MVP
Press if Helpful; Answer when a problem solved

If you change the method for the loop and use For Each loop then it should work for all sheets because a range variable contains the calling parent information as has been demonstrated in the answer by Oskar Shon.
Function RegionACosts(thCells As Range) As Double
Dim rngCel As Range
Dim totalClintonCosts As Double
For Each rngCel In thCells
If rngCel.Value > 0 Then
Select Case rngCel.Value
Case Is <= 3
totalClintonCosts = totalClintonCosts + 0#
Case Is <= 5
totalClintonCosts = totalClintonCosts + 0#
Case Is <= 6.5
totalClintonCosts = totalClintonCosts + 0.25
Case Is <= 8.75
totalClintonCosts = totalClintonCosts + 0.5
Case Is > 8.75
totalClintonCosts = totalClintonCosts + 0.75
End Select
End If
Next rngCel
RegionACosts = totalClintonCosts
End Function
Regards, OssieMac
 Edited by OssieMac Wednesday, September 19, 2012 1:37 AM

Hello again John.Feeney,
I thought that you also might also be interested in the following information. When a range is assigned to a variable it is actually similar to a mini worksheet on its own. Both the row and column numbers of the range can be addressed from 1 to the total number of rows/columns in the range. If there is a need to iterate through the rows and columns (rather than the For Each Loop as per my previous post) then it can be done like the following. Remember that because the range contains the embedded information re the worksheet and workbook, it is addressing the range on the correct worksheet from which it is called.
Function RegionACosts_2(thCells As Range) As Double
Dim totalClintonCosts As Double
Dim r As Long
Dim c As Long
For r = 1 To thCells.Rows.Count
For c = 1 To thCells.Columns.Count
If thCells.Cells(r, c) > 0 Then
Select Case thCells.Cells(r, c).Value
Case Is <= 3
totalClintonCosts = totalClintonCosts + 0#
Case Is <= 5
totalClintonCosts = totalClintonCosts + 0#
Case Is <= 6.5
totalClintonCosts = totalClintonCosts + 0.25
Case Is <= 8.75
totalClintonCosts = totalClintonCosts + 0.5
Case Is > 8.75
totalClintonCosts = totalClintonCosts + 0.75
End Select
End If
Next c
Next r
RegionACosts_2 = totalClintonCosts
End FunctionGenerally programmers would use the With statement instead of repeating the range name throughout the code. Example as follows. Note the leading Dot in front of Rows.Count, Columns.Count and Cells(r, c) which ties these commands back to the With thCells.
Function RegionACosts_3(thCells As Range) As Double
Dim totalClintonCosts As Double
Dim r As Long
Dim c As Long
With thCells
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count
If .Cells(r, c) > 0 Then
Select Case .Cells(r, c).Value
Case Is <= 3
totalClintonCosts = totalClintonCosts + 0#
Case Is <= 5
totalClintonCosts = totalClintonCosts + 0#
Case Is <= 6.5
totalClintonCosts = totalClintonCosts + 0.25
Case Is <= 8.75
totalClintonCosts = totalClintonCosts + 0.5
Case Is > 8.75
totalClintonCosts = totalClintonCosts + 0.75
End Select
End If
Next c
Next r
End With
RegionACosts_3 = totalClintonCosts
End FunctionRegards, OssieMac
 Marked as answer by John.Feeney Wednesday, September 19, 2012 6:09 PM