Excel VBA

I've never been a big VBA coder in Excel, but I've e'er wanted to be a big VBA coder in Excel. Information technology would certainly save me a lot of time, just, well, yeah, I merely oasis't learned.

Until now!

Over the past few months, I've embarked on a journey to learn some VBA. I'one thousand not trying to become an skilful, mind you, simply know plenty to be dangerous. That's why I was super excited to create my very own macro to practise some uncomplicated chart styling using VBA code.

The whole thing started because I wanted to automate a few tasks in my graph creation procedure. In this case, I wanted tostart with grayness; I wanted to make all the objects in my chart gray and and then start calculation color to draw the reader's attending to a specific line or bar. For purposes of demonstration, permit's just say I wanted to create a slope chart in which–just to showtime–I'll make every line gray except one that volition be vivid green.

Building a slope chart in Excel is pretty like shooting fish in a barrel–create a line chart and and then switch the rows and columns (hither's i version). There are two parts of the Excel slope chart that can be time-consuming: adding labels and recoloring the lines, so let's utilise VBA to sparse the lines and make them all a calorie-free gray.

Allow's say, for example, I have this basic slope chart. All of the lines are different colors, but I am going to make all of them gray except for the one series that increases between the 2 years. I could select each line one at a fourth dimension and change the color of each, only It'southward in this repetitive work where VBA comes in handy.

So here'due south what I did: A bit of VBA lawmaking that loops through all the serial on a chart (doesn't matter what kind of chart) and changes the width of each line to 1.5 and the color to gray. I and so manually change the formatting of just one line to vivid greenish (and, no, this chart isn't done–information technology needs labels, title, etc.–but not today).

Sub OneColorLine()

Dim icolor As Long
Dim seriescount As Long

seriescount = ActiveChart.SeriesCollection.Count

For icolor = 1 To seriescount

ActiveChart.SeriesCollection(icolor).Select

With Option.Format.Line

.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText2
.ForeColor.RGB = RGB(217, 217, 217)
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency = 0
.Weight = 1.v

End With

Next

End Sub

Cool, eh? (Well, possibly not for people who really know how to code in VBA, but it'southward 1 of my starting time shots, and so I'm pretty pleased.)

The lawmaking itself may look complicated, but it'due south actually pretty simple: Define 2 variables, icolor, which will exist the counter in the forthcoming Forloop; andseriescount, which equals the total number of series in the nautical chart. TheForloop then goes through each series–from the first to the terminal serial (remember,seriescount?)–and changes the color to grey (denoted by the RGB code as 217, 217, 217). TheWeightcommand changes the line thickness to one.5. Obviously,TintandShade, Brightness,andTransparencyare additional options that I have chosen not to change.

Want to try it for yourself? Okay, here's what you do.

1. First, you need to actuate theDevelopertab. In Excel 2010 on Windows, select theFilebutton, thenOptions.In the resulting carte, selectCustomize Ribbonand in the right-hand menu underCustomize the Ribbon, select the checkbox next toDeveloper.In Excel 2011 on Mac, selectPreferencesunder theExcellink in the toolbar, then selectRibbon and check the box next toDeveloper.
2. Now that the Programmertab exists in your ribbon, navigate there and select theMacropush. This will open theMacrowindow. Type the proper noun of the Macro–I named this 1OneColorLine, which y'all can identify considering it comes right afterwards theSubcommand at the very top.
3. SelectCreate.
4. In the VBA Editor, paste the lawmaking snippet from in a higher place (the code must brainstorm with "Sub OneColorLine()" and end with "End Sub").
5. Go back to Excel and create a simple chart for yourself. Select the chart, select theMacrospush again in theDevelopertab, select the macro you merely created, and hitRun.
6. Yous should now accept a chart where all the lines are grayness. If y'all want, you can change the RGB code in the VBA code to whatsoever colour y'all want.

As I said, I'm no VBA expert, but there are tons out there if y'all'd similar to acquire more. I'm currently enjoying some of the videos at Excel Campus.

If you'd like to learn more than about VBA here, please allow me know in the comments beneath and I'll write a fleck more about how to become started, how to save them for utilise in all of your Excel files, and how to add them as shortcut buttons.

PS Just a funny story about this post. I was using this post equally a reference for some new Excel VBA evolution I'yard doing, and i day I couldn't find the postal service! I logged back into my WordPress account and certain enough found it–listed in my Drafts folder. I had neglected to publish this post for nearly a year. Anyways, I promise you found information technology useful.

gracefortaged.blogspot.com

Source: https://policyviz.com/2016/10/20/excel-vba/

Share this post