Excel VBA Procedure is too large UserForm

Question

I use below code to display calculation results in separate window to follow changes in live time. This is only short part of the code it continues down below for many rows down. I have long code and would like to make my UserForm to display summary reports in tabbed view (MultiPage). I can only create one tab with following code. When I try to add second "Tab" I get "Procedure is too large".

My code:

Controls("Label841").Caption = ThisWorkbook.Sheets("Price calculation").Range("A109").Value
Controls("Label842").Caption = ThisWorkbook.Sheets("Price calculation").Range("A110").Value
Controls("Label843").Caption = ThisWorkbook.Sheets("Price calculation").Range("A111").Value
Controls("Label844").Caption = ThisWorkbook.Sheets("Price calculation").Range("A112").Value
Controls("Label845").Caption = ThisWorkbook.Sheets("Price calculation").Range("A113").Value
Controls("Label846").Caption = ThisWorkbook.Sheets("Price calculation").Range("A114").Value
Controls("Label847").Caption = ThisWorkbook.Sheets("Price calculation").Range("A115").Value
Controls("Label848").Caption = ThisWorkbook.Sheets("Price calculation").Range("A116").Value
Controls("Label849").Caption = ThisWorkbook.Sheets("Price calculation").Range("A117").Value
Controls("Label850").Caption = ThisWorkbook.Sheets("Price calculation").Range("A118").Value
Controls("Label851").Caption = ThisWorkbook.Sheets("Price calculation").Range("A119").Value
Controls("Label852").Caption = ThisWorkbook.Sheets("Price calculation").Range("A120").Value
Controls("Label853").Caption = ThisWorkbook.Sheets("Price calculation").Range("A121").Value
Controls("Label854").Caption = ThisWorkbook.Sheets("Price calculation").Range("A122").Value
Controls("Label855").Caption = ThisWorkbook.Sheets("Price calculation").Range("A123").Value
Controls("Label856").Caption = ThisWorkbook.Sheets("Price calculation").Range("A124").Value

Controls("Label875").Caption = ThisWorkbook.Sheets("Price calculation").Range("D109").Value
Controls("Label876").Caption = ThisWorkbook.Sheets("Price calculation").Range("D110").Value
Controls("Label877").Caption = ThisWorkbook.Sheets("Price calculation").Range("D111").Value
Controls("Label878").Caption = ThisWorkbook.Sheets("Price calculation").Range("D112").Value
Controls("Label879").Caption = ThisWorkbook.Sheets("Price calculation").Range("D113").Value
Controls("Label880").Caption = ThisWorkbook.Sheets("Price calculation").Range("D114").Value
Controls("Label881").Caption = ThisWorkbook.Sheets("Price calculation").Range("D115").Value
Controls("Label882").Caption = ThisWorkbook.Sheets("Price calculation").Range("D116").Value
Controls("Label883").Caption = ThisWorkbook.Sheets("Price calculation").Range("D117").Value
Controls("Label884").Caption = ThisWorkbook.Sheets("Price calculation").Range("D118").Value
Controls("Label885").Caption = ThisWorkbook.Sheets("Price calculation").Range("D119").Value
Controls("Label886").Caption = ThisWorkbook.Sheets("Price calculation").Range("D120").Value
Controls("Label887").Caption = ThisWorkbook.Sheets("Price calculation").Range("D121").Value
Controls("Label888").Caption = ThisWorkbook.Sheets("Price calculation").Range("D122").Value
Controls("Label889").Caption = ThisWorkbook.Sheets("Price calculation").Range("D123").Value
Controls("Label890").Caption = ThisWorkbook.Sheets("Price calculation").Range("D124").Value
Controls("Label891").Caption = ThisWorkbook.Sheets("Price calculation").Range("D125").Value

Controls("Label911").Caption = ThisWorkbook.Sheets("Price calculation").Range("E109").Value
Controls("Label912").Caption = ThisWorkbook.Sheets("Price calculation").Range("E110").Value
Controls("Label913").Caption = ThisWorkbook.Sheets("Price calculation").Range("E111").Value
Controls("Label914").Caption = ThisWorkbook.Sheets("Price calculation").Range("E112").Value
Controls("Label915").Caption = ThisWorkbook.Sheets("Price calculation").Range("E113").Value
Controls("Label916").Caption = ThisWorkbook.Sheets("Price calculation").Range("E114").Value
Controls("Label917").Caption = ThisWorkbook.Sheets("Price calculation").Range("E115").Value
Controls("Label918").Caption = ThisWorkbook.Sheets("Price calculation").Range("E116").Value
Controls("Label919").Caption = ThisWorkbook.Sheets("Price calculation").Range("E117").Value
Controls("Label920").Caption = ThisWorkbook.Sheets("Price calculation").Range("E118").Value
Controls("Label921").Caption = ThisWorkbook.Sheets("Price calculation").Range("E119").Value
Controls("Label922").Caption = ThisWorkbook.Sheets("Price calculation").Range("E120").Value
Controls("Label923").Caption = ThisWorkbook.Sheets("Price calculation").Range("E121").Value
Controls("Label924").Caption = ThisWorkbook.Sheets("Price calculation").Range("E122").Value
Controls("Label925").Caption = ThisWorkbook.Sheets("Price calculation").Range("E123").Value
Controls("Label926").Caption = ThisWorkbook.Sheets("Price calculation").Range("E124").Value
Controls("Label927").Caption = ThisWorkbook.Sheets("Price calculation").Range("E125").Value

Use loops? Your label numbers and ranges appear to go in sequence. — SJR, Nov 23 '18 at 11:54
+ Instead of using a lot of labels on different Tabs on your multipage, you could re-use your Labels. — EvR, Nov 23 '18 at 12:13
See some hints to reduce KB limitation [Getting error .. Too Large in VBA macros Excel](https://stackoverflow.com/questions/11450232/getting-error-procedure-too-large-in-vba-macros-excel) — T.M., Nov 24 '18 at 17:42
Loop, class, dictionary (or array), are needed, except if you want to spend hours on building all those labels and naming them... Also, classes, make it possible to change the number of labels. — Patrick Lepelletier, Dec 04 '18 at 23:39

Dy.Lee · Accepted Answer · 2018-11-26T15:42:31.580

3

Try

Dim vDB As Variant, a As Variant, c As Variant
Dim Ws As Worksheet
Dim i As Integer, j As Integer, n As Integer
Set Ws = ThisWorkbook.Sheets("Price calculation")

a = Array("a", "d", "e") 'column characters
c = Array(841, 875, 911) 'label numbers

For i = LBound(a) To UBound(a)
    vDB = Ws.Range(a(i) & 109).Resize(16)
    n = 0
    For j = c(i) To c(i) + 15
        n = n + 1
        Me.Controls("Label" & j).Caption = vDB(n, 1)
    Next j
Next i

If the size of the data is different, one variable k will be given.

Dim vDB As Variant, a As Variant, c As Variant
Dim Ws As Worksheet
Dim i As Integer, j As Integer, n As Integer
Dim k As Integer

Set Ws = ThisWorkbook.Sheets("Price calculation")

a = Array("a", "d", "e") 'column characters
c = Array(841, 875, 911) 'label numbers

For i = LBound(a) To UBound(a)
    If i = 0 Then
        k = 16
    Else
        k = 17
    End If
    vDB = Ws.Range(a(i) & 109).Resize(k)
    n = 0
    For j = c(i) To c(i) + k - 1
        n = n + 1
        Me.Controls("Label" & j).Caption = vDB(n, 1)
    Next j
Next i

edited Nov 26 '18 at 15:42

answered Nov 23 '18 at 15:12

Dy.Lee

7,527
1
12
14

Thank you! However NOTE that in the first column A there are 16 items and all the rest 17. – 10101 Nov 26 '18 at 13:45
@user7202022, Answers were added. – Dy.Lee Nov 26 '18 at 14:00
Your code looks the most interesting implementation. For some reason I can't get it work. P.S. There is "i" outside of "code" tag in your last edit. I have added it in my macro but for some reason it still not working. Getting an error all the time – 10101 Nov 26 '18 at 15:04
@user7202022, I mistyped " a & (i) & 109 ". This to be a(i) & 109. – Dy.Lee Nov 26 '18 at 15:43

Pspl · Answer 2 · 2018-11-23T12:07:20.477

You could make your procedure a little bit smaller (and faster) by doing the follow:

With ThisWorkbook.Sheets("Price calculation")
    Controls("Label841").Caption = .Range("A109").Value
    Controls("Label842").Caption = .Range("A110").Value
    Controls("Label843").Caption = .Range("A111").Value
    ....

End With

Besides that, you can write the equivalent thing with a for... nextstatement. For instance, the first 16 lines of your code could be replaced with the following routine:

With ThisWorkbook.Sheets("Price calculation")
    For x = 841 to 856
        Controls("Label" & x).Caption = .Range("A" & x - 732).Value
    Next x
End With

or:

With ThisWorkbook.Sheets("Price calculation")
    For x = 841 to 856
        Controls("Label" & x).Caption = .Cells(x - 732, 1).Value
    Next x
End With

This kind of practice reduces A LOT your code size and makes your procedures A LOT faster.

Thank you for your answer! Can you please add example for two blocks like Label841-Label856 and Label875-Label891? Thank you! — 10101, Nov 23 '18 at 13:53

score 0 · Answer 3 · answered Nov 23 '18 at 12:38

Start with a blank form. Add a multipage control with a single page. Put this code into the UserForm Initialize event

Private Sub UserForm_Initialize()


Dim x As Integer: Dim y As Integer: Dim counter As Integer
Dim SourceRange As Range
Set SourceRange = ThisWorkbook.Sheets("Price calculation").Range("A109:A124")

Dim p As Control
Dim lab As Control
Const rowoffset = 20 'height of each row
Const startpoint = 60  'position of top row in tab
Const columnoffset = 3 'where next columns for captions are on spreadhseet
y = 12 'indent from left of form
Dim r As Range
For counter = 0 To 2
Set p = Me.MyMultiPage.Pages(counter)
x = startpoint
For Each r In SourceRange
Set lab = p.Controls.Add("Forms.Label.1")
lab.Left = y
lab.Top = x
lab.Width = 100
lab.Caption = r.Text
x = x + rowoffset
Next r
Set SourceRange = SourceRange.Offset(0, columnoffset)
If counter = Me.MyMultiPage.Pages.Count - 1 Then
    Me.MyMultiPage.Pages.Add "Page" & counter + 1, "Page" & counter + 1, counter + 1
End If

Next counter
End Sub

play with the constants till it looks pretty/fits in the form

Excel VBA Procedure is too large UserForm

3 Answers3