excel - Modify an Arbitrary Number of Variables Within a Subroutine
问题描述
So I have an application where I'm looking up data from a bunch of different tables and entering the data into a bunch of variables to be used elsewhere. I wrote a function to look up and return a row (as a Range) from a specified table given an entry in the first column:
Public Function RowLookup(table As Range, entry As String) As Variant
Dim rowNum As Variant
rowNum = Application.Match(entry, table.Columns.item(1), 0)
If IsError(rowNum) Then
RowLookup = CVErr(xlErrValue)
Else
RowLookup = table.Rows(rowNum)(1)
End If
End Function
However, now I've ended up with a ton of code that looks like this:
tempRow = RowLookup(Range("Table1"), var1)
If IsError(tempRow) Then
var2 = ""
var3 = ""
var4 = ""
var5 = ""
Debug.Print "Error looking up data"
Else
var2 = tempRow(1, 2)
var3 = tempRow(1, 3)
var4 = tempRow(1, 4)
var5 = tempRow(1, 6)
End If
Any ideas on how to work this boilerplate code into the function? I want it to take an arbitrarily long list of (colIndex, variable) pairs and then set each variable based on columnIndex. Using a Collection doesn't seem to work as I can only change the entry in the Collection, not the original variable.
解决方案
You could pass the Vars and Table Index's as a ParamArray
I'd rework RowLookup
as a Sub. Param Arrayexpects pairs of Variables, and Table Column Numbers.
- An Odd number of passed parameters cauase an error.
- A Column Index > number of columns in Table is ignored
- Vars don't have to be Variants (they will be type cast, so could throw errors)
Public Sub RowLookup(table As Range, entry As Variant, ParamArray Vars() As Variant)
Dim rowNum As Variant
Dim i As Long
Dim TableData As Variant
Dim TableColumns As Long
If WorksheetFunction.IsEven(UBound(Vars)) Then
For i = 0 To UBound(Vars) - 1 Step 2
Vars(i) = CVErr(xlErrNA)
Next
Else
rowNum = Application.Match(entry, table.Columns(1), 0)
If IsError(rowNum) Then
For i = 0 To UBound(Vars) - 1 Step 2
Vars(i) = CVErr(xlErrValue)
Next
Else
TableData = table.Value
TableColumns = UBound(TableData, 2)
For i = 0 To UBound(Vars) - 1 Step 2
If Vars(i + 1) >= 1 And Vars(i + 1) <= TableColumns Then
Vars(i) = TableData(rowNum, Vars(i + 1))
End If
Next
End If
End If
End Sub
and call it like this
Sub Demo()
Dim SomeLongVar As Long
Dim SomeStringVar As String
Dim var3(1 To 2) As Variant
Dim var4 As Variant
Dim var5 As Variant
var3(2) = 5 ' Column Index
RowLookup Range("Table1"), "x", SomeLongVar, 2, SomeStringVar, 3, var3(1), var3(2)
Debug.Print SomeLongVar, SomeStringVar, var3(1)
End Sub
推荐阅读
- laravel - 如何减少laravel中的数据大小以进行ajax响应?
- javascript - 如何使用 Vue.js 更改 NUXT.js 中的元素
- html - 如何使用常规文本输入上的清除“x”按钮获取搜索输入的行为?
- android - 添加隐藏/显示编辑文本
- typescript - 如何使 VSCode 自动导入在 lerna-yarn-typescript 项目中工作?
- c++ - 使用 Simd::Motion::Detector 检测小型快速物体
- sql - 使用 has_many 列出所有没有父类别的条目,并通过连接表关系
- angular - 尝试接受阅读后的服务
- node.js - 错误类型错误:this.statutForm.value.poste.subscribe 不是函数
- json - 我得到一个 - 类型'列表
' 不是类型 'Map 的子类型 ' 错误