CODEWITHSUNDEEP
arraysexcelvba
Navkanth
Navkanth

Reputation: 50

Using an array of objects created in a function in a different sub

I have employee details in a worksheet and I am loading all that data into several objects of employee class that I have created. Based on criteria that a user selects on a GUI tool, I would like to display the data using the class objects that I had initially created/loaded.

Function loadEmpData() As Employee()
    Dim e() As New Employee
    With dataSheet

        no_of_emp = .Range(.Range("a4"), .Range("a4").End(xlDown)).Count


'    no_of_emp = .Range(.Range("a3"), .Range("a3").End(xlDown)).Offset(1, 0).SpecialCells(xlCellTypeVisible).Count
'    .Range(.Range("a3"), .Range("a3").End(xlDown)).SpecialCells(xlCellTypeVisible).Copy clsTest.Range("a1")
    'MsgBox no_of_emp - 1



        no_of_emp = no_of_emp - 1
        ReDim e(no_of_emp) As New Employee

        xc = 4
        yc = 1
        zc = 0
        Do While zc <= no_of_emp

            e(zc).EmpID = .Cells(xc, 1).Value
            e(zc).Fname = .Cells(xc, 2).Value
            e(zc).Lname = .Cells(xc, 3).Value
            e(zc).Band = .Cells(xc, 6).Value
            e(zc).EmpTitle = .Cells(xc, 7).Value
            e(zc).SecondLR = .Cells(xc, 12).Value
            e(zc).LR = .Cells(xc, 13).Value

            On Error Resume Next
            e(zc).Acc_Know = .Cells(xc, 15).Value
            e(zc).Ref_Acc_Know = .Cells(xc, 16).Value
            e(zc).Analytic = .Cells(xc, 17).Value
            e(zc).Ref_Analytic = .Cells(xc, 18).Value
            e(zc).Sys_Know = .Cells(xc, 19).Value
            e(zc).Ref_Sys_Know = .Cells(xc, 20).Value
            e(zc).Ssigma = .Cells(xc, 21).Value
            e(zc).Ref_Ssigma = .Cells(xc, 22).Value
            e(zc).Quality_focus = .Cells(xc, 23).Value
            e(zc).Ref_Quality_focus = .Cells(xc, 24).Value
            e(zc).Risk_mgmt = .Cells(xc, 25).Value
            e(zc).Ref_Risk_mgmt = .Cells(xc, 26).Value
            e(zc).Transition_mgmt = .Cells(xc, 27).Value
            e(zc).Ref_Transition_mgmt = .Cells(xc, 28).Value
            e(zc).stakehold_orient = .Cells(xc, 29).Value
            e(zc).Ref_stakehold_orient = .Cells(xc, 30).Value
            e(zc).atten_details = .Cells(xc, 31).Value
            e(zc).Ref_atten_details = .Cells(xc, 32).Value
            e(zc).Accountability = .Cells(xc, 33).Value
            e(zc).Ref_Accountability = .Cells(xc, 34).Value
            e(zc).collab_team = .Cells(xc, 35).Value
            e(zc).Ref_collab_team = .Cells(xc, 36).Value
            e(zc).comm_skills = .Cells(xc, 37).Value
            e(zc).Ref_comm_skills = .Cells(xc, 38).Value
            e(zc).achieve_results = .Cells(xc, 39).Value
            e(zc).Ref_achieve_results = .Cells(xc, 40).Value
            e(zc).strategic_orient = .Cells(xc, 41).Value
            e(zc).Ref_strategic_orient = .Cells(xc, 42).Value
            e(zc).behaviour = .Cells(xc, 43).Value
            e(zc).Ref_behaviour = .Cells(xc, 44).Value
            e(zc).build_strong_org = .Cells(xc, 45).Value
            e(zc).Ref_build_strong_org = .Cells(xc, 46).Value

            e(zc).DFP = .Cells(xc, 49).Value
            On Error GoTo 0
'            clsTest.Cells(xc, 1).Value = e(zc).EmpID
'            clsTest.Cells(xc, 2).Value = e(zc).Fname
'            clsTest.Cells(xc, 3).Value = e(zc).Lname
'            clsTest.Cells(xc, 4).Value = e(zc).FunctionGroup
'            clsTest.Cells(xc, 5).Value = e(zc).ProcessGroup
'            clsTest.Cells(xc, 6).Value = e(zc).Band
            zc = zc + 1
            xc = xc + 1
        Loop

'        MsgBox e(12).EmpID
'        MsgBox e(12).Band
    End With
End Function

These objects that I have created, I would like to use them in a sub in the same project. Is it possible? I have searched a lot on the web and did not come anywhere near to a solution.

employee class:

Public EmpID As String
Public Fname As String
Public Lname As String
Public FunctionGroup As String
Public ProcessGroup As String
Public Band As String
Public EmpTitle As String
Public SecondLR As String
Public LR As String
Public DFP As Integer


Public Acc_Know As Integer
Public Analytic As Integer
Public Sys_Know As Integer
Public Ssigma As Integer
Public Quality_focus As Integer
Public Risk_mgmt As Integer
Public Transition_mgmt As Integer
Public stakehold_orient As Integer
Public atten_details As Integer
Public Accountability As Integer
Public collab_team As Integer
Public comm_skills As Integer
Public achieve_results As Integer
Public strategic_orient As Integer
Public behaviour As Integer
Public build_strong_org As Integer

Public Ref_Acc_Know As Integer
Public Ref_Analytic As Integer
Public Ref_Sys_Know As Integer
Public Ref_Ssigma As Integer
Public Ref_Quality_focus As Integer
Public Ref_Risk_mgmt As Integer
Public Ref_Transition_mgmt As Integer
Public Ref_stakehold_orient As Integer
Public Ref_atten_details As Integer
Public Ref_Accountability As Integer
Public Ref_collab_team As Integer
Public Ref_comm_skills As Integer
Public Ref_achieve_results As Integer
Public Ref_strategic_orient As Integer
Public Ref_behaviour As Integer
Public Ref_build_strong_org As Integer

Upvotes: 1

Views: 21

Answers (1)

user4039065
user4039065

Reputation:

I don't have access to your Employee class module but you need the function to return the new array.

Stay away from public vars; passing the array of values back from the function is the better method.

Function loadEmpData() As Employee()
    Dim e() As New Employee

    '... lots of code here

    loadEmpData = e
end function

Then call the loading function in a sub like this.

sub WorkWithEmployees()
    Dim a as long, emps() As New Employee
    emps = loadEmpData()
    for a=lbound(emps, 1) to ubound(emps, 1)
        debug.print emps(a).EmpID
    next a
end sub

Upvotes: 2

Related Questions