Solved: Transfer data from Array declared with Userdefined Type variable to Excel

[BenD]

I have declared a Userdefined Type array:


Option Explicit
Option Private Module

Private Const iMAX_CONTROL_PROPERTIES As Integer = 500

Private Type FORM_TRANSLATION_PARAMS
sKeyID As String
sFormName As String
sControlType As String
sControlName As String
sControlItem As String
sTransText As String
End Type

Public aTransTable(1 To iMAX_CONTROL_PROPERTIES) As FORM_TRANSLATION_PARAMS


This array is used in a subroutine that loops through all the UserForms in a project and captures some of the properties of some of the controls on these forms.

Once all UserForms have been processed, I want to store the contents of the array in an Excel range.
I know how to do this with an ordinary array (meaning non-Userdefined type as variable). However, the standard way to do this, does of course not apply for the Userdefined Type I use for my array. This because a single item of the array, e.g. aTransTable(1) does not store one single value but 6 values:


aTransTable(1).sKeyID
aTransTable(1).sFormName
aTransTable(1).sControlType
aTransTable(1).sControlName
aTransTable(1).sControlItem
aTransTable(1).sTransText

Now of course I could do any of the following things to resolve my problem:

Rewrite my code and not use a Userdefined Type for my array, but use a standard 2D variable for the array;

or,
After the Userdefined Type array has been filled with data, transfer the contents to a new standard 2D array, which I can easily write to an Excel range.Before I do that, I just wanted to run this by you to see if I might be overlooking something, and I actually can transfer the data from my original (Userdefined Type) array directly to an Excel range.

Any ideas?

[Bob Phillips]

No, I would just use a 2D array?

[BenD]

That is already what I thought, so thanks for confirming xld!

[BenD]

That is already what I thought, so thanks for confirming xld!