EXCEL VBA:类模块变量值的位置取决于if函数 [英] EXCEL VBA: Class Module Variables value location depending on if function

问题描述

我创建了一个字典来为一个事件组合几位数据.例如，NameA可以有4个条目，具体取决于第5列中的值.它可以是A，B，C，D.但是，我想将A，B，C，D中的所有这些信息显示在表的一行上将NameA作为最终产品.

I have created a dictionary to combine several bits of data for one event. For example, NameA can have four entries depending on the value in column 5. It can be A, B, C, D. However i would like to display all this information from A, B, C, D on one row in a table for NameA as the end product.

因此，我创建了一个带有类模块的字典来显示此内容，该类列出了要显示在最终输出中的所有可能变量.根据条目(A，B，C，D)的不同，第1列中的值可以等于该类中的不同变量.

So i have created a dictionary with a class module to show this, with the class listing all possible variables to display in final output. Depending on the entries (A, B, C, D) the value in column 1 can be equal to different variables in the class.

因此，词典将添加一个NameA和标准信息，而该信息的来源不会更改，但是如果NameA已经存在，它将为该值添加A，B，C，D的现有信息，每个信息均来自不同的值源位置.

SO the dictionary will add one NameA and the standard info which source doesn't change but then if NameA is exists already, it will add the existing info for A, B, C, D to said values, each from a different source location.

例如如果输入A，则列1的值等于PQL；如果输入B，则值1IW；如果C，2IW和D为PPL.

E.g. If entry A, the value is column 1 is equal to PQL, if entry B, the value is 1IW, if C, 2IW and D is PPL.

我使用了if函数，但是在写入数据时它不会为IF语句中的变量写任何东西，例如PQL，1IW，2IW和PPL，每个NameA都保留为空白.因为方巾没有装满.

I have used an if function however when writing the data it will not write anything for the variables within the IF statements e.g. PQL, 1IW, 2IW and PPL, it just remains blanks for each NameA. As the varibales are not filled.

整个模块

Sub Dictionary()

    Dim dict As Dictionary

    Set dict = ReadData()

    Call WriteDict(dict)

End Sub


Function ReadData()

    Dim dict As New Dictionary
    Dim DataWs As Worksheet: Set DataWs = ThisWorkbook.Sheets("DATA")
    Dim PoolOfWeekWs As Worksheet: Set PoolOfWeekWs = ThisWorkbook.Sheets("Pool of the week")
    
    Dim LastrowData As Long: LastrowData = DataWs.range("A" & Rows.Count).End(xlUp).row
    Dim LastColData As Long: LastColData = DataWs.Cells(1 & DataWs.Columns.Count).End(xlToLeft).Column
    
    Dim LastColDataString As String: LastColDataString = Split(Cells(1, LastColData).Address, "$")(1)
    
    Dim DataRange As range: Set DataRange = DataWs.range("A1:" & LastColDataString & LastrowData)
    Dim DataArr As Variant: DataArr = DataWs.range("A1:AO" & LastrowData)
    
    Dim range As range: Set range = DataWs.range("A1").CurrentRegion
    
    Dim i As Long
    Dim CandidateProcessID As String, CandidateName As String, FirstName As String, ProcessStatus As String, FirstITWDate As String, PQLDate As String, XP As String, oCandidate As ClsCandidate
    
    For i = 2 To range.Rows.Count
        If range.Cells(i, 35).Value <> "NOK" Then
    
            CandidateProcessID = range.Cells(i, 10).Value
            FirstName = range.Cells(i, 17).Value
            ProcessStatus = range.Cells(i, 9).Value
            CandidateName = range.Cells(i, 16).Value
            Nationality = range.Cells(i, 39).Value
            XP = range.Cells(i, 24).Value
            SalaryExpectation = range.Cells(i, 48).Value
            Email = range.Cells(i, 18).Value
            PhoneNum = range.Cells(i, 19).Value
            ROName = range.Cells(i, 46).Value
            YearsExp = range.Cells(i, 24).Value
            Sector = range.Cells(i, 48).Value
            ProcessType = range.Cells(i, 35).Value
            InterviewScore = range.Cells(i, 37).Value
            DetailedSkills = range.Cells(i, 28).Value
            SkillsSummary = range.Cells(i, 29).Value
            NameofCM = range.Cells(i, 44).Value
            ROName = range.Cells(i, 46).Value
             
            If range.Cells(i, 13) = "Prequalification" Then PQLDate = range.Cells(i, 11).Value
    
            If range.Cells(i, 13) = "Candidate Interview 1" Then
                    FirstITWDate = range.Cells(i, 11).Value
                    BM1ITW = range.Cells(i, 44).Value
                    ProposedSalary = range.Cells(i, 48).Value
            End If
            If range.Cells(i, 13) = "Candidate Interview 2+" Then
                    SecondITWDate = range.Cells(i, 11).Value
                    ProposedSalary = range.Cells(i, 48).Value
            End If
            If range.Cells(i, 13) = "Candidate Interview 2*" Then
                    PPLDate = range.Cells(i, 11).Value
                    ProposedSalary = range.Cells(i, 48).Value
            End If
    
            If range.Cells(i, 13) = "Signature Interview" Then
                    SignatureInterview = range.Cells(i, 11).Value
            End If
        
        If dict.Exists(CandidateProcessID) = True Then
            Set oCandidate = dict(CandidateProcessID)
                oCandidate.YearsExp = oCandidate.YearsExp
                oCandidate.NameofCM = oCandidate.NameofCM
                oCandidate.ROName = oCandidate.ROName
                oCandidate.DetailedSkills = oCandidate.DetailedSkills
                oCandidate.Nationality = oCandidate.Nationality
                oCandidate.CandidateName = oCandidate.CandidateName
                oCandidate.FirstName = oCandidate.FirstName
                oCandidate.PQLDate = oCandidate.PQLDate
        Else
            Set oCandidate = New ClsCandidate
            dict.Add CandidateProcessID, oCandidate
                oCandidate.YearsExp = oCandidate.YearsExp + YearsExp
                oCandidate.NameofCM = oCandidate.NameofCM + NameofCM
                oCandidate.ROName = oCandidate.ROName + ROName
                oCandidate.DetailedSkills = oCandidate.DetailedSkills + DetailedSkills
                oCandidate.Nationality = oCandidate.Nationality + Nationality
                oCandidate.CandidateName = oCandidate.CandidateName + CandidateName
                oCandidate.FirstName = oCandidate.FirstName + FirstName
                oCandidate.PQLDate = oCandidate.PQLDate + oCandidate.PQLDate
        End If
        
        End If
    Next i
    
    Set ReadData = dict
End Function

Sub WriteDict(dict As Dictionary)

    Dim key As Variant, oCandidate As ClsCandidate, row As Long
    Set rangeoutput = Sheets("Sheet1").range("A2").CurrentRegion
    row = 2
    
    For Each key In dict
        Set oCandidate = dict(key)
        'Debug.Print key, oCandidate.CandidateName, oCandidate.PQLDate, oCandidate.YearsExp, oCandidate.NameofCM, oCandidate.ROName, oCandidate.DetailedSkills, oCandidate.Nationality
        rangeoutput.Cells(row, 1).Value = oCandidate.Nationality
        rangeoutput.Cells(row, 2).Value = oCandidate.DetailedSkills
        rangeoutput.Cells(row, 3).Value = oCandidate.ROName
        rangeoutput.Cells(row, 4).Value = oCandidate.NameofCM
        rangeoutput.Cells(row, 5).Value = oCandidate.YearsExp
        rangeoutput.Cells(row, 6).Value = oCandidate.PQLDate
        rangeoutput.Cells(row, 7).Value = oCandidate.CandidateName
        rangeoutput.Cells(row, 8).Value = oCandidate.FirstName
        row = row + 1
    Next key

End Sub

阶级模样

Option Explicit

Public CandidateProcessID As String
Public Status As String
Public PQLDate As Date
Public ProcessType As String
Public InterviewScore As String
Public CandidateName As String
Public FirstName As String
Public NameofCM As String
Public BM1ITW As String
Public FirstITWDate As Date
Public DetailedSkills As String
Public SkillsSummary As String
Public XP As Long
Public NP As String
Public Nationality As String
Public SalaryExpectation As Long
Public ProposedSalary As Long
Public SecondITWDate As Date
Public PPLDate As Date
Public Email As String
Public PhoneNum As Long
Public ROName As String
Public BusinessUnit As String
Public RecruiterTregram As String
Public Country As String
Public YearsExp As Long
Public Sector As String
Public SignatureInterview As Date

推荐答案

此处:

If dict.Exists(CandidateProcessID) = True Then
   Set oCandidate = dict(CandidateProcessID)  '<<< existing instance
   oCandidate.YearsExp = oCandidate.YearsExp  
   oCandidate.NameofCM = oCandidate.NameofCM  

您只是将属性值设置为自己的值?似乎应该在此处将其他值添加到现有属性值中(需要比仅 + 更好的过程...)，而不是在 Else 块中

You just set the property values to their own values? Seems like this is where you should be adding the other values to the existing property values (needs a better process than just + though...), not in the Else block

这篇关于EXCEL VBA:类模块变量值的位置取决于if函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！