Excel VBA 程序太大用户窗体

Question

我使用下面的代码在单独的窗口中显示计算结果，以跟踪实时时间的变化。这只是代码的一小部分，它在下面继续向下很多行。我有很长的代码，想让我的用户窗体在选项卡式 View (多页)中显示摘要报告。我只能使用以下代码创建一个选项卡。当我尝试添加第二个“选项卡”时，我得到“程序太大”。

我的代码:

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

Answer 1

尝试

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

如果数据的大小不同，将给出一个变量k。

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