(Solved) – Custom Type to store and output data

(solved)-–-custom-type-to-store-and-output-data

I have been working on a project for work where I need to determine a number of values based on the concentration and component inputs. I have used named ranges for the source data. I am looking for advice on how best to call this public function, whether it should be moved to its own module, and how best to output this data. I was thinking of using a nested for loop to print the data stored by the public function (should it be public?) to an output page for each characteristic of the components.

Is this a good approach, will it work, is there a better way?

Public Static Function StoreData()
Dim i As Integer
i = 1
Dim j As Integer
j = 1
Dim k As Integer
Dim Inputs() As Components
Dim Source_Row As Variant
Dim Input_Conc As Range
    Set Input_Conc = Worksheets("Input").Range("D3:D52")

Do ' Counts number of Components
    If IsEmpty(Input_Conc(i)) = False Then
        j = j   1
    End If
    i = i   1
Loop Until IsEmpty(Input_Conc(i)) = True

ReDim Inputs(j)
k = 3
Do
    For i = LBound(Inputs) To UBound(Inputs)
        With Inputs(i)
            .CAS = Worksheets("Input").Cells(k, 3)
            .Concentration = Worksheets("Input").Cells(k, 4)
            .Name = Worksheets("Input").Cells(k, 2)

            .Source_Row = Application.Match(Inputs(i).CAS, Worksheets("Component Properties").Range("A3:A1000"), 0)

            .Name = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 2)
            .MW = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 3)
            .VP50_Kpa = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 4)
            .Tki = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 5)
            .L_Den = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 6)
            .mL_mol = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 7)
            .BP = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 8)
            .FP = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 9)
            .Refrigerated = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 10)
            .Pyrophoric = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 11)
            .GUP = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 12)
            .FG = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 13)
            .LG = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 14)
            .FL = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 15)
            .OG = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 16)
            .SC = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 17)
            .EI = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 18)
            .RS = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 19)
            .SS = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 20)
            .GCM = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 21)
            .Carcinogen = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 22)
            .RH = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 23)
            .STORE = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 24)
            .STOSE = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 25)
            .EHA = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 26)
            .EHC = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 27)
            .ODS = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 28)
            .Toxicity = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 29)
            .AMCRN = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 30)
            .Tci = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 31)
            .LC50 = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 32)
            .S1S = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 33)
            .S2S = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 34)
            .Comp_F = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 35)
            .SL_Hi = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 36)
            .SL_Lo = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 37)
            .Reactive = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 38)
            .BTU = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 39)
            .LEL = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 40)
            .UEL = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 41)
            .MOC = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 42)
            .CF = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 43)
            .KK = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 44)
            .Prop_65 = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 45)
            .C_no = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 46)
            .H_no = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 47)
            .O_no = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 48)
            .S_no = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 49)
            .Si_no = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 50)
            .B_no = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 51)
            .N_no = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 52)
            .P_no = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 53)
            .F_no = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 54)
            .Cl_no = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 55)
            .Br_no = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 56)
            .I_no = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 57)
            .Halogen_no = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 58)
            .Silane = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 59)
            .Amine = Worksheets("Component Properties").Cells(Inputs(i).Source_Row, 60)
        End With
        j = j   1
    Next i
Loop Until IsEmpty(Inputs(i).CAS) = True

End Function

Leave a Reply

Your email address will not be published. Required fields are marked *