Why does this vba code works different depending on whether I'm debugging or running it?

Question

I'm trying to code a macro to copy specific values from a worksheet to another. When I run the code it works just fine, but if I run it, it doesn't.

The idea is to copy all the lines from one workbook to a new one but only some of the cells from two lines at a time. I made a macro with some trivial validations, and then call a function to make the copying. (The function can be called with "espaciosEnBlanco" being 0 or having a positive value, and the columnaValorTotal can be two different columns)

The parameters are:

originWorksheet: original workbook

newWorksheet: destiny workbook

valueColumn: the column from where I need one of the values

iFirstRow: index (starts in 0)

blankRows: index to leave blank rows (When you're calling the function and you don't need to leave empty rows, you send 0 in this fild)

iWithoutTitle: another index (starts in 2 and increments by 1 each iteration)

nextRowsNeedBlankSpaces: boolean that indicates the you need to leave blank rows

I tried to replicate them in many ways; copying them, matching the values, changing Cells to Range, and matching the values with Range. I also try importing Sleep function and putting it between all lines, and the same with wait, just in case it wasn't having enough time to copy the values, but it didn't work either.

What I can't understand is that when I debug it line by line it works just fine.

Public Function CopiarRegistros(originWorksheet, newWorksheet, valueColumn, iFirstRow, blankRows, iWithoutTitle, nextRowsNeedBlankSpaces)

If (Cells((iWithoutTitle + iFirstRow), valueColumn).Value < 0) Then

     originWorksheet.Cells((iWithoutTitle + iFirstRow), "T").Copy Destination:=newWorksheet.Cells((iWithoutTitle - 1 + iFirstRow + blankRows), "B")
     originWorksheet.Cells((iWithoutTitle + 1 + iFirstRow), "T").Copy Destination:=newWorksheet.Cells((iWithoutTitle + 1 - 1 + iFirstRow + blankRows), "B")
     originWorksheet.Cells((iWithoutTitle + 1 + iFirstRow), valueColumn).Copy Destination:=newWorksheet.Cells((iWithoutTitle - 1 + iFirstRow + blankRows), "K")

     newWorksheet.Range("I" & (iWithoutTitle - 1 + iFirstRow + blankRows)).Value = "D"
     newWorksheet.Range("I" & (iWithoutTitle + 1 - 1 + iFirstRow + blankRows)).Value = "C"

Else
     originWorksheet.Cells((iWithoutTitle + iFirstRow), "T").Copy Destination:=newWorksheet.Cells((iWithoutTitle - 1 + iFirstRow + blankRows), "B")
     originWorksheet.Cells((iWithoutTitle + 1 + iFirstRow), "T").Copy Destination:=newWorksheet.Cells((iWithoutTitle + 1 - 1 + iFirstRow + blankRows), "B")
     originWorksheet.Cells((iWithoutTitle + 1 + iFirstRow), valueColumn).Copy Destination:=newWorksheet.Cells((iWithoutTitle - 1 + iFirstRow + blankRows), "K")

     newWorksheet.Range("I" & (iWithoutTitle - 1 + iFirstRow + blankRows)).Value = "C"
     newWorksheet.Range("I" & (iWithoutTitle + 1 - 1 + iFirstRow + blankRows)).Value = "D"

End If

    iFirstRow = iFirstRow + 1

    If (nextRowsNeedBlankSpaces) Then
        blankRows = blankRows + 2
    End If

End Function

Not that this is your issue and is still 'legal code', why are you using a function over a sub? — K.Dᴀᴠɪs, Jul 15 '19 at 20:35
@K.Dᴀᴠɪs I would even say [why are you using a non-returning `Function` over a `Sub`?](https://stackoverflow.com/a/52766237/1188513) ;-) — Mathieu Guindon, Jul 15 '19 at 21:03
@MathieuGuindon I like the detailed answer you provided there. Would be a great addition to my collection of favorites :-) — K.Dᴀᴠɪs, Jul 15 '19 at 21:25

score 4 · Answer 1 · answered Jul 15 '19 at 20:27

4

In a standard code module:

Cells((iWithoutTitle + iFirstRow), valueColumn).Value

Will refer to whatever happens to be the active sheet - typically that leads to wrong results or errors. You need to be more specific when referring to ranges - always specify which sheet you mean to be involved.

answered Jul 15 '19 at 20:27

Tim Williams

154,628
8
97
125

@Tim Williams @Mathieu Guindon I tried doing it like this, but it doesn't seem to work either. Could you give me an example of how should I do it? `With hojaOrigen .Cells((iSinTitulo + indicePrimeraFila), "T").Copy End With With hojaDestino .Cells((iSinTitulo - 1 + indicePrimeraFila + espaciosEnBlanco), "B").PasteSpecial End With` – FranciscoG Jul 18 '19 at 16:24
@FranciscoG I'll take a stab in the dark and guess that the error is 1004 and either `iSinTitulo + indicePrimeraFila` or `iSinTitulo - 1 + indicePrimeraFila + espaciosEnBlanco` evaluates to `0` or less. Excel rows start at `1`. – Mathieu Guindon Jul 18 '19 at 16:29
@MathieuGuindon I'm sorry, I've changed the variables names, but "iSinTítulo" is "iWithoutTitle" (Which starts in 2). So it would be imposible that it turn out to be less than 1. Also, the macro works just fine when debugging it, that's why I think the problem is not because of the indexes but the copying. – FranciscoG Jul 19 '19 at 20:28

score 0 · Answer 2 · answered Jun 04 '21 at 12:23

It also could be something trivial. I have had problems with copying data while debugging. I had code that in debug mode run smoothly but when I tried to run it I got different result. It was driving me crazy and I spent several hours looking for cause. It turned out it was collapsed grouping of columns that was changing behavior of code in debug and in run. To prevent it from happening use ThisWorkbook.Sheets("Your sheet name").Outline.ShowLevels ColumnLevels:=8 , before data manipulations in sheet.

Why does this vba code works different depending on whether I'm debugging or running it?

2 Answers2