In Excel: Copy data from UDT (User Defined Type) Array to Sheet Range

[KurtVba]

Hi MrExcel Experts,

I created an array of User-defined Data Type (UDT) and now want to pass it to an Excel Sheet range. How can I do it and do it elegantly?

With my code (shorten for ease of reading), I get the compiler error message: "Only user-defined types defined in public object modules can be coerced to and from a variant or passed to late bound functions".

Note: I created a Public data type and array at the top of a normal module. Also, I did not create a function.

Thank you for any help or suggests.
Kurt

'Create custom array for user input. At top of normal module.
Public Type InputUser
     SeqNr As Integer
     PlanCat As String * 15
     Dept As String
 End Type

'Declare Variables Arrays
Public aIU() As InputUser

Private Sub DefineUserInputs()

Dim Sht As Object      'Input sheet with data & Output sheet to range
Dim Rng As Range       'Find range with data
Dim Rq As Long          'Row count
Dim Cq As Long          'Column count
Dim x As Long            ' loop counter

'Find sheet
Set Sht = Worksheets("WagesUserInputDB")

'Find range of input values
Set Rng = Sht.Range("A1").CurrentRegion

'Find quantity of rows and cols of range to resize array
Rq = Rng.Rows.Count
Cq = Rng.Columns.Count
ReDim aIU(Rq)

'Copy values from user input range TO user-defined ARRAY
For x = 1 To Rq
    aIU(x).SeqNr = Rng.Cells(x, 1)
    aIU(x).PlanCat = Rng.Cells(x, 2)
    aIU(x).Dept = Rng.Cells(x, 3)
Next x

'Copy values FROM user-defined ARRAY to user input range
Set Rng = Worksheets("NewSht").Range("a1")  'Set variable to new sheet
Set Rng = Rng.Resize(UBound(aIU), 1)       'Resize Range = Array size
Rng = aIU                                   ' Write array to sheet range
End Sub

 

[dk]

Hi

I don't think you'll be able to assign your own UDT like that. Having said that I will happily buy a beer for anyone who proves me wrong

One way you might be able to handle it is to use a conversion function to convert an array of InputUser types into a regular array that can then be assigned to a range. Here is a possible example (but I have not tested this).

Function ConvertIUToArray(Inp() As InputUser) As Variant

Dim lIndex As Long
Dim vRetval As Variant

ReDim vRetval(LBound(Inp) To UBound(Inp), 0 To 2)


For lIndex = LBound(Inp) To UBound(Inp)
    
    vRetval(lIndex, 0) = Inp(lIndex).Dept
    vRetval(lIndex, 1) = Inp(lIndex).PlanCat
    vRetval(lIndex, 2) = Inp(lIndex).SeqNr


Next lIndex

ConvertIUToArray = vRetVal

End Function

HTH
DK

 

[KurtVba]

Hi dk,

Your function worked perfectly the first time. Not only was it elegant, but simply ingenious.

Thank you.
Kurt

 

[KurtVba]

Hi dk,

As an after thought:

Can you explain what the error message means: "Only user-defined types defined in public object modules can be coerced to and from a variant or passed to late bound functions".

I thought I defined the UDT in a public object module. Did I do something wrong?

Kurt

 

[dk]

Hi Kurt

Glad the function worked for you.

As to the meaning of the error message. An object module means a class module such as one from the Insert, Class Module menu item or a worksheet, workbook or userform code module. A standard module is not an object module. However, if you tried to declare your UDT as public within a class module then you'll get an error message "Cannot define a Public user-defined type within an object module" so basically you can't win within VBA.

I believe that <a href="http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q184/8/98.asp&NoWebContent=1">this</a> article (which is talking about the stand alone version of Visual Basic 6) is related to your problem. I just dusted off my copy of VB6 and was able to declare your UDT within a class module but only if I was creating an ActiveX component (i.e. something that can be created within VBA with CreateObject or New) and that is where the link to the MS article comes in. If I tried to declare it within a class module in a normal VB executable I got the same error message.

I hope that helps a bit Maybe someone else will be able to offer a clearer explanation.

DK

 

[KurtVba]

Hi dk,

You must be good at what you do because even I understood your explanation which was to-the-point and clear.

Thank you for your extra, second effort!
Kurt