excel 如何在字符串中使用“部分限定”名称修改窗体控件?

hs1ihplo  于 2023-03-13  发布在  其他
关注(0)|答案(1)|浏览(98)

我有三个子例程,它们与下面的子例程基本相同,使用Partially Qualified Label Name硬编码,即"Label's Name没有Index

Sub lblToggle2(ByRef lblYr As Long)
    Dim i
    Dim lblcnt As Long
    If Not lblYr = 0 Then
        lblcnt = wbCurYear - lblYr + 1
        Debug.Print lblYr, lblcnt
        For i = 1 To lblcnt
            Me.Controls("lblCostYrE" & i).Caption = lblYr
            Me.Controls("lblCostYrG" & i).Caption = lblYr
            lblYr = lblYr + 1
        Next i
    Else
        For i = 1 To 5
            Me.Controls("lblCostYrE" & i).Caption = vbNullString
            Me.Controls("lblCostYrG" & i).Caption = vbNullString
        Next i
    End If
End Sub

上面的sub部分基于This Link,调用时使用:

Call lblToggle2(StartYear)

我正在尝试使用一个Sub来处理多个Label组合。
以下是几种尝试中的两种。
调用代码:

Call lblToggleTest1(StartYear, """lblCostYrG""", """lblCostYrE""")
Call lblToggleTest2(StartYear, """lblCostYrG""", """lblCostYrE""")

子程序代码:

Sub lblToggleTest1(ByRef lblYr As Long, lblG As String, lblE As String)
    Dim i
    Dim lblcnt As Long
 
    If Not lblYr = 0 Then
        lblcnt = wbCurYear - lblYr + 1
        For i = 1 To lblcnt
            Debug.Print lblYr, cntrl1.Name, cntrl2.Name, lblcnt
            Me.Controls(lblG).Caption = lblYr
            Me.Controls(lblE).Caption = lblYr
            lblYr = lblYr + 1
        Next i
    Else
        For i = 1 To 5
            Me.Controls(lblG).Caption = vbNullString
            Me.Controls(lblE).Caption = vbNullString
        Next i
    End If
End Sub

Sub lblToggleTest2(ByRef lblYr As Long, lblG As String, lblE As String)
    Dim i
    Dim lblcnt As Long
    Dim cntrl1 As Object
    Dim cntrl2 As Object
    cntrl1 = lblG & (i)
    cntrl2 = lblE & (i)
 
    If Not lblYr = 0 Then
        lblcnt = wbCurYear - lblYr + 1
        For i = 1 To lblcnt
        Debug.Print lblYr, cntrl1.Name, cntrl2.Name, lblcnt
            Me.Controls(cntrl1).Caption = lblYr
            Me.Controls(cntrl2).Caption = lblYr
            lblYr = lblYr + 1
        Next i
    Else
        For i = 1 To 5
            Me.Controls(cntrl1).Caption = vbNullString
            Me.Controls(cntrl2).Caption = vbNullString
        Next i
    End If
End Sub

我尝试将Label变量声明为VariantObjectString
我得到Object Not FoundObject Required或其他错误。

e0bqpujr

e0bqpujr1#

调用代码中的引号太多。
未测试:

Sub Tester()
    lblToggleTest StartYear, "lblCostYrG", "lblCostYrE"
End Sub

Sub lblToggleTest(ByRef lblYr As Long, lblG As String, lblE As String)
    Dim i As Long, lblcnt As Long, haveYear As Boolean, v
    
    haveYear = lblYr <> 0 'was a start year provided?
    lblcnt = IIf(haveYear, wbCurYear - lblYr + 1, 5)
    
    For i = 1 To lblcnt
        v = IIf(haveYear, lblYr, vbNullString)
        Me.Controls(lblE & i).Caption = v
        Me.Controls(lblG & i).Caption = v
        If haveYear Then lblYr = lblYr + 1
    Next i
    
End Sub

相关问题