Question

excel中vba命令问题，不能执行，大家帮我看看

vba中每行命令如下，但是却没有办法执行下去，a，b，d无法赋值。帮我看看是怎么回事
（空间工具箱中的组合框已经设置完成，第一个组合框是1-12个数字，第二个组合框是sheet2，sheet3，sheet4）
Public a, b As Integer
Public d, str As String

Private Sub ComboBox1_Change()
a = Choose(ComboBox1.Value, 31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
b = Choose(ComboBox1.Value, 6, 37, 66, 97, 127, 158, 188, 219, 250, 280, 311, 341)
End Sub
Private Sub ComboBox2_Change()
d = Choose(ComboBox2.Value, "sheet2", "sheet3", "sheet4")
End Sub

Sub 数据1()
Dim str
Sheets("sheet1").Range(Cells(1, 2), Cells(a, 42)).Copy
Sheets(d).Select
Range(Cells(b, 3), Cells(a+b, 43)).PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False, Transpose:=False
Sheets("sheet1").Select
Range(Cells(1, 2), Cells(a, 42)).ClearContents
str = "录入完毕!"
MsgBox str, vbOKOnly
End Sub

Answer 1

Public a, b As Integer
Public d, str As String
定义是否放在模块中？
在每次运行过程“数据1”之前，ComboBox1和ComboBox2先发生Change事件时，a、b、d才能取得值。建议a、b、d在过程“数据1”内取值。
Sub 数据1()
Dim str
a = Choose(Sheet1.ComboBox1.Value, 31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
b = Choose(Sheet1.ComboBox1.Value, 6, 37, 66, 97, 127, 158, 188, 219, 250, 280, 311, 341)
d = Choose(Sheet1.ComboBox2.Value, "sheet2", "sheet3", "sheet4")
Sheets("sheet1").Range(Cells(1, 2), Cells(a, 42)).Copy
Sheets(d).Select
Range(Cells(b, 3), Cells(a + b, 43)).PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False, Transpose:=False
Sheets("sheet1").Select
Range(Cells(1, 2), Cells(a, 42)).ClearContents
str = "录入完毕!"
MsgBox str, vbOKOnly
End Sub

Answer 2

Public a, b As Integer
Public d, str As String

Private Sub ComboBox1_Change()
ComboBox1.List = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
a = Choose(ComboBox1.Value, 31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
MsgBox a
b = Choose(ComboBox1.Value, 6, 37, 66, 97, 127, 158, 188, 219, 250, 280, 311, 341)
MsgBox b
End Sub

Private Sub ComboBox2_Change()
ComboBox2.List = Array(1, 2, 3)
d = Choose(ComboBox2.Value, "sheet2", "sheet3", "sheet4")
MsgBox a
MsgBox d
End Sub

Sub 数据1()
Dim str
MsgBox a
Sheets("sheet1").Range(Cells(1, 2), Cells(a, 42)).Copy
Sheets(d).Select
Range(Cells(b, 3), Cells(a + b, 43)).PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False, Transpose:=False
Sheets("sheet1").Select
Range(Cells(1, 2), Cells(a, 42)).ClearContents
str = "录入完毕!"
MsgBox str, vbOKOnly
End Sub
你试下，先combobox1选择一下，后面就有值传过去了。

Answer 3

a = Choose(ComboBox1.Value, 31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
b = Choose(ComboBox1.Value, 6, 37, 66, 97, 127, 158, 188, 219, 250, 280, 311, 341)
d = Choose(ComboBox2.Value, "sheet2", "sheet3", "sheet4")
以上ComboBox1和ComboBox2没有初始化赋值。