2

Background:

I have a VBA userform with ~1050 checkboxes, and ~100 labels, all populated from the active Sheet. The labels are taken directly from the sheet, based on the ActiveCell.Row (t and i are the rows of specific information in relation to the ActiveCell, where there are 20 different tables on the same sheet where the userform would be pulled from when double-clicked).

The event to load the userform ("Stb") is a doubleclick event which doesn't appear to cause the issue.

Issue:

Seemingly random, Excel crashes stating I am using too much memory, that I should use the x64 version if I keep having the issue. I believe it is tied to the loop in my code.

I have played with the quantity of lines within the loop and it has helped to not have the crash happen on all computers, but some users have older computers that crash more often than others.

All users have 6 GB of RAM in their system (not sure how relevant that is), using Win7 and Office 365 (Office 2016).

Question:

Is there anything beyond that loop which appear to cause the memory issue? To my limited knowledge, the storage of variables/captions has to happen in an initialization prior to the userform loading, since the userform cannot update once it has loaded. Is my understanding wrong, where I may be able to refresh the userform while/after it is loaded?

I need to keep all of the information in a similar format, but maybe I am missing out on efficiencies... anything to help in that direction would be appreciated (I know that is better suited towards Code Review, though the code does not work if the system crashes).

Code in Question:

Sorry this is a bit long.

Private Sub UserForm_Initialize()
    Dim r As Long, c As Long, i As Long, t As Long, v As Long
    r = ActiveCell.Row
    c = 3
    Select Case r
        Case 25 To 44 '1
            i = 52
            t = 24
        Case 115 To 134 '2
            i = 142
            t = 114
        Case 205 To 224 '3
            i = 232
            t = 204
        Case 295 To 314 '4
            i = 322
            t = 294
        Case 385 To 404 '5
            i = 412
            t = 384
        Case 475 To 494 '6
            i = 502
            t = 474
        Case 565 To 584 '7
            i = 592
            t = 564
        Case 655 To 674 '8
            i = 682
            t = 654
        Case 745 To 764 '9
            i = 772
            t = 744
        Case 835 To 854 '10
            i = 862
            t = 834
        Case 925 To 944 '11
            i = 952
            t = 924
        Case 1015 To 1034 '12
            i = 1042
            t = 1014
        Case 1105 To 1124 '13
            i = 1132
            t = 1104
        Case 1195 To 1214 '14
            i = 1222
            t = 1194
        Case 1285 To 1304 '15
            i = 1312
            t = 1284
        Case 1375 To 1394 '16
            i = 1402
            t = 1374
        Case 1465 To 1484 '17
            i = 1492
            t = 1464
        Case 1555 To 1574 '18
            i = 1582
            t = 1554
        Case 1645 To 1664 '19
            i = 1672
            t = 1644
        Case 1735 To 1754 '20
            i = 1762
            t = 1734
    End Select
    With Sheets("Stability")
        Stb.Cond1.Caption = .Cells(r, c + 1)
        Stb.Cond2.Caption = .Cells(r, c + 1)
        Stb.TP01.Caption = .Cells(t, c + 3)
        Stb.TP02.Caption = .Cells(t, c + 4)
        Stb.TP03.Caption = .Cells(t, c + 5)
        Stb.TP04.Caption = .Cells(t, c + 6)
        Stb.TP05.Caption = .Cells(t, c + 7)
        Stb.TP06.Caption = .Cells(t, c + 8)
        Stb.TP07.Caption = .Cells(t, c + 9)
        Stb.TP08.Caption = .Cells(t, c + 10)
        Stb.TP09.Caption = .Cells(t, c + 11)
        Stb.TP10.Caption = .Cells(t, c + 12)
        Stb.TP11.Caption = .Cells(t, c + 13)
        Stb.TP12.Caption = .Cells(t, c + 14)
        Stb.TP13.Caption = .Cells(t, c + 15)
        Stb.TP14.Caption = .Cells(t, c + 16)
        Stb.TP15.Caption = .Cells(t, c + 17)
        Stb.TP16.Caption = .Cells(t, c + 18)
        Stb.TP17.Caption = .Cells(t, c + 19)
        Stb.TP18.Caption = .Cells(t, c + 20)
        Stb.TP19.Caption = .Cells(t, c + 21)
        Stb.TP20.Caption = .Cells(t, c + 22)
        Stb.TP21.Caption = .Cells(t, c + 23)
        Stb.TP22.Caption = .Cells(t, c + 24)
        Stb.TP23.Caption = .Cells(t, c + 25)
        Stb.TP24.Caption = .Cells(t, c + 26)
        Stb.TP25.Caption = .Cells(t, c + 27)
        Stb.TP26.Caption = .Cells(t, c + 28)
        Stb.TP27.Caption = .Cells(t, c + 29)
        Stb.TP28.Caption = .Cells(t, c + 30)
        Stb.TP29.Caption = .Cells(t, c + 31)
        Stb.TP01x.Caption = .Cells(t, c + 3)
        Stb.TP02x.Caption = .Cells(t, c + 4)
        Stb.TP03x.Caption = .Cells(t, c + 5)
        Stb.TP04x.Caption = .Cells(t, c + 6)
        Stb.TP05x.Caption = .Cells(t, c + 7)
        Stb.TP06x.Caption = .Cells(t, c + 8)
        Stb.TP07x.Caption = .Cells(t, c + 9)
        Stb.TP08x.Caption = .Cells(t, c + 10)
        Stb.TP09x.Caption = .Cells(t, c + 11)
        Stb.TP10x.Caption = .Cells(t, c + 12)
        Stb.TP11x.Caption = .Cells(t, c + 13)
        Stb.TP12x.Caption = .Cells(t, c + 14)
        Stb.TP13x.Caption = .Cells(t, c + 15)
        Stb.TP14x.Caption = .Cells(t, c + 16)
        Stb.TP15x.Caption = .Cells(t, c + 17)
        Stb.TP16x.Caption = .Cells(t, c + 18)
        Stb.TP17x.Caption = .Cells(t, c + 19)
        Stb.TP18x.Caption = .Cells(t, c + 20)
        Stb.TP19x.Caption = .Cells(t, c + 21)
        Stb.TP20x.Caption = .Cells(t, c + 22)
        Stb.TP21x.Caption = .Cells(t, c + 23)
        Stb.TP22x.Caption = .Cells(t, c + 24)
        Stb.TP23x.Caption = .Cells(t, c + 25)
        Stb.TP24x.Caption = .Cells(t, c + 26)
        Stb.TP25x.Caption = .Cells(t, c + 27)
        Stb.TP26x.Caption = .Cells(t, c + 28)
        Stb.TP27x.Caption = .Cells(t, c + 29)
        Stb.TP28x.Caption = .Cells(t, c + 30)
        Stb.TP29x.Caption = .Cells(t, c + 31)
        Stb.tA.Caption = .Cells(i, c + 1)
        Stb.tB.Caption = .Cells(i + 1, c + 1)
        Stb.tC.Caption = .Cells(i + 2, c + 1)
        Stb.tD.Caption = .Cells(i + 3, c + 1)
        Stb.tE.Caption = .Cells(i + 4, c + 1)
        Stb.tF.Caption = .Cells(i + 5, c + 1)
        Stb.tG.Caption = .Cells(i + 6, c + 1)
        Stb.tH.Caption = .Cells(i + 7, c + 1)
        Stb.tI.Caption = .Cells(i + 8, c + 1)
        Stb.tJ.Caption = .Cells(i + 9, c + 1)
        Stb.tK.Caption = .Cells(i + 10, c + 1)
        Stb.tL.Caption = .Cells(i + 11, c + 1)
        Stb.tM.Caption = .Cells(i + 12, c + 1)
        Stb.tN.Caption = .Cells(i + 13, c + 1)
        Stb.teO.Caption = .Cells(i + 14, c + 1)
        Stb.tP.Caption = .Cells(i + 15, c + 1)
        Stb.tQ.Caption = .Cells(i + 16, c + 1)
        Stb.tR.Caption = .Cells(i + 17, c + 1)
        Stb.tS.Caption = .Cells(i + 18, c + 1)
        Stb.tT.Caption = .Cells(i + 19, c + 1)
        Stb.tU.Caption = .Cells(i + 20, c + 1)
        Stb.tV.Caption = .Cells(i + 21, c + 1)
        Stb.tW.Caption = .Cells(i + 22, c + 1)
        Stb.tX.Caption = .Cells(i + 23, c + 1)
        Stb.tY.Caption = .Cells(i + 24, c + 1)
        Stb.tZ.Caption = .Cells(i + 25, c + 1)
        'skip row 78
        Stb.oA.Caption = .Cells(i + 27, c + 1)
        Stb.oB.Caption = .Cells(i + 28, c + 1)
        Stb.oC.Caption = .Cells(i + 29, c + 1)
        Stb.oD.Caption = .Cells(i + 30, c + 1)
        Stb.oE.Caption = .Cells(i + 31, c + 1)
        Stb.oF.Caption = .Cells(i + 32, c + 1)
        Stb.oG.Caption = .Cells(i + 33, c + 1)
        Stb.oH.Caption = .Cells(i + 34, c + 1)
        Stb.oI.Caption = .Cells(i + 35, c + 1)
        For v = 0 To 28
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "a", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 1 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "b", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 31 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "c", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 61 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "d", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 91 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "e", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 121 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "f", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 151 + v).Value = True
        Next v
        For v = 0 To 28
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "g", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 181 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "h", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 211 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "i", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 241 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "j", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 271 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "k", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 301 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "l", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 331 + v).Value = True
        Next v
        For v = 0 To 28
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "m", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 361 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "n", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 391 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "o", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 421 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "p", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 451 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "q", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 481 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "r", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 511 + v).Value = True
        Next v
        For v = 0 To 28
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "s", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 541 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "t", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 571 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "u", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 601 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "v", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 631 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "w", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 661 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "x", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 691 + v).Value = True
        Next v
        For v = 0 To 28
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "y", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 721 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "z", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 751 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "1", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 781 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "2", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 811 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "3", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 841 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "4", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 871 + v).Value = True
        Next v
        For v = 0 To 28
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "5", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 901 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "6", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 931 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "7", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 961 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "8", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 991 + v).Value = True
            If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), "9", "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & 1021 + v).Value = True
        Next v
    End With 
End Sub
Improve this question
6
  • 2
    I once fixed a crashing Excel form with many controls by making sure I'm referring to these controls via the Controls collection and never via their code names. It was weird but it worked. Try replacing all your Stb.Cond1.Caption with Me.Controls("Stb").etc. Commented May 2, 2018 at 17:57
  • 1
    @GSerg I think that's what just saved all of my crashes. I have one more user to test on (my every time crasher), and if it works, that tip would have fixed the issue... now if we only knew an exact rationale for why that change did it. Commented May 2, 2018 at 20:36
  • 2
    a) I remember a former limitation Excel 2000/2003 of up to 411 UF controls that can be addressed directly by name, b) whereas controls beyond must be addressed as a member of the controls collection (e.g. For each myControl in Me.Controls or via item index). c) Manually deleting controls with a TabIndex property number less than 411 (e.g. labels not used in code) includes the next controls within the mentioned limitation. Note that changing this prop does not change the behavior; the behavior seems to be entirely determined by the order in which the controls were created. Commented May 3, 2018 at 18:40
  • 2
    Rationale: VBA limits direct referencing of UF controls, e.g. Label1.Caption = "...". - So you can summarize all possible solutions as follows: [1.] Use indirect referencing via Controls collection (loop, index or name) [2.] Reduce the number of controls, e.g. by distributing the controls to multiple user forms [3.] Reorganize the order of the controls, e.g. by deleting static labels and adding them again (point c in above comment). - Note: The only supporting link I knew (support.microsoft.com/en-us/kb/177842) doesn't exist any more. Commented May 6, 2018 at 19:27
  • 1
    @GSerg That was the fix to this, and TM gave rationale. If you want to post as an answer, I can accept it. Thank you! Commented May 8, 2018 at 14:20

2 Answers 2

Reset to default
3

I once fixed a crashing Excel form with many controls by making sure the code always refers all controls via the Controls collection:

Me.Controls("Cond1").Caption = .Cells(r, c + 1)

and never via their code names:

Me.Cond1.Caption = .Cells(r, c + 1)

It was weird, but it worked.
Try replacing all your Stb.Cond1.Caption with Stb.Controls("Cond1").Caption etc.

That was about 15 years ago, and up until today it remained unclear to me why it worked and why on Earth I even thought that utilising Controls in this way might have something to do with fixing it. As I learned today, it is a long standing limitation in Excel on the number of controls that can be addressed directly by their name - a poorly documented limitation, and a poorly implemented one too, as such a limitation must produce a compilation error rather than a runtime error at a random moment.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Again, highly appreciated. Between you and TM's explanation in comments, I am definitely in a better place for understanding.
I agree that the described behavior is poorly documented. I'm happy having helped by giving a rationale (even 15 years later) and by showing some possible solutions. +)
2

I can't speak to why your Excel is crashing, but you could probably cut down some code by loading some values into an Array, then instead of 6 For v = 0 to 28 loops you could have two nested loops, the inner one iterating through your array:

Dim start As Long, z As Long, arr As Variant

start = 1
arr = Array("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", "1", "2", "3", "4", "5", "6", "7", "8", "9")

For v = 0 To 28
    For z = 0 To UBound(arr)
        If Len(WorksheetFunction.Substitute(.Cells(r, c + 3 + v), arr(z), "")) < Len(.Cells(r, c + 3 + v)) Then Controls("CheckBox" & start + v).Value = True
        start = start + 30
    Next z
    start = 1
Next
Improve this answer

3 Comments

I can probably do the same for some of the other labeling pieces. Will give this a try then test on a few people's computers to see if I don't run into other issues. Thank you!
So far in testing, that hasn't caused issues! Added this after doing testing with the first tip I was given, which I think resolved my major memory issue. Will give you the +1, but if the other fellow posts his as an answer, I'll accept his. You are very much appreciated for this, it works like a charm!
@Cyril No worries! I didn't think this would do anything to solve that issue - just wanted to throw this change out there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.