社区首页 >问答首页 >如何将用户输入(字符串变量)转换为Excel表引用

问如何将用户输入(字符串变量)转换为Excel表引用
EN

Stack Overflow用户

提问于 2020-03-07 12:58:23

回答 2查看 189关注 0票数 1

我有一个复杂的Excel 2016股票和期权交易应用程序，但一个简单的问题并没有很明显的答案。市场关闭后，将提示交易员输入包含当天事务的电子表格的名称，该电子表格存储在Excel表中，每笔交易有一行。

问题：如何将用户输入(例如字符串变量"Mar06“)转换为名称管理器中列出的tblMar06表名，应用程序可以使用结构化表引用执行统计，而不需要多个数组或列表对象或VLOOKUP(.，间接)语句？

简单的例子：

'User is prompted for spreadsheet name, e.g. Mar06, then  
tableName = "tbl" & "Mar06"  
'tableName is then converted to an Excel table name (the question is how?)  
...  
...  
...  
'The Excel table name is then used for statistic calculations, e.g.  
If ([tblMar06[@Type]] = "Opt") AND ([tblMar06[@Action]] = "SLD") Then     
    [tblMar06[@[Realized P&L]]] = [tblMar06[@Qty]] * 100 * [tblMar06[@Price]] - _ 
    [tblMar06[@Comm]] - [tblMar05[Cost]].Rows(i)  
Else

excel

vba

回答 2

Stack Overflow用户

回答已采纳

发布于 2020-03-09 22:51:44

针对上述问题，我设计了一个简单的解决方案，允许我继续使用具有通用表名的结构化Excel表引用，例如tblTrades，这对于所有工作表来说都是一样的。

...  
Dim ws As Worksheet, tbl As ListObject, sWS As String, sTbl As String, _ 
    tblSave As ListObject
...          
'User inputs the current spreadsheet name, e.g. Mar06  
...  
sWS = "Mar06"    
sTbl = "tbl" & sWS 'current trades table name, e.g. tblMar06  
Set ws = ThisWorkbook.Sheets(sWS)  
ws.Activate  
set tbl =wsListObjects(sTbl)  
Set tblSave = tbl    
tbl.Name = "tblTrades" 'rename table
...      
If ([tblTrades[@Type]] = "Opt") And ([tblTrades[@Action]] = "SLD") Then  
    [tblTrades[@[Realized P&L]]] = [tblTrades[@Qty]] * 100 * _
    [tblTrades[@Price]] - [tblTrades[@Comm]] - Cost  
    'where Cost comes from a previous day's buy  
Else  
    ...  
End If  
tblSave.Name = sTbl 'restore table name to tblMar06  
...

票数 1

Stack Overflow用户

发布于 2020-03-07 13:34:16

，对工作表名
的无效条目进行无错误测试，对工作表

上的无效表条目进行无错误测试()

Option Explicit
Function myTbl(wsName As String) As ListObject
    Set myTbl = ThisWorkbook.Worksheets(wsName).ListObjects("tbl" & wsName)
End Function

Sub tester()
    Dim LO As ListObject
Set LO = myTbl(Application.InputBox("Sheet Name: ", , , , , , , 2))

Stop

'Do your calculations

End Sub

如果工作表名称或表名无效，则在将LO设置为所需表的函数中将得到一个subscript out of range错误。

关于公式和Excel类型的结构化引用，您将遇到一些问题。

Excel公式不能写入其他单元格。因此，即使使用Evaluate方法，仍然需要捕获IF语句的结果，然后将其写入工作表中所需的位置。

可行，但在我看来，比只使用本机VBA函数更复杂。

此外，如果速度成为一个问题，您可以很容易地调整本机VBA方法来读取/写入vba数组，而不是从工作表中读取/写入速度显著提高的方法。

可读性可能是一个问题，但下面是一个示例，说明您重新编写的使用本机VBA函数的公式，但仍然保留了表的有用名称：

注意，下面公式中的Cost 因子将返回零，因为我不清楚您在哪里获得应用于示例中的第一项和最后一项的成本。

Sub terfuge()
    Dim LO As ListObject
    Dim I As Long

Set LO = myTbl(InputBox("Worksheet Name: "))
With LO.ListColumns
    For I = 1 To LO.DataBodyRange.Rows.Count
       If .Item("Type").DataBodyRange(I) = "Opt" And _
          .Item("Action").DataBodyRange(I) = "SLD" Then

            .Item("Realized P&L").DataBodyRange(I) = _
                .Item("Qty").DataBodyRange(I) * 100 * _
                .Item("Price").DataBodyRange(I) - _
                .Item("Comm").DataBodyRange(I) - _
                .Item("Cost").DataBodyRange(I)
        End If
    Next I
End With

End Sub