Redim Array Inner Bound?

[osx99]

I have a grid in excel which I want to write to based on 20 different possible filtered variations on a set of data contained in an array. This grid will be used to populate excel charts

I've set up an array of arrays using

Private Type OsArray
    write_array() As Variant 
End Type 

Dim chart_data(1 To 20) As OsArray

then redimension the array ready for loading data using

                        On Error Resume Next
                        chartdata_bound = UBound(chart_data(combo_count).write_array, 2) + 1
                        If Err.Number = 9 Then 'array is uninitialized
                        ReDim Preserve chart_data(combo_count).write_array(1 To 10, 1 To 1)
                        Else
                        ReDim Preserve chart_data(combo_count).write_array(1 To 10, 1 To chartdata_bound)
                        End If
                        On Error GoTo 0

This is a workaround at the moment as I would prefer to redim the inner bound rather than the outer.

The perfect set up would be to have the data stored as 'chart_data(combo_count).write_array(rows, columns)'

but due to limitations of Redim in VBA I'm forced to use 'chart_data(combo_count).write_array(columns, rows)'

Ideally I would like to perform this function in the fastest most efficient way possible

'ReDim Preserve chart_data(combo_count).write_array(1 To chartdata_bound, 1 To 10)

this would allow the data to be in the correct layout for writing back to excel using a simple

.range("whatever").value = chart_data(combo_count)

Any ideas on how to achieve this without looping if possible are greatly appreciated?

 

[osx99]

I managed to solve the above problem having found this thread

http://www.tek-tips.com/viewthread.cfm?qid=1555032

so I now have

                        On Error Resume Next
                        chartdata_bound = UBound(chart_data(combo_count).write_array, 1) + 1
                        If Err.Number = 9 Then 'array is uninitialized
                        ReDim Preserve chart_data(combo_count).write_array(1 To 1)
                        Else
                        ReDim Preserve chart_data(combo_count).write_array(1 To chartdata_bound)
                        End If
                        On Error GoTo 0

tempchart_data(1) = Fetch_Data(Fetch_Datacount, 14)
''
tempchart_data(10) = Fetch_Data(Fetch_Datacount, 50)

chart_data(combo_count).write_array(UBound(chart_data(combo_count).write_array, 1)) = tempchart_data()

I'm now experinecing problems writing the final array back to excel

using a single row is fine, eg thi sworks ok
.Range("b1000:K1000").Value = chart_data(1).write_array(1)

however, when I try
.Range("b1000:K1025").Value = chart_data(1)

I get the following compile error

"Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions"

I've tried moving the Private Type OsArray to a new MODULE and changing to Public Type OsArray without success

Can anyone help? Thanks Os

 

[jges]

Is the module itself public?

 

[osx99]

How do I make the module public?

The code above is curently in the sheet1 object.

Sorry but...

Private Type Thick
    need_help() As Variant
End Type 

Dim Brain as Thick

 

[jges]

At the top of the module you can put a line
Option Private Module
or
Option Public Module
I don't remember what the default is if you leave it out.

Perhaps it is public by default and your problem lies elsewhere.

 

[osx99]

I can only assume MODULES are public by default as Option Public Module creates an error whereas Option Private Module is ok in Excel

 

[osx99]

Ok I think I might know what the problem is. I've made a small test proc to test

this is in sheet1 object

Option Explicit

Private Type OsArray
write_array() As Variant
End Type


Private Sub test()
Dim tempchart_data(1 To 5) As Variant
Dim chart_data(1 To 5) As OsArray
Dim a As Integer
Dim chartdata_bound As Long
Dim test() As Variant

For a = 1 To 5

On Error Resume Next
chartdata_bound = UBound(chart_data(a).write_array, 1) + 1
If Err.Number = 9 Then 'array is uninitialized
    ReDim Preserve chart_data(a).write_array(1 To 1)
    Else
    ReDim Preserve chart_data(a).write_array(1 To chartdata_bound)
End If
On Error GoTo 0


tempchart_data(1) = Range("a1").Offset(a - 1, 0).Value
tempchart_data(2) = Range("b1").Offset(a - 1, 0).Value
tempchart_data(3) = Range("c1").Offset(a - 1, 0).Value
tempchart_data(4) = Range("d1").Offset(a - 1, 0).Value
tempchart_data(5) = Range("e1").Offset(a - 1, 0).Value
chart_data(a).write_array(UBound(chart_data(a).write_array, 1)) = tempchart_data()
Next

test = Range("A1:E5").Value

'this works!!
For a = 1 To 5
Range("A10:E10").Offset(a - 1, 0).Value = chart_data(a).write_array(1)
Next

'this gives error!! "Only user-defined types defined in public object modules
'can be coerced to or from a variant or passed to late-bound functions"
'Range("A16:E20").Value = chart_data(1)
End Sub

the array 'test' is structured
test
-test(1)
--test(1,1)
--test(1,2)
.
.
--test(1,5)
-test(2)
etc


whereas array 'chart_data' is structured
chart_data
-chart_data(1)
--write_array
---write_array(1)
----write_array(1,1)
----write_array(1,2)
etc

so there's an extra level. If I try changing Dim chart_data(1 To 5) As OsArray to Dim chart_data(1 To 5) As Variant it throws a runtime 424 error.

I'm a little stumped now.

 

[osx99]

This is actually more representative of what I'm attempting to code. I just can't get this line to work

Range("A16:E20").Value = chart_data(1)

Private Sub test()
Dim tempchart_data(1 To 5) As Variant
Dim chart_data(1 To 5) As OsArray
Dim a As Integer, b As Integer
Dim chartdata_bound As Long
Dim test() As Variant

For a = 1 To 5
For b = 1 To 5
On Error Resume Next
chartdata_bound = UBound(chart_data(a).write_array, 1) + 1
If Err.Number = 9 Then 'array is uninitialized
    ReDim Preserve chart_data(a).write_array(1 To 1)
    Else
    ReDim Preserve chart_data(a).write_array(1 To chartdata_bound)
End If
On Error GoTo 0


tempchart_data(1) = Range("a1").Offset(b - 1, 0).Value
tempchart_data(2) = Range("b1").Offset(b - 1, 0).Value
tempchart_data(3) = Range("c1").Offset(b - 1, 0).Value
tempchart_data(4) = Range("d1").Offset(b - 1, 0).Value
tempchart_data(5) = Range("e1").Offset(b - 1, 0).Value
chart_data(a).write_array(UBound(chart_data(a).write_array, 1)) = tempchart_data()
Next
Next

test = Range("A1:E5").Value

'this works!!
For a = 1 To 5
Range("A10:E10").Offset(a - 1, 0).Value = chart_data(a).write_array(1)
Next

'this gives error!! "Only user-defined types defined in public object modules
'can be coerced to or from a variant or passed to late-bound functions"
Range("A16:E20").Value = chart_data(1)
End Sub