Private function different results in step through and run modes

Question

My code runs without errors in both step through mode and run mode. However, it is only in step through mode that I get the correct results of the calculations in the code. It is not clear where the wrong results come from in run mode.

    Private Function get_totals(sh As Worksheet, lastrowi As Long, rowi As Integer, n As Double, o As Integer, k As Integer, totals_sheet As Worksheet, arearange As Range)

k = 2
lastrowi = Application.WorksheetFunction.CountA(arearange)

    For rowi = k To lastrowi

        totals_sheet.Cells(rowi, 12).Value = Application.Sum(Range(Cells(rowi, 2), Cells(rowi, 4)))
        n = Application.Sum(Range(Cells(rowi, 6), Cells(rowi, 11)))
        totals_sheet.Cells(rowi, 13).Value = n / o

    Next rowi

End Function

I assume the problem is that it is referencing different sheets/cells when in run mode but as I set my variables outside of the function (code below) I am unsure where the problem arises. Anyone with fresh eyes able to spot the cause of error?

  For Each sh In Sheets(Array("pipe_totals", "node_totals")) 'needs expanding once the calcs sheets are in

If sh.Name = "pipe_totals" Then
    Set sh1 = Sheets("pipe_diam_calcs")
    Set totals_sheet = Sheets("pipe_totals") 'will change for each asset group node/wps/reservoir/address
    Set arearange = totals_sheet.Columns("A:A") ' will change for node/wps/reservoir/address
    Set dmalist = sh1.Columns("c:c")
    o = 6

        ElseIf sh.Name = "node_totals" Then
            Set sh1 = Sheets("node_z_calcs")
            Set totals_sheet = Sheets("node_totals") 'will change for each asset group node/wps/reservoir/address
            Set arearange = totals_sheet.Columns("A:A") ' will change for node/wps/reservoir/address
            Set dmalist = sh1.Columns("c:c")
            o = 2

End If

Call getdma_list(dmalist, arearange)
Call loop_weight_tot(sh, totals_sheet, arearange, sh2, rowi, row, rowW, dma_string, k, col, colNum, colNum_new)

Call get_totals(sh, lastrowi, rowi, n, o, k, totals_sheet, arearange) 'need to be defined outside of function???

Next sh

Application.ScreenUpdating = True


End Sub

What do `loop_weight_tot` and `getdma_list ` do? Also, your function doesn't return anything . You can use that as a sub. — peege, Jan 16 '15 at 16:54
@peege 'getdma_ list' gets a range of values from another sheet and places them on the totals sheet in column A. 'loop_weight_tot' uses match to find values relating to the list obtained in 'getdma_list'. What is the benefit of using it as a sub rather than a function? Cheers — squar_o, Jan 16 '15 at 17:01
Subs perform operations and functions can return values. Functions can perform operations also, but what separates them is the added functionality of the return. — peege, Jan 16 '15 at 17:03
If you put break points in and run, do the calculations still come out wrong? As opposed to stepping through. And do you use the locals window? — peege, Jan 16 '15 at 17:04
@peege with break points I get the same result as with running the code - i.e. the wrong results. I didn't know about the locals window so I will check it out, looks incredibly useful :) — squar_o, Jan 16 '15 at 17:12
Yes. Here's what I'd do. Insert break points around just the calls to the functions. Then examine the Locals Window. See where the values deviate from your expectations. It's a good thing it doesn't work with the break points. That will help. — peege, Jan 16 '15 at 17:13
Start by qualifying all of your `Range()` and `Cells()` with worksheet references. e.g. `sheetRef.Range(sheetRef.Cells(...), sheetRef.Cells(...))` and not just `Range(Cells(...), Cells(...))` If you don't do this, they will default to the `ActiveSheet` and that makes results unpredictable. — Tim Williams, Jan 16 '15 at 18:20
Thanks @TimWilliams, I previously tried to qualify them but kept getting runtime errors, 'n = Application.Sum(totals_sheet.Range(Cells(rowi, 6), totals_sheet.Cells(rowi, 11)))' returns range of class of object _worksheet failed. — squar_o, Jan 19 '15 at 09:35
@TimWilliams I used a workaround, by activating `totals_sheet` and using `With Activesheet`. It returns the desired results but I suspect it is inelegant/slow! — squar_o, Jan 19 '15 at 09:57

score 1 · Answer 1 · answered Jan 19 '15 at 17:29

1

You shouldn't need to activate a sheet (and you should always try to avoid writing code which relies on a specific sheet being active)

Eg:

With totals_sheet
    .Cells(2, 15).Value = Application.Sum( _
                         .Range(.Cells(2, 12), .Cells(lastrowi, 12)))
    .Cells(2, 16).Value = Application.Average( _
                         .Range(.Cells(2, 13), .Cells(lastrowi, 13)))
End With

answered Jan 19 '15 at 17:29

Tim Williams

154,628
8
97
125

@TimWillams this makes sense but get the same runtime error. – squar_o Jan 20 '15 at 12:59
If that last block in your posted answer works, then this should too, since it's functionally the same (but without relying on the sheet being active) – Tim Williams Jan 20 '15 at 17:20

score 0 · Answer 2 · answered Jan 19 '15 at 10:06

I came up with an inelegant solution, as suspected the problem seemed to be that the references to cells/ranges were unqualified but when I attempted to qualify them with Application.Sum(totals_sheet.Range(Cells(rowi, 6), totals_sheet.Cells(rowi, 11))) I got runtime error range of class of object _worksheet failed.

My very inelegant solution was to activate the totals_sheet

Private Function get_totals(sh As Worksheet, lastrowi As Long, rowi As Integer, n As Double, o As Integer, k As Integer, totals_sheet As Worksheet, arearange As Range)

k = 2
lastrowi = Application.WorksheetFunction.CountA(arearange)

    For rowi = k To lastrowi

        totals_sheet.Activate 'to prevent active sheet returning unpredictable results

            With ActiveSheet
            totals_sheet.Cells(rowi, 12).Value = Application.Sum(Range(Cells(rowi, 2), Cells(rowi, 4)))
            n = Application.Sum(totals_sheet.Range(Cells(rowi, 6), totals_sheet.Cells(rowi, 11)))

            totals_sheet.Cells(rowi, 13).Value = n / o
            End With


    Next rowi

'get total mains/weight for the model
    totals_sheet.Activate
        With ActiveSheet
            totals_sheet.Cells(2, 15).Value = Application.Sum(Range(Cells(2, 12), Cells(lastrowi, 12)))
            totals_sheet.Cells(2, 16).Value = Application.Average(Range(Cells(2, 13), Cells(lastrowi, 13)))
        End With

End Function

I welcome any feedback/criticism on how to improve this to help me develop my VBA skills which are a bit lacking at present!

Cheers

This will work, but is not robust for the reason outlined in my comment above. You can fix it be qualifying all of your Range and Cells calls. — Tim Williams, Jan 19 '15 at 16:09
@TimWilliams yes I agree it's not robust but I can't seem to qualify the ranges within `application.sum` or `application.average` without getting runtime errors. Any idea why this is the case? — squar_o, Jan 19 '15 at 16:56

Private function different results in step through and run modes

2 Answers2