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

Question

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

user4039065 · Accepted Answer · 2017-06-10 14:18:18Z

2

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

answered Jun 10, 2017 at 14:18

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

Navkanth Over a year ago

I had considered your solution, but if I need the objects in multiple procedures, wouldn't the whole loading process happen again and delay the code substantially?Would it better to do away with class objects and just use the find method and offset as required to the necessary fields in the master data sheet and then display?

user4039065 Over a year ago

If you are using the same array in multiple subs then you can pass the array into the other subs as a parameter. sub otherSub(e() as employee) called from WorkWithEmployees above as otherSub emps.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related