CODEWITHSUNDEEP
arraysvbaexcelcharts
Sam G
Sam G

Reputation: 161

VBA Creating an excel chart using values in an array

I have some data in a two dimensional array and I need to create a chart using excel charts. However, it seems that the charts setsourcedata accepts only range. This means that I have to store the array in a range on a spreadsheet somewhere and then pass the range to the chart as an input. So i created a temp sheet to store the data in a specific range and then plan to pass it to the create chart call. Here is a type that I created

Private Type CItem

CName As Variant
CWeek(13) As Variant
CDisabled(13) As Variant
CEmpty(13) As Variant
CEnabled(13) As Variant

End Type

Dim CItemArray(100) As CItem
cnt1 = 0

//.. do something

ActiveWorkbook.Sheets.Add(After:=Sheets("MainSheet")).Name = "WK_Report"

Worksheets("WK_Report").Range("C3:C15").value = CItemArray(cnt1).CWeek()
Worksheets("WK_Report").Range("D3:D15").value = CItemArray(cnt1).CDisabled()
Worksheets("WK_Report").Range("E3:E15").value = CItemArray(cnt1).CEnabled()
Worksheets("WK_Report").Range("F3:F15").value = CItemArray(cnt1).CEmpty()

My goal is to assign the entire array of CWeek for the first CItem to the range of C3:C15 respectively. However, the syntax I used above assigns the first element of Cweek, CDisabled, CEnabled, CEmpty to the entire range. I tried without the parentheses after CWeek and others, but the result is same.

How can I assign the array values to the range? I know that the values are in the array because I did a debug.print after assigning to verify.

Upvotes: 1

Views: 1648

Answers (2)

Tim Williams
Tim Williams

Reputation: 166531

You can use Application.Transpose: here's a simplified example

Option Explicit

Private Type CItem
    CWeek(13) As Variant
End Type

Sub Tester()

    Dim itm As CItem, x As Long

    For x = 1 To 14
        itm.CWeek(x - 1) = x
    Next x

    ActiveSheet.Range("C3:C15").Value = itm.CWeek '>> all "1"

    ActiveSheet.Range("D3:D15").Value = Application.Transpose(itm.CWeek) '>> 1 to 13

End Sub

Upvotes: 2

user4039065
user4039065

Reputation:

You've left out how you are populating the user defined type arrays but you should move to a 2-D variant array.

Private Type CItem
    CName As Variant
    CStuff(1 to 13, 1 to 4) As Variant
End Type

Dim CItemArray(100) As CItem, cnt1 as long

//.. populate 100 CItemArray.CStuff with 13 rows by 4 columns each

ActiveWorkbook.Sheets.Add(After:=Sheets("MainSheet")).Name = "WK_Report"

cnt1 = 1
with Worksheets("WK_Report").Range("C3")
    .resize(ubound(CItemArray(cnt1).CStuff, 1), ubound(CItemArray(cnt1).CStuff, 2)) = CItemArray(cnt1).CStuff
end with

From my point of view, the user defined type is only getting in the way but once you get it working it may be a 'set and forget' type of thing. I used a 1-based array so you didn't have to add 1 to the ubounds.

Upvotes: 1

Related Questions