VBA Creating an excel chart using values in an array

Question

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.

Try Worksheets("WK_Report").Range("C3:C15").value = Application.Transpose(CItemArray(cnt1).CWeek) — Tim Williams
– Tim Williams, Commented May 11, 2017 at 0:10
I tried this.. This reduces the output to a single item of the cweek — Sam G
– Sam G, Commented May 11, 2017 at 0:16

Tim Williams · Accepted Answer · 2017-05-11 00:27:35Z

2

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

answered May 11, 2017 at 0:27

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sam G Over a year ago

Thanks.. I tried this.. it did not work Worksheets("WK_Report").Range("C3:C15").value = Application.Transpose(CItemArray(cnt1).CWeek) The only difference is I am trying to refer to a particular CItemarray

user4039065 Over a year ago

'it did not work' is neither a valid error code or an adequate description of the problem.

Sam G Over a year ago

@jeeped unfortunately, enter = submit for the comment, I was trying to get to the new line.. I tried Alt + Enter as well. it did the same. I gave up.I just edited my comment.. I will learn how to enter on new line in a comment. Cool?

Tim Williams Over a year ago

Works fine for me, so something else is going on with your code... BTW you cannot use newlines in comments.

Sam G Over a year ago

Yep! I ran your code and it works. I am probably doing something wrong here.. given that I am trying to access a specific element of the citem array

|

user4039065 · Accepted Answer · 2017-05-11 00:09:51Z

1

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.

answered May 11, 2017 at 0:09

user4039065

3 Comments

Sam G Over a year ago

"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." Can you please clarify this?

user4039065 Over a year ago

1. You need a 2-D array to transfer values directly to the worksheet in row by columns. You used 4 1-D arrays. 2. It might be easier to populate the user defined structure but it isn't easier to get it back to the worksheet and you declined to offer how you were populating it in the first place so... I dunno. 3. If you have a zero-based array and use its ubound to resize the destination them you have to add 1 to the ubound cuz there is no range of cells on the worksheet that is zero rows by zero columns.

Sam G Over a year ago

Super! Thanks for your help and suggestion on moving to the 2 D array.

Collectives™ on Stack Overflow

VBA Creating an excel chart using values in an array

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related