时间戳

首页 > 解决方案 > 使用循环函数为用户窗体对象设置变量值

excel - 使用循环函数为用户窗体对象设置变量值

问题描述

我的用户表单中有 60 个文本框，我想要一种快速设置它们的值的方法。我目前有：

  Dim TextB1_1, TextB1_2, TextB1_3, TextB1_4, TextB1_5, TextB1_6, TextB1_7, TextB1_8, TextB1_9, TextB1_10, TextB1_11, TextB1_12 As String
  Dim TextB2_1, TextB2_2, TextB2_3, TextB2_4, TextB2_5, TextB2_6, TextB2_7, TextB2_8, TextB2_9, TextB2_10, TextB2_11, TextB2_12 As String
  Dim TextB3_1, TextB3_2, TextB3_3, TextB3_4, TextB3_5, TextB3_6, TextB3_7, TextB3_8, TextB3_9, TextB3_10, TextB3_11, TextB3_12 As String
  Dim TextB4_1, TextB4_2, TextB4_3, TextB4_4, TextB4_5, TextB4_6, TextB4_7, TextB4_8, TextB4_9, TextB4_10, TextB4_11, TextB4_12 As String
  Dim TextB5_1, TextB5_2, TextB5_3, TextB5_4, TextB5_5, TextB5_6, TextB5_7, TextB5_8, TextB5_9, TextB5_10, TextB5_11, TextB5_12 As String

并设置我拥有的值：

TextB1_1 = Me.TextBox1_1.Text
TextB1_2 = Me.TextBox2_2.Text
TextB1_3 = Me.TextBox3_3.Text
TextB1_4 = Me.TextBox4_4.Text
TextB1_5 = Me.TextBox5_5.Text
TextB1_6 = Me.TextBox5_6.Text
TextB1_7 = Me.TextBox5_7.Text
TextB1_8 = Me.TextBox5_8.Text
TextB1_9 = Me.TextBox5_9.Text
TextB1_10 = Me.TextBox5_10.Text
TextB1_11 = Me.TextBox5_11.Text
TextB1_12 = Me.TextBox5_12.Text

我需要对文本框组 2、3、4、5 执行相同的操作，尽管循环它并将该组的值设置为 I 会更容易，但是它不起作用，即

  For i = 1 To 5
    TextB&i&_1 = Me.TextBox1_1.Text
    TextB&i&_2 = Me.TextBox2_2.Text
    TextB&i&_3 = Me.TextBox3_3.Text
    TextB&i&_4 = Me.TextBox4_4.Text
    TextB&i&_5 = Me.TextBox5_5.Text
    TextB&i&_6 = Me.TextBox5_6.Text
    TextB&i&_7 = Me.TextBox5_7.Text
    TextB&i&_8 = Me.TextBox5_8.Text
    TextB&i&_9 = Me.TextBox5_9.Text
    TextB&i&_10 = Me.TextBox5_10.Text
    TextB&i&_11 = Me.TextBox5_11.Text
    TextB&i&_12 = Me.TextBox5_12.Text
  Next I

是否可以这样循环，如果可以，我需要做什么，因为我无法让它工作。我也尝试过 TextB1_&i 以防万一它会简化它，但这也不起作用

标签： excelvbauserform

解决方案

使用数组来存储ControlsUserform 对象的值和属性，以按名称寻址文本框：

Dim TextB(1 To 5, 1 To 12) As String
Dim i As Long, j As Long

With Me
    For i = 1 To 5
        For j = 1 To 12
            TextB(i, j) = .Controls("TextBox" & i & "_" & j).Text
        Next
    Next
End With

顺便说一句，在 VBA 中，您必须显式声明所有String变量，否则它们将被隐式假定为Variant类型，即：

Dim TextB1_1, TextB1_2, TextB1_3, TextB1_4, TextB1_5, TextB1_6, TextB1_7, TextB1_8, TextB1_9, TextB1_10, TextB1_11, TextB1_12 As String

将导致TextB1_12仅被假定为String类型，而所有其他类型都被假定为Variant类型。

尽管：

Dim TextB1_1 As String, TextB1_2 As String, TextB1_3 As String, TextB1_4 As String, TextB1_5 As String, TextB1_6 As String, TextB1_7 As String, TextB1_8 As String, TextB1_9 As String, TextB1_10 As String, TextB1_11 As String, TextB1_12 As String

将假定所有这些变量的String类型

推荐阅读