时间戳

首页 > 解决方案 > 用户表单的复杂变量名称

excel - 用户表单的复杂变量名称

问题描述

我正在尝试简化我的代码以动态检查一些值并更新一堆复选框。

复选框是一种表格，它们的名称是“col” & number_of_column & type_of_data 即：

Col1PAM Col2PAM Col3PAM 
Col1RL   Col2RL   Col3RL

我尝试了一种简单的方法 for...next 有效：`

For i = 1 To 16

    If ThisWorkbook.Sheets("Setup").Cells(j + 2, i + 1) = 1 Then
        Me.Controls("Col" & i & "PAM").Value = True
        Else: Me.Controls("Col" & i & "PAM").Value = False
    End If

    If ThisWorkbook.Sheets("Setup").Cells(j + 3, i + 1) = 1 Then
        Me.Controls("Col" & i & "RL").Value = True
        Else: Me.Controls("Col" & i & "RL").Value = False
    End If

Next i

但是我有很多列类型，我试图使列类型动态：

Dim Coltype1 As String
Dim coltype2 As String
Coltype1 = "PAM"
coltype2 = "RL"

For j = 1 To 2
    For i = 1 To 16

        If ThisWorkbook.Sheets("Setup").Cells(j + 2, i + 1) = 1 Then
            Me.Controls("Col" & i & ("Coltype" & j)).Value = True
            Else: Me.Controls("Col" & i & ("Coltype" & j)).Value = False
        End If

    Next i
Next j

我的语法不正确，经过多次尝试，我无法弄清楚什么是正确的。有什么建议吗？

附带说明一下，我还尝试了 for...next 方法来声明我的 coltype1、coltype2... 变量，但看起来你不能这样做？

感谢您的任何提示！

标签： excelvba

解决方案

像这样的东西：

Dim arr(1 To 2) As String, ws As WorkSheet, i As Long, j As Long

Set ws = ThisWorkbook.Sheets("Setup")

arr(1) = "PAM"
arr(2) = "RL"

For j = 1 To 2
    For i = 1 To 16
        Me.Controls("Col" & i & arr(j)).Value = (ws.Cells(j + 2, i + 1) = 1)
    Next i
Next j

推荐阅读