如何将 VBA 中的公式扩展到 1 行代码之后
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17979916/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to extend a formula in VBA past 1 line of code
提问by user2639527
So I do not have too much experience with programming and almost non with VBA. My main issue is that the formula in my code extends past 1 line and when I include an underscore, space and then start a new line, I receive an error.
所以我没有太多的编程经验，几乎没有 VBA 经验。我的主要问题是我的代码中的公式超出了 1 行，当我包含下划线、空格然后开始新行时，我收到一个错误。
I have attached a pic of the code, there is probably unnecessary lines of code because I recorded a macro to get the code.
我附上了代码的图片，可能有不必要的代码行，因为我录制了一个宏来获取代码。
More information on what I am trying to do:
有关我正在尝试做什么的更多信息：
I have a list contained in a cell using "data validation" and based on the selection from that list, the cell below will output a certain list.
我有一个包含在使用“数据验证”的单元格中的列表，并根据该列表中的选择，下面的单元格将输出某个列表。
The information for these lists are stored on other worksheets in the workbook.
这些列表的信息存储在工作簿的其他工作表中。
I was able to develop an IF statement in the "data validation" list source box that worked for several inputs. However I have 84 possibilities and i could not fit all the individual if statements in the list source box. Therefore I decided to try and manually input the formula using VBA by recording a macro of the several input "data validation" if statement.
我能够在“数据验证”列表源框中开发一个适用于多个输入的 IF 语句。但是，我有 84 种可能性，我无法在列表源框中包含所有单独的 if 语句。因此，我决定通过记录几个输入“数据验证”if 语句的宏来尝试使用 VBA 手动输入公式。
Here is the code:
这是代码：
Sub HelpSetUp()
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _
"=if($B='fuel columns'!$A,agriculturalbiproduct,if($B='fuel columns'!$B,agriculturalresidue,if($B='fuel columns'!$C,agriculturalwaste,Nofuel)))"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
回答by Shari W
When you have a long bit of text to squeeze in, you need to break it up into chunks using "&" and _.
当您有很长的文本要挤入时，您需要使用“&”和 _ 将其分成块。
Like this
像这样
dim aString as string
将 aString 调暗为字符串
aString = "four score and seven years ago our fathers " & _
"set forth on this continent a new nation, " & _
"conceived in liberty and dedicated to the " & _
"proposition that all men are created equal."
Be sure to leave a space between the & and the _.
一定要在 & 和 _ 之间留一个空格。
SMW
微博
