VBA:如何最好地将三元组信息存储在一起
VBA:如何最好地将三元组信息存储在一起
提问于 2020-07-08 16:23:19
我需要通过搜索存储在列A中的行名称来查找一些行。到目前为止，每次我需要这样做时，我都在新的一行中调用了函数(FindRow)。这意味着我的代码看起来像这样：
Option Explicit
Public fRowType As Long
Public fRowClosing As Long
Public fRowHPPlanDate As Long
Public fRowLoan As Long
Public fRowDoS As Long
Public fRowShare As Long
Public fRowInvestmentType As Long
Public fRowObjectNumber As Long
Public fRowObjectName As Long
Public fRowRisk As Long
Public fRowMacro As Long
Public fRowCountry As Long
Public fRowCity As Long
Public fRowConstruction As Long
Public fRowModernization As Long
Public fRowUsage As Long
Public fRowHPPlanYear As Long
Public fRowHP As Long
Public fRowInterest As Long
Public fRowBorrowed As Long
Public fRowLTV As Long
Public fRowEquity As Long
Public fRowSPVRev As Long
Public fRowSPVExp As Long
Public fRowNCF As Long
Public fRowIRR As Long
Public fRowIRRIM As Long
Public fRowCapRate As Long
Public fRowNOIAcq As Long
Public fRowLeased As Long
Public fRowRentalUnits As Long
Public fRowParking As Long
Public fRowTotalArea
Public fRowRent As Long
Public fRowNOI As Long
Public fRowWalt As Long
Public fRowPriceNet As Long
Public fRowExchange As Long
Public fRowCurrency As Long
Public fRowPriceGross As Long
Public fRowGIK As Long
Public fRowBook As Long
Public fRowMarketValue As Long
Public fRowMarketValuePerc As Long
Public fRowDeterminationMarketValue
Public fRowRepatriation As Long
Public fRowSalesPrice As Long
Sub Test()
Call DefiningRows(ActiveSheet)
End Sub
Sub DefiningRows(ws As Worksheet)
fRowType = FindRow("Type", "A")
fRowClosing = FindRow("Closing", "A")
fRowHPPlanDate = FindRow("Holding Period Plan Date (BP)", "A")
fRowLoan = FindRow("End of Loan", "A")
fRowDoS = FindRow("Date of Sale", "A")
fRowShare = FindRow("BVK-Share (%)", "A")
fRowInvestmentType = FindRow("Investmet Type", "A")
fRowObjectNumber = FindRow("Objectnumber", "A")
fRowObjectName = FindRow("Objectname", "A")
fRowRisk = FindRow("Risk Allocation", "A")
fRowMacro = FindRow("Macro Allocation", "A")
fRowCountry = FindRow("Country", "A")
fRowCity = FindRow("City", "A")
fRowConstruction = FindRow("Construction Year", "A")
fRowModernization = FindRow("Modernization Year", "A")
fRowUsage = FindRow("Main Usage", "A")
fRowHPPlanYear = FindRow("Holding Period Plan Year (BP)", "A")
fRowHP = FindRow("Holding Period Plan Year (BP)", "A") + 2
fRowInterest = FindRow("Interest on debt (ytd.)", "A") + 1
fRowBorrowed = FindRow("Borrowed Capital (Delta)", "A") + 1
fRowLTV = FindRow("LTV (Delta)", "A") + 1
fRowEquity = FindRow("Equity Investment (Delta)", "A") + 1
fRowSPVRev = FindRow("SPV Revenues (ytd.)", "A") + 1
fRowSPVExp = FindRow("SPV Expenses (ytd.)", "A") + 1
fRowNCF = FindRow("NCF (ytd.)", "A") + 1
fRowIRRIM = FindRow("IRR (IM)", "A")
fRowIRR = FindRow("IRR (Forecast)", "A") + 1
fRowCapRate = FindRow("CapRate (Acquisition)", "A") + 1
fRowLeased = FindRow("Leased Area (m²)", "A") + 1
fRowRentalUnits = FindRow("Rental Units", "A", PartOrWhole:=xlPart)
fRowParking = FindRow("Parking Spaces", "A")
fRowTotalArea = FindRow("Total Area (m²)", "A")
fRowRent = FindRow("Contractual Rent", "A") + 1
fRowNOI = FindRow("NOI (ytd.)", "A") + 1
fRowWalt = FindRow("WALT", "A") + 1
fRowPriceNet = FindRow("Purchase Price (net)", "A") + 1
fRowExchange = FindRow("Exchange Rate", "A")
fRowCurrency = FindRow("Currency", "A")
fRowPriceGross = FindRow("Purchase Price (gross)", "A") + 1
fRowGIK = FindRow("Total Costs (GIK)", "A") + 1
fRowBook = FindRow("Book Value", "A") + 1
fRowMarketValue = FindRow("Market Value", "A") + 1
fRowMarketValuePerc = FindRow("Market Value", "A")
fRowDeterminationMarketValue = FindRow("Determination of market value", "A")
fRowSalesPrice = FindRow("Sales Price", "A")
fRowRepatriation = FindRow("Equity Repatriation", "A")
End Sub
Function FindRow(ByVal searchTerm As String, ByVal col As String, Optional ws As Worksheet, Optional ByVal PartOrWhole As XlLookAt = xlWhole) As Long
Dim searchRng As Range ' the range to search, based on the column passed to the function
Dim foundCell As Range ' the found match
If ws Is Nothing Then Set ws = ActiveSheet
With ws
Set searchRng = .Range(.Cells(1, col), .Cells(.Rows.Count, col).End(xlUp))
End With
Set foundCell = searchRng.Find(searchTerm, LookAt:=PartOrWhole)
If Not foundCell Is Nothing Then
FindRow = foundCell.Row
Else
MsgBox searchTerm & " couldn't be found. Macro will continue."
End If
End Function我的想法是创建一个包含所有searchTerm的数组，并对其进行循环。问题是，我意识到我每次都有三条信息:我想要创建的变量的名称(如fRowType )、searchTerm和我添加到其中的行(0，1，2)。我可能想得太多了，但我想不出什么办法，于是决定用谷歌搜索我的问题。有一篇文章使用了Collection对象。我试过了，但很快意识到这可能不是一个好主意。我甚至没有使用变量名创建数组，因为它看起来像这样：
Sub DefiningRows(ws As Worksheet) 'Loop -> is xlPart necessary? if so, separate array
Dim fRowCollection As Collection
Set fRowCollection = New Collection
fRowCollection.Add Array("Type", "Closing", "Holding Period Plan Date (BP)", "End of Loan", "Date of Sale", "BVK-Share (%)", "Investmet Type", "Objectnumber", _
"Objectname", "Risk Allocation", "Macro Allocation", "Country", "City", "Construction Year", "Modernization Year", "Main Usage", _
"Holding Period Plan Year (BP)", "Holding Period Plan Year (BP)", "Interest on debt (ytd.)", "Borrowed Capital (Delta)", "Equity Investment (Delta)", _
"SPV Revenues (ytd.)", "SPV Expenses (ytd.)", "NCF (ytd.)", "IRR (IM)", "IRR (Forecast)", "CapRate (Acquisition)", "NOI (Aquisition)", "Leased Area (m²)", _
"Parking Spaces", "Total Area (m²)", "Contractual Rent", "NOI (ytd.)", "WALT", "Purchase Price (net)", "Exchange Rate", "Currency", "Purchase Price (gross)", _
"Total Costs (GIK)", "Book Value", "Market Value", "Market Value", "Determination of market value", "Sales Price", "Equity Repatriation"), "searchTerm"
fRowCollection.Add Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2, 1, 1, 1, 1, 1, 1, 1, 0, 1, 1, 0, 1, 0, 0, 0, 1, 1, 1, 1, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0), "additionalRow"
End Sub对我来说，这看起来不是很好读，如果我或其他人要从数组中删除某些内容，他们将不得不遍历元素，以确保删除additionalRow数组中的正确条目。看起来不切实际。
做这件事的好方法是什么？
回答 2
Stack Overflow用户
回答已采纳
发布于 2020-07-11 14:19:10
您可以在自定义类中编写一个函数，允许您以更简洁的方式对属性进行硬编码：
我的类现在看起来像这样：
Public property1 As Long
Public property2 As String
Public property3 As String
Public Function Add(param1 As Long, param2 As String, param3 As String, ByRef col As Collection) As Collection
property1 = param1
property2 = param2
property3 = param3
col.Add Me, param2
Set Add = col
End Function它接受四个参数--我想要设置的三个属性和我想要将类本身添加到的集合。注意，col.Add Me, param2使用Me来添加类本身，并将param2或property2 (此时相同)作为键。
然后，我可以编写一个函数，在其中存储和返回所有属性，如下所示：
Function GetMyClasses() As Collection
Dim returnCollection As New Collection
Dim f1 As New Class1: f1.Add 10, "Type", "A", returnCollection
Dim f2 As New Class1: f2.Add 20, "Closing", "A", returnCollection
Dim f3 As New Class1: f3.Add 30, "Etc", "B", returnCollection
set GetMyCollection = returnCollection
End Functionf1，f2命名法在这里无关紧要，一旦返回集合，你就不需要处理类的名称，只需处理集合内类的每个实例中的属性：
Sub TestMe()
Dim aCollection As New Collection
Set aCollection = GetMyClasses
MsgBox aCollection("Type").property3
End Sub同样，您可以使用许多不同的方法来处理它，并编写自己的函数来提供您想要的确切语法，但我希望这将向您展示一些更酷的事情，您可以使用类来做这些事情！
Stack Overflow用户
发布于 2020-07-08 22:30:32
使用一个类，你可以做这样的事情。
创建一个名为clsRow的新类，并为其提供三个简单的公共属性：
Public Row As Long
Public SearchTerm As String
Public Name As String现在，如果我将所有属性分配存储在sheet1上的一个表中，例如：
我可以创建一个集合，循环遍历工作表数据，并使用该数据逐行填充我的新类的许多实例，将其添加到集合中：
Sub example()
Dim myRows As New Collection
Dim c As clsRow
For i = 2 To 11
Set c = New clsRow
' next lines assign the new class properties based on the sheet values
c.Name = Sheet1.Range("A" & i).Value2
c.Row = Sheet1.Range("B" & i).Value2
c.SearchTerm = Sheet1.Range("C" & i).Value2
' next line adds this particular instance of the class
' to the collection with a KEY based on the class' name (can use any property)
myRows.Add c, c.Name '
Next i
' I can now use any key to call on any member of the collection
MsgBox myRows("e").SearchTerm
' e.g. will produce "Look 5"
MsgBox myRows("b").Row
' e.g. will produce "10"
End Sub简而言之，您将所有数据定义放在一个易于维护的位置(隐藏工作表？！而不是硬编码)，通过循环遍历工作表来填充您的自定义类的集合，然后您就可以更轻松地随时获得一大堆数据。
有无数种方法可以在这个想法的基础上构建，所以祝你好运，玩得开心！
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/62790514
复制相关文章
相似问题
腾讯云开发者
