Indirect Assignment to a User Defined Type Using Variant Arrays RRS feed

  • Question

  • I am pretty sure this can't be done but I thought I would ask.

    I have a user defined type

    Public Type cpUT
      startYr As Integer
      analysisYrs As Integer
      milesYr As Long
    End Type

    Dim cp1 As cpUT   ' Define instance
    Dim cpArr As Variant  'Define variant to hold user type members

    cpArr = Array(cp1.startYr, cp1.analysisYrs, cp1.milesYr)

    Now I can write a simple loop to save the values to a sheet or to a form.  I don't need to have dozens of assignment statements.

    Is there a trick to save sheet cell values to the user type members using a simple loop.  VBA does not support pointers.  I am currently using dozens of simple assignment statements.

    cp1.startYr = ws.Cells(2,2) 
    Saturday, June 23, 2012 7:19 PM

All replies

  • I am not sure what you are doing with the

    Dim cpArr As Variant  'Define variant to hold user type members
    cpArr = Array(cp1.startYr, cp1.analysisYrs, cp1.milesYr)

    For me that is unnecessary.

    All I had to do was use something like

    ThisWorkbook.Sheets(1).Cells(2,2) = cp1.startYrs
    to write to a worksheet cell


    cp1.startYrs = ThisWorkbook.Sheets(1).Cells(2,2)

    to read from worksheet cell

    • Edited by ieee488 Saturday, June 23, 2012 10:18 PM
    Saturday, June 23, 2012 10:17 PM
  • The reason I do that savings members to the sheet is simpler

    For i = LBound(cp1) to UBound(cp1)

      ws.Cells(i + 1, 2) = cp1(i)

    Next i

    Basically 3 statements versus in my 1 case over 40 statements.  Less code less headaches.

    I'll just use my current case statement to load the user defined type.  

    Sunday, June 24, 2012 12:06 AM
  • Perhaps I am not understanding what you are trying to do.

    I would create array of the UDT and iterate through them.

    Dim arrayUdt(10) as cpUT Dim i as Integer

    'intialize arrayUdt


    For i = 0 to UBound(arrayUdt) ThisWorkbook.Sheets(1).Cells(i * 3 + 1, 2) = arrayUdt(i).analysisYrs ThisWorkbook.Sheets(1).Cells(i * 3 + 2, 2) = arrayUdt(i).milesYr ThisWorkbook.Sheets(1).Cells(i * 3 + 3, 2) = arrayUdt(i).startYr Next i

    which prints the values in a column


    For i = 0 to UBound(arrayUdt)
        ThisWorkbook.Sheets(1).Cells(i + 1, 1) = arrayUdt(i).analysisYrs
        ThisWorkbook.Sheets(1).Cells(i + 1, 2) = arrayUdt(i).milesYr
        ThisWorkbook.Sheets(1).Cells(i + 1, 3) = arrayUdt(i).startYr
    Next i

    to print them out in rows

    • Edited by ieee488 Sunday, June 24, 2012 1:27 AM
    Sunday, June 24, 2012 1:24 AM
  • I don't need an array of UDTs I just need 1 and it has some 40+ variables I want output.  I just find it easier to maintain with a simple 3 line loop rather than 40+ statements.  Anyway the point of the post was how to load a UDT from spreadsheet data using a similar simple loop.  I don't see a way without pointers so I will need 40+ statements to load it unless there is a trick I am not aware of.
    Sunday, June 24, 2012 12:07 PM
  • 1.) I simply don't know why you bother having a UDT when you are doing what you are doing.

    2.) You declare a UDT with 3 members, yet you claim that it has 40+ variables.


    If your UDT has 40 members, then show it as such instead of just showing 3. Your code should show what you have and are doing.

    • Edited by ieee488 Sunday, June 24, 2012 12:23 PM
    Sunday, June 24, 2012 12:20 PM