VBA Excel程序过大

Question

我试图使用VBA excel创建一个工具。我是这方面的业余爱好者，需要一些帮助。我有一个专用的按钮/宏，使用混乱的代码负载if/then语句。我需要这个按钮来做我想要它做的事，这就是我有的。程序太大错误，请帮助

Private Sub Save_Click()
Dim ws As Worksheet
Set ws = Worksheets("Sales Tracker")
Dim newRow As Long
newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
ws.Cells(newRow, 1).Value = Me.AcctNum.Value
If AcctNum = "" Then
ws.Cells(newRow, 1).Value = "--------"
End If
If CableCB = True And Cable = "private" Then
ws.Cells(newRow, 3).Value = "private"
ElseIf CableCB = True And Cable = "private" Then
ws.Cells(newRow, 3).Value = "private"
ElseIf CableCB = True And Cable = "private" Then
ws.Cells(newRow, 3).Value = "private"
ElseIf CableCB = True And Cable = "private" Then
ws.Cells(newRow, 3).Value = "private"
ElseIf CableCB = True And Cable = "private" Then
ws.Cells(newRow, 3).Value = "Cable"
ElseIf CableCB = True And Cable = "--------" Then
ws.Cells(newRow, 3).Value = "ERROR"
ElseIf CHSICB = True And CHSI = "private" Then
ws.Cells(newRow, 3).Value = "private"
ElseIf CHSICB = True And CHSI = "private" Then
ws.Cells(newRow, 3).Value = "private"
ElseIf CHSICB = True And CHSI = "private" Then
ws.Cells(newRow, 3).Value = "private"
ElseIf CHSICB = True And CHSI = "private" Then
ws.Cells(newRow, 3).Value = "private"
ElseIf CHSICB = True And CHSI = "private" Then
ws.Cells(newRow, 3).Value = "private"
ElseIf CHSICB = True And CHSI = "--------" Then
ws.Cells(newRow, 3).Value = "ERROR"
ElseIf CDVCB = True And CDV = "private" Then
ws.Cells(newRow, 3).Value = "private"
ElseIf CDVCB = True And CDV = "private" Then
ws.Cells(newRow, 3).Value = "private"
ElseIf CDVCB = True And CDV = "private" Then
ws.Cells(newRow, 3).Value = "private"
ElseIf CDVCB = True And CDV = "--------" Then
ws.Cells(newRow, 3).Value = "ERROR"
ElseIf XH = True Then
ws.Cells(newRow, 3).Value = "private"
End If
If private= True And private= True And Cable = "private" And private= "private" Then
ws.Cells(newRow, 3).Value = "private"
ElseIf CableCB = True And private= True And Cable = "private" And private= "--------" Then
ws.Cells(newRow, 3).Value = "private"
ElseIf CableCB = True And private= True And Cable = "private" And private= "private" Then
ws.Cells(newRow, 3).Value = "private"
ElseIf CableCB = True And private= True And Cable = "private" And private= "private" Then
ws.Cells(newRow, 3).Value = "private"
ElseIf CableCB = True And CHSICB = True And Cable = "private" And private= "private" Then
ws.Cells(newRow, 3).Value = "private"
ElseIf CableCB = True And CHSICB = True And Cable = "private" And private= "private" Then
ws.Cells(newRow, 3).Value = "private"
ElseIf CableCB = True And private= True And private= "Cable" And CDV = "private" Then

and so on and so on!!!

Answer 1

在我看来，你需要重新考虑你的结构。我假设您已经取出了所有的变量名和字符串，但是如果没有这些，您的代码将变得毫无意义。如果我们知道我们在看什么，我们只能帮助你的结构，所以也许你可以重命名你的变量为varA varB varC和你的字符串为"stringA"stringB"?那会比这个更有意义。

如果出现以下情况，我有一些想法:

`If CableCB = True And Cable = "stringA" Then
    ws.Cells(newRow, 3).Value = "stringA"
ElseIf CableCB = True And Cable = "stringB" Then
    ws.Cells(newRow, 3).Value = "stringB"
ElseIf CableCB = True And Cable = "stringC" Then
    ws.Cells(newRow, 3).Value = "stringC`

您可以将lot更改为:

If CableCB Then
    ws.Cells(newRow, 3).Value = Cable 

这将大大缩短你的程序。但是，如果不了解这里的变量和字符串，我们就无法进一步帮助您。