as part of some VBA for Excel that I'm writing I would like to print a 12 x 101 element array (dwt_percentiles) that is part of an item in a collection to a workbook so that I can make some graphs from the data. I have only started using classes in VBA in the past 24 hours and have got stuck on the following issue:
The line in the function where I am attempting to print the array throws the following error: "Runtime error 451: Property let procedure not defined and property get procedure did not return an object". The dwt_percentiles array is showing up fine with expected values when I add colltasks to the watches window, but I can't seem to access it from the code. I can access the other properties of the items of the collection fine, so it's probably an error in how I'm trying to access the array, but I can't find any similar code to what I'm attempting online (probably because I use the wrong terminology...).
The code I'm using is as follows:
Function plotECDF
Sub plotECDF(ByVal colltasks As Collection)
Dim item As Variant
Dim i As Integer, j As Integer, k As Integer
For i = 7 To 13
Sheet3.Range("A1").Value = i
Sheet3.Range("B1").Value = colltasks(i).Name
For j = 1 To 12
For k = 0 To 100
' error shown on next line - run time error 451
Sheet3.Cells(1 + j, 3 + k).Value = colltasks(i).dwt_percentiles(j, k)
Next k
Next j
Next
End Sub
Class module Task - tasks form the elements of a collection
Option Explicit
' Basic properties
Public Name As String
Public Hs As Double
Public Ws As Double
Public window As Double
' Array definitions
Public dwt_array As Variant
Public dwt_byMonth As Variant
Public dwt_percentiles As Variant
Code which creates dwt_percentiles() array and sticks it in a collection
Sub do_things()
Dim percentileByMonth() As Variant
Dim percentileSum As Double
Dim percentileArray() As Variant
Dim numYears as Integer, j as Integer, jj as Integer, k as Integer
Dim t as New Task
Dim colltasks as New Collection
percentileSum = 0
ReDim percentilesByMonth(1 To 12, 0 To 100)
For j = 1 To 12
For jj = 0 To 100
For k = 1 To numYears + 1
percentileSum = percentileSum + percentileArray(j, k, jj)
Next k
percentilesByMonth(j, jj) = percentileSum / (numYears + 1)
percentileSum = 0
Next jj
Next j
t.dwt_percentiles = percentilesByMonth
End sub
Many thanks.