none
How to ensure that the function is getting its data from the correct Excel worksheet (VBA question) RRS feed

  • 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

    Tuesday, September 18, 2012 10:01 PM

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 Function

    Generally 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 Function


    Regards, OssieMac

    • Marked as answer by John.Feeney Wednesday, September 19, 2012 6:09 PM
    Wednesday, September 19, 2012 2:47 AM

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

    Tuesday, September 18, 2012 11:33 PM
    Answerer
  • 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
    Wednesday, September 19, 2012 1:12 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 Function

    Generally 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 Function


    Regards, OssieMac

    • Marked as answer by John.Feeney Wednesday, September 19, 2012 6:09 PM
    Wednesday, September 19, 2012 2:47 AM