Don’t count on it. All I wanted to do was get the cell address assigned to a data point in a chart so I could change it’s color based on the data. Seems like a basic thing to do right? No amount of googling could solve that one. Changing the color was easy. Reading the data was impossible as far as I could tell.
So I figured the data I wanted was in each data point on the chart anyway so I didn’t need to get the cell address. I could just read it off the chart. How hard could that be? Surely they’d let you read data points easily. I mean it’s bloody obvious you’d sometimes want to actions based on chart conditions, right? Right?
WRONG MOTHER FUCKER.
Three rage filled hours later I finally came up with this:
Dim mavars As Variant
mavars = Sheets(“Time Available”).ChartObjects(“Clock” + CStr(j)).Chart.SeriesCollection(1).Values
I’ll be honest. I don’t think you’re really supposed to be able to read chart values. No amount of googling could even find discussion of it in which functional code was produced. The help documentation as patchy, vague, and horribly organized.
The full code [spoiler](posted because I need to brag dang it, and incase someone smarter then me can give me tips to improve it I could learn from)
Sub cleanyerclocks()
For j = 1 To 14 Step 1
For i = 1 To 12 Step 1
Dim mavars As Variant
mavars = Sheets("Time Available").ChartObjects("Clock" + CStr(j)).Chart.SeriesCollection(1).Values
Select Case mavars(i)
Case Is = 1
Sheets("Time Available").ChartObjects("Clock" + CStr(j)).Chart.SeriesCollection(1).Points(i).Interior.ColorIndex = 43
Case Is = 1.01
Sheets("Time Available").ChartObjects("Clock" + CStr(j)).Chart.SeriesCollection(1).Points(i).Interior.ColorIndex = 29
Case Is = 1.02
Sheets("Time Available").ChartObjects("Clock" + CStr(j)).Chart.SeriesCollection(1).Points(i).Interior.ColorIndex = 32
Case Is = 1.03
Sheets("Time Available").ChartObjects("Clock" + CStr(j)).Chart.SeriesCollection(1).Points(i).Interior.ColorIndex = 46
End Select
Next i
Next j
End Sub
What it does is:
I have two spread sheets. A visable one with my weekly schedule and a hidden one. The hidden one has mirror cells of the visable one. Each one has nested if statements in that says ( for example the one from C23) “=IF(‘Time Available’!C23=“Available”,1,IF(‘Time Available’!C23=“In Class”,1.01,IF(‘Time Available’!C23=“Drive Time”,1.02,1.03)))”
What this does is make it so I can make a chart for each block of 12 hours. The chart will look like a clock since it’ll have 12 sections that will appear to be the same size. The slight difference in number is invisible to the eye, but my macro can see it and take action accordingly. Using a 1-12 filled range for the legend serious gives me the clock numbers. I put the clocks on the visible sheet, with a slight rotation so 12 is on top, like a clock. 12 is kinky like that.
Then I assigned the macro to one of the clocks.
Then I can fill in my weekly schedule, click the trigger clock and it automatically colors in the clocks based on what I’m doing at each time.
[/spoiler]
I had to give my self a crash course in VB to program that. Did it in 6 hours starting with no knowledge of VB at all. Mot of that six hours was spent trying to figure out how to read the bloody chart data point data.
I made it because I wanted something cool to whizbang them at a job interview yesterday.
*f-bomb meant only as intensifier not an insult.