Reading Array of UserDefined Type from Cells in Excel

[AustinMan]

This is what I got so far.

Option Explicit

Type ProgramData
pContract As Long
'pDocumentNumber As Long
'pID As Long
'pName As String
'pCOBJ As Integer
'pCalDRS As Single
'pDRSMatch As Single
'pNHICcert As Single
'pMACMatch As Single
End Type



Sub WriteShit(iTitle As Integer, iDetail As Integer, iRow As Integer, icontract As Integer, irowcontract As Integer)

Dim ETVArr() As ProgramData
Dim i As Single
Dim j As Integer
Dim LengthRowDetail As Integer
Dim LengthDetail As Integer
Dim LengthTitle As Integer
Dim ncol As Integer
Dim nrow As Integer

LengthTitle = iTitle
LengthDetail = iDetail
LengthRowDetail = iRow
ncol = icontract
nrow = irowcontract

i = 0

ReDim ETVArr(LengthRowDetail) As ProgramData

For i = 1 To LengthRowDetail
ETVArr(i).pContract = Cells(nrow + 2, ncol)
nrow = nrow + 1
Next i
Debug.Print ETVArr(0).pContract


It errors on ReDim ETVArr(LengthRowDetail) -> Subscript out of range;

Also, ETVArr(i).pContract stays literally ETVArr(i).pContract on Tooltip, What the heck is going on??

I am trying to loop values from a Excel into this UDT.


What is going on?


Any insight would be great! Please respond.

Thanks!

 

[RobBroekhuis]

That should work just fine. What's the value of irow you're passing to the sub?

Rob

 

[AustinMan]

The value of irow is an integer value (a number).

Any other suggestions??


I am totally stumped!

 

[RobBroekhuis]

But what is the actual value of the integer?


Rob

 

[AustinMan]

The actual value is 10.

 

[AustinMan]

Is it possible for LengthRowDetail, a variable representing a number instead of a straight numeric literal (an actual number)?

It defeats the purpose of looping through.

 

[RobBroekhuis]

Just to see what would happen, I copied your code and pasted it into a module, and then executed from within the immediate window:

writeshit 1,1,10,10,10

It works just fine (at least, it doesn't error). There must be something else about what you're doing...


Rob