In the last post of this series we set up our default formatting and built a subroutine that would reset it when the workbook is opened. We also connected to our database, pulled some information, and changed the formatting of our storage sizes. Finally, we password protected our code from prying eyes. In this post, we will create some pizazz for our workbook and make it more attractive by adding charts and a dashboard.
Latest posts by Andrew Jacops (see all)

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:

Excel-VBA-SQL - Storage Overview

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:

Excel-VBA-Storage - Area Chart

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.

Excel-VBA-SQL - Rename

We also want to move the Dashboard tab in front of the Volumes tab and delete Sheet3 so when complete we will have this:

Excel-VBA-SQL - - Tabs

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:

Excel-VBA-SQL - - VBAProject

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:

Excel-VBA-SQL - Dashboard

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!

0 Comments

Leave a reply

Your email address will not be published.

*

© 4sysops 2006 - 2022

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account