Increment VBA int variable in-line - like i++ ?

shell_l_d · #1 (**permalink**) 04-12-11, 22:22

Is there a way to increment an Excel VBA integer variable like you can with C++, eg: iCol++ please - in-line?

Want to avoid hard-coded numbers to increment iCol:

Code:

    Dim iRow As Integer, iCol As Integer
    iRow = 1
    iCol = 1

        ' HEADINGS
        .Cells(iRow, iCol + 1).Value = "Total"
        .Cells(iRow, iCol + 2).Value = "RespTm PASS"
        .Cells(iRow, iCol + 3).Value = "RespTm %"

This works, but double the number of lines, is there a way to do it in-line (combine 2 lines into 1)?

Code:

        iCol = iCol + 1
        .Cells(iRow, iCol).Value = "Total"
        iCol = iCol + 1
        .Cells(iRow, iCol).Value = "RespTm PASS"
        iCol = iCol + 1
        .Cells(iRow, iCol).Value = "RespTm %"

This in-line attempt fails, causes error '1004 Application-defined or object-defined error':

Code:

        .Cells(iRow, (iCol = iCol + 1)).Value = "Total"
        .Cells(iRow, (iCol = iCol + 1)).Value = "RespTm PASS"
        .Cells(iRow, (iCol = iCol + 1)).Value = "RespTm %"

I also need to fill the table with data for each of the headings, like doing this (again for >20 columns & more complex/longer formulas)

Code:

        .Cells(iRow + 1, iCol + 1).FormulaR1C1 = "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """)"
        .Cells(iRow + 1, iCol + 2).FormulaR1C1 = "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[Response Time],""PASS"")"
        .Cells(iRow + 1, iCol + 3).FormulaR1C1 = "=IF(tblOverall[[#This Row],[Total]]>0,tblOverall[[#This Row],[RespTm PASS]]/tblOverall[[#This Row],[Total]],0)"

Teddy · #2 (**permalink**) 04-13-11, 00:33

Unfortunately you don't get an increment operator. I would strongly recommend going with the first option you listed. It is very clear what you intended to do. Anything more than that seems unnecessary.

shell_l_d · #3 (**permalink**) 04-13-11, 00:37

I didn't want to have to manually edit column numbers when inserting new columns... so was after a less hard-coded way

This works... (via Increment VBA int variable in-line - like i++ ? - MrExcel Message Board)

Code:

    Dim iRow As Integer, iCol As Integer, iTotalCols As Integer
        
    iRow = 1
    iCol = 1
    iTotalCols = 21
    '...etc...
        ' HEADINGS
        ' All & SLA & Non-SLA
        .Cells(iRow, iCol + 1).Resize(, iTotalCols).Value = _
            Array( _
                    "Total", _
                    "RespTm PASS", _
                    "RespTm %", _
                    "ResolTm PASS", _
                    "ResolTm %", _
                    "OVERALL PASS", _
                    "OVERALL %", _
                    "SLA Total", _
                    "SLA RespTm PASS", _
                    "SLA RespTm %", _
                    "SLA ResolTm PASS", _
                    "SLA ResolTm %", _
                    "SLA OVERALL PASS", _
                    "SLA OVERALL %", _
                    "Non-SLA Total", _
                    "Non-SLA RespTm PASS", _
                    "Non-SLA RespTm %", _
                    "Non-SLA ResolTm PASS", _
                    "Non-SLA ResolTm %", _
                    "Non-SLA OVERALL PASS", _
                    "Non-SLA OVERALL %" _
                  )
        Call CreateTable
        
        ' VALUES - Exclude FFS
        ' All & SLA & Non-SLA
        .Cells(iRow + 1, iCol + 1).Resize(, iTotalCols).FormulaR1C1 = _
            Array( _
                    "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """)", _
                    "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[Response Time],""PASS"")", _
                    "=IF(tblOverall[[#This Row],[Total]]>0,tblOverall[[#This Row],[RespTm PASS]]/tblOverall[[#This Row],[Total]],0)", _
      ...etc... 17 more forumulas
                    "=IF(tblOverall[[#This Row],[Non-SLA Total]]>0,tblOverall[[#This Row],[Non-SLA OVERALL PASS]]/tblOverall[[#This Row],[Non-SLA Total]],0)" _
                  )__________________

shell_l_d · #4 (**permalink**) 04-13-11, 02:41

Bugger... I added line continuations in the code to make it more readable... however I need to add another 6 columns (headings & formula's) however I can only add 1 extra (25 line continuations) if I keep it in the format above, as I get the error: Too many line continuations.

Might have to split it into 3 lots (all, sla, non-sla).

Teddy · #5 (**permalink**) 04-13-11, 10:06

I'm telling you from experience... you'll end up putting more effort into a clever work around than you will for the 1-2 minutes of refactoring code.

You won't save any time and you'll end up with something that's harder to read. I know it's tempting, don't do it man!

That code you posted... I have no idea what it does. It will take some time to go through it and figure out what's going on. That's a bad sign, we call it "code smell". Always code like the guy who has to support it after you has an unstable violent streak and an uncanny ability to find someone's home address.

shell_l_d · #6 (**permalink**) 04-13-11, 19:31

This is what I ended up doing...

Code:

...
          Dim iRow As Integer, iCol As Integer, iTotalCols As Integer
              
2         iRow = 1
3         iCol = 1
          
          ' Array of Headings
          ' All
4         Call AddToArray(sHeadings, "Total")
5         Call AddToArray(sHeadings, "RespTm PASS")
6         Call AddToArray(sHeadings, "RespTm %")
7         Call AddToArray(sHeadings, "ResolTm PASS")
8         Call AddToArray(sHeadings, "ResolTm %")
9         Call AddToArray(sHeadings, "OVERALL PASS")
10        Call AddToArray(sHeadings, "OVERALL %")
11        Call AddToArray(sHeadings, "ReWorks")
12        Call AddToArray(sHeadings, "CSR PASS")
          ' SLA
13        Call AddToArray(sHeadings, "SLA Total")
14        Call AddToArray(sHeadings, "SLA RespTm PASS")
15        Call AddToArray(sHeadings, "SLA RespTm %")
16        Call AddToArray(sHeadings, "SLA ResolTm PASS")
17        Call AddToArray(sHeadings, "SLA ResolTm %")
18        Call AddToArray(sHeadings, "SLA OVERALL PASS")
19        Call AddToArray(sHeadings, "SLA OVERALL %")
20        Call AddToArray(sHeadings, "SLA ReWorks")
21        Call AddToArray(sHeadings, "SLA CSR PASS")
          ' Non-SLA
22        Call AddToArray(sHeadings, "Non-SLA Total")
23        Call AddToArray(sHeadings, "Non-SLA RespTm PASS")
24        Call AddToArray(sHeadings, "Non-SLA RespTm %")
25        Call AddToArray(sHeadings, "Non-SLA ResolTm PASS")
26        Call AddToArray(sHeadings, "Non-SLA ResolTm %")
27        Call AddToArray(sHeadings, "Non-SLA OVERALL PASS")
28        Call AddToArray(sHeadings, "Non-SLA OVERALL %")
29        Call AddToArray(sHeadings, "Non-SLA ReWorks")
30        Call AddToArray(sHeadings, "Non-SLA CSR PASS")
          
          ' Array of Formulas
          ' All
31        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """)")
32        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[Response Time],""PASS"")")
33        Call AddToArray(sFormulas, "=IF(tblOverall[[#This Row],[Total]]>0,tblOverall[[#This Row],[RespTm PASS]]/tblOverall[[#This Row],[Total]],0)")
34        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[Resolution Time],""PASS"")")
35        Call AddToArray(sFormulas, "=IF(tblOverall[[#This Row],[Total]]>0,tblOverall[[#This Row],[ResolTm PASS]]/tblOverall[[#This Row],[Total]],0)")
36        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[OVERALL],""PASS"")")
37        Call AddToArray(sFormulas, "=IF(tblOverall[[#This Row],[Total]]>0,tblOverall[[#This Row],[OVERALL PASS]]/tblOverall[[#This Row],[Total]],0)")
38        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>6"",tblGlobalData[ReWork],""Yes"")")
39        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>6"",tblGlobalData[Customer Satisfaction Rating],""Passed"")")
          ' SLA
40        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],""<>"")")
41        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],""<>"",tblGlobalData[Response Time],""PASS"")")
42        Call AddToArray(sFormulas, "=IF(tblOverall[[#This Row],[SLA Total]]>0,tblOverall[[#This Row],[SLA RespTm PASS]]/tblOverall[[#This Row],[SLA Total]],0)")
43        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],""<>"",tblGlobalData[Resolution Time],""PASS"")")
44        Call AddToArray(sFormulas, "=IF(tblOverall[[#This Row],[SLA Total]]>0,tblOverall[[#This Row],[SLA ResolTm PASS]]/tblOverall[[#This Row],[SLA Total]],0)")
45        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],""<>"",tblGlobalData[OVERALL],""PASS"")")
46        Call AddToArray(sFormulas, "=IF(tblOverall[[#This Row],[SLA Total]]>0,tblOverall[[#This Row],[SLA OVERALL PASS]]/tblOverall[[#This Row],[SLA Total]],0)")
47        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],""<>"",tblGlobalData[ReWork],""Yes"")")
48        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],""<>"",tblGlobalData[Customer Satisfaction Rating],""Passed"")")
          ' Non-SLA
49        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],"""")")
50        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],"""",tblGlobalData[Response Time],""PASS"")")
51        Call AddToArray(sFormulas, "=IF(tblOverall[[#This Row],[Non-SLA Total]]>0,tblOverall[[#This Row],[Non-SLA RespTm PASS]]/tblOverall[[#This Row],[Non-SLA Total]],0)")
52        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],"""",tblGlobalData[Resolution Time],""PASS"")")
53        Call AddToArray(sFormulas, "=IF(tblOverall[[#This Row],[Non-SLA Total]]>0,tblOverall[[#This Row],[Non-SLA ResolTm PASS]]/tblOverall[[#This Row],[Non-SLA Total]],0)")
54        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],"""",tblGlobalData[OVERALL],""PASS"")")
55        Call AddToArray(sFormulas, "=IF(tblOverall[[#This Row],[Non-SLA Total]]>0,tblOverall[[#This Row],[Non-SLA OVERALL PASS]]/tblOverall[[#This Row],[Non-SLA Total]],0)")
56        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],"""",tblGlobalData[ReWork],""Yes"")")
57        Call AddToArray(sFormulas, "=COUNTIFS(tblGlobalData[RegOff],tblOverall[[#This Row],[RegOff]],tblGlobalData[Severity],""<>" + scSeverityFFS + """,tblGlobalData[SLA],"""",tblGlobalData[Customer Satisfaction Rating],""Passed"")")

58        With ThisWorkbook.Worksheets("Overall")
          
59            .Select
60            Range("A1").Select
              
              ' Fill column A with Header & Unique Values from tblGlobalData tables RegOff column.
61            Sheets("GlobalData").Range("tblGlobalData[[#Headers],[#Data],[RegOff]]").AdvancedFilter _
                             Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True
              
              ' HEADINGS
62            iTotalCols = UBound(sHeadings) + 1
63            .Cells(iRow, iCol + 1).Resize(, iTotalCols).Value = sHeadings
64            Call CreateTable
              
              ' VALUES - Exclude FFS
65            On Error Resume Next
66            iTotalCols = UBound(sFormulas) + 1
67            .Cells(iRow + 1, iCol + 1).Resize(, iTotalCols).FormulaR1C1 = sFormulas

68            On Error GoTo Error_In_ExtraOverall
...

shell_l_d · #7 (**permalink**) 04-13-11, 19:41

Quote:

Originally Posted by Teddy

That code you posted... I have no idea what it does. It will take some time to go through it and figure out what's going on. That's a bad sign, we call it "code smell".

Nice!!!

I have prepared a document on what to change and how, to pass on to whoever else may need to edit it.

There are many worksheets populated by SQL queries, then I've created a table for each of the worksheets (all done with VBA & a userform created on the fly for late binding to work with multiple versions of windows & office). Those formula's are doing a calculation on a table in another worksheet (tblGlobalData table in GlobalData worksheet).

I pride myself on documenting/commenting/coding, however I'm not an expert at VBA... I can only try my best.

Teddy · #8 (**permalink**) 04-13-11, 22:33

Why not use the method that doesn't require complex documentation? Really... all of this to save what, 45 seconds of rifling through every line and replacing "iCol + 1" with "iCol + n"? The tradeoff is the other guy is going to have to spend far more time reading your documentation and figuring out why the sam hell you didn't just say "iCol + 1" instead of using some bizarre fringe case application of array logic than it would have for him to just write it himself.

There is good lazy and bad lazy when it comes to writing code. This, my friend, is bad lazy. You ALREADY spent way more time and effort trying not to expend time and effort and you still have documentation to do. The next guy will have to pay many times over the effort required to understand what you did because you went way off into left field just to avoid typing some numbers. You both lose. No bueno.

I'm telling you, I am no stranger to being in your shoes. There are two things that catch out almost all developers at every skill level:
1. Bad assumptions. Assumptions are the root of nearly all evil. Make bad assumption about a business requirement, proper functionality of a peice of code, path that a user will take through the application, etc, and you may as well up your stock in handbaskets.

2. Trying to be clever. If it seems like you're over-complicating things for very little benefit, you probably are. If you find yourself beaming over figuring out a totally badass, complex, novel (aka: obtuse and deviating from best practice) way to accomplish a trivial task, you're doing it wrong. Stop it.

I'm over a decade in and I still struggle with those two, as do most of the developers I come across, including the sr. folks. You gotta reign it in man!

shell_l_d · #9 (**permalink**) 04-14-11, 00:51

Teddy - be nice please... you are making alot of assumptions too. I have not spent hours on this, I was documenting other stuff, not just related to this.
Gees man, if it can take me what 30mins to make a change that will make it simpler for the non-vba programmers here to edit, then I'm all for it.
I've been a programmer for a decade too (not in VBA) & I'm still learning too.

Teddy · #10 (**permalink**) 04-14-11, 10:47

I am being nice, and I'm not making any assumptions (because they are evil, as stated). Hence the smiley!

I would take an entirely different approach if the aim is to allow people unfamiliar with VBA to modify the solution. That said, I would rather task them with incrementing integers than developing a deep understanding of programmatically created array formulas. Have you looked into a using a configuration sheet or something similar? Asking "normal" users to crack the VBE has traditionally ended in tears for me...

I would be even more critical if you did this with maintainability as a goal than if you did it with saving time in mind. It just doesn't make sense.

PS: I'm really, REALLY not trying to tear in to you. I'm saying this is a common problem among all developers across all languages with all experience levels. It's a problem because we tend to lose sight of whether what we're doing is actually accomplishing our goals. Myself absolutely, whole-heartedly included.

shell_l_d · #11 (**permalink**) 04-14-11, 21:06

Thanks Teddy for adding a smiley to these comments of yours, but doesnt stop them sounding harsh, then you say in your latest post 'I would be even more critical if...':

1) That code you posted... I have no idea what it does. It will take some time to go through it and figure out what's going on. That's a bad sign, we call it "code smell".
2) There is good lazy and bad lazy when it comes ...to writing code. This, my friend, is bad lazy. You ALREADY spent way more time and effort..... You both lose. No bueno.
3) If you find yourself beaming over figuring out a totally badass, complex, novel (aka: obtuse and deviating from best practice) way to accomplish a trivial task, you're doing it wrong. Stop it

One of your 'assumptions' was 'you've spend hours on this already'.

I took your feedback on board, but I'm not going back to edit it now, I only have 3 more days to give handover documentation before my resignation ends. If you don't agree with this decision that's fine, but I believe the above comments were certainly uncalled for.

Sure I'll take constructive criticism anytime, provided it's actually 'constructive' (unlike the ones above).

If it's alright with you, I think I might just move on to a different forum, where I wont be attacked.

Teddy · #12 (**permalink**) 04-21-11, 20:35

I didn't say I wasn't being critical, I said I was being nice. There is a big, big difference.

Since the sugar coating wasn't thick enough for you, here's the same sentiment without wasting the time and effort:

Your attitude and lack of foresight call for a good deal more than just "harsh" criticism. I was hoping that jokingly ribbing you for your decisions and urging you to think long term would be taken as constructive criticism. It's disappointing that you couldn't handle even that. The inability of a developer to recognize being told to make architecture decisions based on the client's needs instead of their own as constructive criticism instead of a personal attack would be a major red flag for any member of a team I manage. If I could not correct this attribute, I would manage them out as quickly as possible. This trait is dangerous and poses a massive risk to both my projects, and potentially my clients directly.

Maybe I just failed to convey the right message, it could be all on me.

Good luck on your next venture, hopefully it's not with a seasoned team of developers that will eat you alive for simply not giving two #$%'s about ease of maintainability and development best practices, or how ignoring them affects other people. I'd put a smiley here, but I only include them when I actually intend the message to be taken as tongue in cheek. That's not my goal this time around.

Internet Services

Web Development

Digital Marketing

Consumer Tech