问题描述
我试图使用VBA excel创建工具.我是这个方面的业余爱好者，需要一些帮助.我有一个专用的按钮/宏，它使用一堆混乱的if/then语句代码.我需要这个按钮来执行我想要的操作，这就是我所拥有的.我收到程序过大"错误，请帮助
I trying to create a tool using VBA excel. I'm am an amateur at this and need some help. I have a dedicated button/macro that uses a mess load of codes if/then statements. I need this one button to do what I want it to do, this is what I have. I'm getting the Procedure Too Large error, please help
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!!!
推荐答案
在我看来，您需要重新考虑一下自己的结构.我假设您已取出所有变量名和字符串，但是如果没有这些，您的代码就会变得毫无意义.如果我们知道我们在看什么，我们只能为您提供结构帮助，那么也许您可以将变量重命名为varA varB varC并将字符串重命名为"stringA""stringB"?比这更有意义.
It seems to me you need to rethink your structure a lot. I assume you have taken out all your variablenames and your strings, but without this, your code becomes nonsense.We can only help you with your structure if we know what we are looking at, so maybe you can rename your variables to varA varB varC and your strings to "stringA" "stringB"? That would make more sense than this.
如果是以下情况，请三思而后行:
A few thoughts, if the following is the case:
`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`
您可以将批次更改为:
If CableCB Then
ws.Cells(newRow, 3).Value = Cable
那将大大缩短您的程序.但是，如果不了解此处的变量和字符串，我们将无法为您提供进一步的帮助.
That would significantly shorten your procedure. But without knowing the variables and strings at play here, we can not really help you any further.
这篇关于VBA Excel过程过大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！