A request for Excel programming gurus

From Visual Basic to GNU C, this is the place to talk programming.

Moderators: SecretSquirrel, just brew it!

A request for Excel programming gurus

Postposted on Fri Jan 16, 2009 4:00 pm

Color-coding bar graphs by manufacturer, model, or whatever, is a great way to make them more readable. However, it's royal pain, especially for hard drive reviews where I'm working with dozens of graphs and a deluge of results. Excel doesn't seem to have a mechanism to automatically color bars based on axis labels, but I imagine VB wizards would be able to code up something that would work. I hope.

What I want to be able to do is simple. After graphs have been created and sorted, I'd like a simple and easy way to color each bar associated with an axis label of XXX with a specified color, and I'd like the colors to cascade across all graphs in a workbook. Beyond the odd batch file, I haven't done any programming for years, so I'm putting the call out to you folks.

I'm running Excel 2002, and can provide a sample file for anyone up to the task of taking on this hopefully simple challenge. In return, I can offer my undying gratitude and the knowledge that you've made TR graphs that little bit easier to create. And perhaps some official TR swag, or something.

If you're interested, drop me an email. I'll also keep an eye on this thread.

Geoff

---
Geoff Gasior
The Tech Report
http://techreport.com
Dissonance
TR Staff
Gold subscriber
 
 
Posts: 529
Joined: Wed Dec 26, 2001 6:00 pm
Location: Vancouver, BC

Re: A request for Excel programming gurus

Postposted on Mon Jan 19, 2009 11:55 am

Sounds like fun. You've got mail.

EDIT: I think. Maybe it didn't go through.
Usacomp2k3
Gerbil God
 
Posts: 21240
Joined: Thu Apr 01, 2004 3:53 pm
Location: Orlando, FL

Re: A request for Excel programming gurus

Postposted on Mon Jan 19, 2009 10:55 pm

So I got it pretty much figured out. Let me know if this works for you. (I got the original code from a web site, but I can't for the life of me find it again. I did make some fairly significant changes to that though.)
http://usacomp.homeserver.com/Site/TR/Book1.xls
Code: Select all
Sub RunChartColoration()
Dim myChart As ChartObject, mysheet As Worksheet
Dim i As Integer, arValues As Variant
For Each ws In Worksheets
    For Each myChart In ws.ChartObjects
        myChart.Activate
        With ActiveChart.SeriesCollection(1)
            arValues = .XValues
            For i = 1 To UBound(arValues)
                .Points(i).Interior.ColorIndex = VLookupStuff(arValues(i))
            Next
        End With
    Next
Next
End Sub

Function VLookupStuff(ByVal name As String)
VLookupStuff = Application.WorksheetFunction.VLookup(name, Sheets("data").Range("names"), 2, False)
End Function

So basically:
*Have a range in some sheet Have that is a simple 2-column table with the names in one column, and the colorindex that you would like to use to represent that value in the next column.
*Give this range a name. In this example I have it in a sheet named "data" with the named range of "names". Edit the code to correspond with your choices.
*Now just run the macro (for exmple, by creating a button and assigning it the macro name, or by hitting alt-f8 and choosing it)

A quick explanation of the code:
This uses the vlookup function in excel to lookup the names and return the colors.
As you can see there are nested for loops that basically go through each worksheet in the file, and then selects each chart on that sheet. It then goes and looks at all the points in that chart, looks up what the value is, runs the function that then returns what color you want that to be, and then applies the color. It should be fairly straightforward.

I hope that this helps, and it's open to tweaking and customizing to what you need it to be.
Usacomp2k3
Gerbil God
 
Posts: 21240
Joined: Thu Apr 01, 2004 3:53 pm
Location: Orlando, FL

Re: A request for Excel programming gurus

Postposted on Fri Jan 23, 2009 5:18 pm

And attempt #2:
Code: Select all
Function VLookupStuff(ByVal name As String)
Dim c As Range
For Each c In Range("names")
    If c.Value = name Then
        VLookupStuff = c.Offset(0, 1).Value
        Exit For
    End If
Next
End Function
Sub RunChartColoration()
Dim myChart As ChartObject, mysheet As Worksheet
Dim i As Integer, arValues As Variant
For Each ws In Worksheets
    For Each myChart In ws.ChartObjects
        myChart.Activate
        With ActiveChart.SeriesCollection(1)
            arValues = .XValues
            For i = 1 To UBound(arValues)
                .Points(i).Interior.ColorIndex = VLookupStuff(arValues(i))
            Next
        End With
    Next
Next
End Sub
Usacomp2k3
Gerbil God
 
Posts: 21240
Joined: Thu Apr 01, 2004 3:53 pm
Location: Orlando, FL

Re: A request for Excel programming gurus

Postposted on Wed Jun 03, 2009 2:09 pm

Ok, wow. Should have started using this a while ago.

I thought for sure I could modify your script to do what I want, but I am not picking up this VB stuff quickly... need more examples, I think. Any chance you could modify this script to:

-Only work on the active worksheet
-Only work on the selected chart
-Step through as many series as are present and colorize them all

Any one of these would be helpful. Thanks!
Scott Wasson - "Damage"
Editor - The Tech Report
Damage
TR Staff
Gold subscriber
 
 
Posts: 1647
Joined: Wed Dec 26, 2001 6:00 pm
Location: Lee's Summit, Missouri, USA

Re: A request for Excel programming gurus

Postposted on Sat Jun 06, 2009 9:58 am

U2K3, if I was Sylar, I'd be coming after your brain boy! I'd much rather eat a little bit of gray matter than take the time required to learn something. Actually, that isn't true, I find the learning process to be quite thrilling, I just don't have much time. When the kids move out............
flip-mode
Gerbil Khan
Silver subscriber
 
 
Posts: 9068
Joined: Thu May 08, 2003 11:42 am
Location: Cincinnati, OH


Return to Developer's Den

Who is online

Users browsing this forum: No registered users and 1 guest