Step 1 – Setup ^
Towards the end of the last article, I suggested that you fix the percentages to actually show a percent and change the colors based on what percentage was left. I have done that here:
' Format the numbers into correct percentages and change color based on result If adoRecordset.Fields(intCol).Name = "VolumePercentAvailable" Then With .Cells(intRow, intCol + 1) ' Remove the decimal .Replace What:=".", Replacement:="" ' Replace the decimal at the beginning if its less than 100 If .Value <> "100" Then .Value = "." & .Value Else ' Otherwise, it is 100% .Value = 1 End If ' No value (error checking) If .Value = "." Then .Value = "0" End If ' Format the cell to two decimal places and a percent sign .NumberFormat = "0.00%" ' If there's less than 20% left, turn the color red If .Value <= 0.2 Then .Font.Color = RGB(156, 0, 6) .Interior.Color = RGB(255, 199, 206) End If ' If there's more than 90%, turn green If .Value >= 0.9 Then .Font.Color = RGB(0, 97, 0) .Interior.Color = RGB(198, 239, 206) End If End With End If
Open the wkbReport in the Visual Basic IDE and insert the above code below this line in the Build_Storage() subroutine.
' Write the data to the cell .Cells(intRow, intCol + 1).Value = adoRecordset.Fields(intCol).Value
If you read the comments in this block of code, you will see that name of the field is VolumePercentAvailable then we want to remove the decimal and place it at the beginning of the value retrieved if it is less than 100 and just change the value to 1 otherwise. Also, a little error checking is done to make sure we don’t get blank values which will cause our code to break. Then we use Excel’s cell formatting function to format the number into two decimal places and a percent sign.
The next part is the most important part. If the number is less than .2 (or 20%) we want to change the color to a red so that it is clearly visible at a quick glance. If the number is greater than .9 (or 90%) we’ll turn it green. Why is this so important? We want to chart the volumes with less than, in this case, 20% so we can just glance at the chart and see what server needs quick help.
Step 2 – Creating the chart ^
We need to add three different variables to our code. We will need an X value and a Y value for the chart, and a constraint value. The constraint value will be 10 for this example. It can be changed to whatever you would like it to be. However, be careful as the code will break if there are too many volumes on a tiny chart!
This code will be inserted inside the Build_Storage() subroutine directly below our last Dim statement.
Dim strVolX As String Dim strVolY As String Dim intVolCount As Integer
To make this more presentable, we also want to remove the gridlines with the following code below these Dim statements:
shtData.Activate ActiveWindow.DisplayGridlines = False
In order to gather the information, we need to put a few lines into the If statement where we check to see if the volume has less than 20% remaining. I have bolded the additional code below:
' If there's less than 20% left, turn the color red If .Value <= 0.2 Then .Font.Color = RGB(156, 0, 6) .Interior.Color = RGB(255, 199, 206) ' Collect the data, up to 10, for our chart and chart If intVolCount < 10 And .Value <> "0" Then strVolX = strVolX & "A" & intRow & "," strVolY = strVolY & "D" & intRow & "," intVolCount = intVolCount + 1 End If End If
Basically all we have done is concatenate the strings so that we will eventually get the names of the servers in strVolX and the percentages of the volumes in strVolY by their cell references.
After attaining the information necessary to create the chart and storing it in a couple strings, we’re ready to insert it into our worksheet. You will need to add the following code to the end of our Clear_Sheets() subroutine inside the With shtData statement. This will remove any previous charts that may have accidentally been saved.
‘ Remove all charts Do Until .Shapes.Count = 0 .Shapes(1).Delete Loop
To create the actual chart, we will add this code to the end of the Build_Storage() subroutine just after the statement Set adoConnection = Nothing:
' Create a chart and add it to shtData shtData.Shapes.AddChart(xl3DColumnClustered, 685, 0, 310, 175).Select With ActiveChart ' Remove any previous collections in memory Do Until .SeriesCollection.Count = 0 .SeriesCollection(1).Delete Loop ' Set the properties for the chart .Parent.Name = "chtVol" .HasTitle = True .ChartTitle.Characters.Text = "Low Volume" .Parent.RoundedCorners = True .ClearToMatchStyle .ChartStyle = 44 .ClearToMatchStyle If strVolX <> "" And strVolY <> "" Then With .SeriesCollection.NewSeries .Name = "Low Volume" ' Assign values and remove last comma (,) .XValues = shtData.Range(Left(strVolX, Len(strVolX) - 1)) .Values = shtData.Range(Left(strVolY, Len(strVolY) - 1)) End With End If ' Remove legend (personal preference) .Legend.Delete End With With shtData ' Remove 3D effects (personal preference) .Shapes("chtVol").ThreeD.RotationX = 0 .Shapes("chtVol").ThreeD.RotationY = 90 End With
One of the most import lines of code in this block is the shtData.Shapes.AddChart(xl3DColumnClustered, 400, 0, 350, 200).Select. What we’re doing is creating a chart as a 3D column chart that is 400 pixels from the left, 0 pixels from the top, 350 pixels wide and 200 pixels tall. Then we automatically select it so we can set its properties. We now have an awesome chart that looks like this:
A list of chart types can be found at Microsoft’s MSDN site here. Be cognoscente of the chart type though. A lot of them have different properties that need to be set for them to show correctly.
Let’s add another chart using our same collected data so that we have a few things to put on our dashboard in the next part. This time let’s use an area chart:
' Create an area chart and add it to shtData shtData.Shapes.AddChart(xl3DArea, 400, 225, 350, 200).Select With ActiveChart ' Remove any previous collections in memory Do Until .SeriesCollection.Count = 0 .SeriesCollection(1).Delete Loop ' Set the properties for the chart .Parent.Name = "chtAreaVol" .HasTitle = True .ChartTitle.Characters.Text = "Area Chart - Low Volume" .Parent.RoundedCorners = True .ClearToMatchStyle .ChartStyle = 44 .ClearToMatchStyle If strVolX <> "" And strVolY <> "" Then With .SeriesCollection.NewSeries .Name = "" ' Assign values and remove last comma (,) .XValues = shtData.Range(Left(strVolX, Len(strVolX) - 1)) .Values = shtData.Range(Left(strVolY, Len(strVolY) - 1)) End With End If ' Remove legend (personal preference) .Legend.Delete End With With shtData ' Remove 3D effects (personal preference) .Shapes("chtAreaVol").ThreeD.RotationX = 0 .Shapes("chtAreaVol").ThreeD.RotationY = 90 End With
This code is nearly exactly the same as our previous chart. The only differences are the type of chart, the position, and the name. Take some time to look over this block of code and the previous. Our new chart looks like this:
Step 3 – The dashboard ^
For organizational purposes, let’s change the captions of the sheets to something a little more descriptive and remove the third worksheet that we won’t be utilizing.
Go to the main Excel window, right click on Sheet1’s tab and select rename. Change it to Volumes. Then right click on Sheet2’s tab and rename it to Dashboard.
We also want to move the Dashboard tab in front of the Volumes tab and delete Sheet3 so when complete we will have this:
Go back into the Visual Basic IDE and you’ll see on the left, things have changed a little in the Project – VBAProject window. You now see the names we changed the tabs to in the parenthesis:
If you recall, in part one of this series, we had to change the name of the worksheet to make it easier to code for. We need to do the same thing here. Click the Sheet2 (Dashboard) worksheet and change the (Name) parameter in the Properties – Sheet2 window to shtDash. Always try to stay as organized as possible. It will help you immensely in larger projects!
We must now create another subroutine to build our dashboard. Go to the very end of the our code, below the Build_Storage() subroutine and create the new one: Build_Dash().
Private Sub Build_Dash() End Sub
We want to make sure this fires off after the Build_Storage() sub, so go into the Workbook_Open() and add this code at the end before the End Sub statement:
' Build the dashboard Build_Dash
We also want to make sure we start with a clean slate for our dashboard worksheet too. Move into the Clear_Sheets() subroutine and add the code to reset the dashboard to the end of that sub right about the End Sub statement:
' Select shtDash worksheet With shtDash ' Reset all of the cells With .Cells .Clear .Font.Size = 10 .Font.Color = RGB(0, 0, 0) End With ' Create the title for the page With .Range("A1:I1") .Merge .Value = "Volume Utilization Dashboard" .Font.Bold = True .Font.Size = 16 .Font.Color = RGB(255, 255, 255) .Interior.Color = RGB(0, 0, 0) .VerticalAlignment = xlCenter .HorizontalAlignment = xlCenter End With ' Remove all charts Do Until .Shapes.Count = 0 .Shapes(1).Delete Loop End With
Here we reset all of the cells to a default format, create a title for our page, and removed all of the charts we’ll be dynamically adding next.
Let’s move back into the Build_Dash() sub and add the code to take a “snapshot” of the charts on the Volumes tab and place them on the Dashboard tab.
' Remove gridlines shtData.Activate ActiveWindow.DisplayGridlines = False ' Select the Volumes tab shtData.Activate ' Create a picture from the cells the chart is located over shtData.Range("G1:N15").CopyPicture ' Go back to the Dashboard tab shtDash.Activate ' Paste the picture shtDash.Range("A3").PasteSpecial ' Select the newly pasted picture shtDash.Shapes.Range(shtDash.Shapes.Count).Select With Selection ' Add a hyperlink to the chart shtDash.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), Address:="", SubAddress:="Volumes!A1" End With ' Select the Volumes tab shtData.Activate ' Create a picture from the cells the chart is located over shtData.Range("G17:N33").CopyPicture ' Go back to the Dashboard tab shtDash.Activate ' Paste the picture shtDash.Range("A20").PasteSpecial ' Select the newly pasted picture shtDash.Shapes.Range(shtDash.Shapes.Count).Select With Selection ' Add a hyperlink to the chart shtDash.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), Address:="", SubAddress:="Volumes!A1" End With
What we’re accomplishing with this code is going to the Volumes tab, copying the cells the chart is over, going back to the Dashboard tab, pasting it in, and adding a hyperlink to the Volumes tab. Then we do the same thing for the second chart. Now we have this:
Step 4 – Auto Save ^
In my opinion, reporting wouldn’t be complete without archiving. Things change so often, it’s nice to have a place to look back on what happened months ago. Let’s create a new subroutine called Save_Workbook(.)
Private Sub Save_Workbook() End Sub
Now add a reference to the Workbook_Open sub below the Build_Dash statement.
' Save the workbook Save_Workbook
Now we can add the code to our Save_Workbook() subroutine. Because the code here is a little lengthy, we’ll take a chunk at a time. The first part of the code is as follows:
Dim strLocation As String Dim strName As String Dim intI As Integer Dim intL As Integer ' Save the workbook and don't prompt Application.DisplayAlerts = False wkbReport.Save Application.DisplayAlerts = True ' This is where you want to save the reports strLocation = "C:\" ' New file name strName = Format(Now(), "yyyymmdd") & "_Dashboard.xlsx"
Here we are instantiating our variables with the Dim statement. Then we want to save our current workbook and not have the user prompted. We then set the location where we want to save our workbooks with the strLocation variable and our file name in the strName variable. We also take today’s date and format it into a YYYYMMDD style date.
' We need to count all the VB components so we can remove them intI = 0 On Error Resume Next intI = ActiveWorkbook.VBProject.VBComponents.Count On Error GoTo 0 If intI > 1 Then ' Remove the VB components With ActiveWorkbook.VBProject For intI = .VBComponents.Count To 1 Step -1 On Error Resume Next .VBComponents.Remove .VBComponents(intI) On Error GoTo 0 Next intI End With ' Remove the lines of code With ActiveWorkbook.VBProject For intI = .VBComponents.Count To 1 Step -1 On Error Resume Next intL = .VBComponents(intI).CodeModule.CountOfLines .VBComponents(intI).CodeModule.DeleteLines 1, intL On Error GoTo 0 Next intI End With End If
In this chunk of code, we are counting of the VB components and lines of code so that we can find and delete everything. We don’t want any of it being saved in the archive workbook.
' Don't prompt the user Application.DisplayAlerts = False ' Save the workbook as the new file in the new location wkbReport.SaveAs strLocation & strName, 51 ' Let the user know where it was saved MsgBox "The document has been saved to: " & strLocation & strName ' Close the workbook wkbReport.Close Application.DisplayAlerts = True ' Close Excel Application.Quit
This last bit of code will save the workbook as another workbook and prompt the user as to where it saves. It then closes out everything leave you with a blank Excel window.
On a side note, if you need to change any code in the workbook, you will need to hold down the escape key on your keyboard while the workbook opens in order for the code to quit running so you can get into the Visual Basic IDE to edit the code.
Series conclusion ^
There was a lot of information in the three parts of this series. We initially set up our environment to utilize ADOdb, we set up Excel and ran our first code. Then we pulled information from the database and formatted it, added charts, a dashboard, and an auto save archive feature.
There are many, many things that can be accomplished using Excel and VBA that can save you hours of wasted time. I encourage you to look further into this amazing Office feature. If you have any questions, feel free to leave them in the comments section, and I will do my best to help out. Thanks for reading!