vba 如何将字符串传递给VBA中的函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17973656/
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 pass string to a function in VBA?
提问by nedstark179
I am trying to pass different table names as strings so they can be used in a function that will export the table I specify to an excel spreadsheet. I cannot get it to work because when I try to do the "DoCmd.TransferSpreadsheet" command, it says that it does not recognize "table_name". I thought that if I passed a string name with the name of the table that it would work, but apparently it doesn't. Am I passing the string wrong, or am I not allowed to do this? Also, what type of excel spreadsheet should I export it as? I didn't see the differences between the different types.
我试图将不同的表名作为字符串传递，以便它们可以在将我指定的表导出到 Excel 电子表格的函数中使用。我无法让它工作，因为当我尝试执行“DoCmd.TransferSpreadsheet”命令时，它说它无法识别“table_name”。我认为如果我传递一个带有表名的字符串名称，它会起作用，但显然它不会。我传递的字符串是错误的，还是我不允许这样做？另外，我应该将其导出为哪种类型的 Excel 电子表格？我没有看到不同类型之间的差异。
Public Function NAME_FILE(table_name As String)
Dim strName As String
Dim strLocation As String
Dim strXLS As String
Dim strFinalName As String
strName = InputBox("What do you want to name the file?", "File Name")
strLocation = "C:\folder1\"
strXLS = ".xls"
strFinalName = strLocation & "" & strName & "" & strXLS
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "table_name", strFinalName, True
End Function
Public Function EXPORT_PRODUCT_CODE()
Dim T_PRODUCT_CODE As String
NAME_FILE (T_PRODUCT_CODE)
End Function
Public Function EXPORT_CAMPAIGN_CODE()
Dim T_CAMPAIGN_CODE As String
NAME_FILE (T_CAMPAIGN_CODE)
End Function
回答by HansUp
table_nameis the name of the parameter you're using to hold the name of a table. So, for TransferSpreadsheet, do not use quotes around that name. Then you will be feeding TransferSpreadsheetthe name of the table instead of the literal text "table_name".
table_name是用于保存表名称的参数的名称。因此，对于TransferSpreadsheet，不要在该名称周围使用引号。然后，您将提供TransferSpreadsheet表的名称而不是文字文本"table_name"。
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
table_name, strFinalName, True
IOW, you want to handle table_namethe same as you did for your strFinalNamevariable --- don't enclose the name with quotes.
IOW，您想像处理strFinalName变量一样处理table_name---不要用引号将名称括起来。
Also you have 2 other functions which call your NAME_FILE()function. Each of those creates a local string variable which is passed to NAME_FILE(). However you don't assign a table name to either of those variables, so the result is the same as NAME_FILE(""). Then, when you hit the TransferSpreadsheetstatement, you have an empty string ("") for the TableNameparameter which causes Access to complain "The action or method requires a Table Name argument."
此外，您还有 2 个其他函数调用您的NAME_FILE()函数。每个都创建一个本地字符串变量，该变量传递给NAME_FILE(). 但是，您没有为这些变量中的任何一个分配表名，因此结果与NAME_FILE(""). 然后，当您点击该TransferSpreadsheet语句时，TableName参数有一个空字符串 ( "")，这会导致 Access 抱怨“操作或方法需要表名参数”。
