Creating Plots in MS Excel Using Visual Basic Code

I am frequently in need of generating scatter and line plots of measured physiological signals. To this end, I like to use many different types of software tools. The one I like the most, however, for rapid manipulation and visualization of data is Microsoft Excel.

Oftentimes I need to create charts from columnar data within Excel. Creating and formatting line plots, particularly if creating many, can be a time-consuming and tedious process. Therefore, I had decided on searching for approaches for creating automated chart plotting routines of the format I was looking for. Naturally, I turned to the Internet and to my various MS Excel textbooks.

To my disappointment, however, after spending what was perhaps a week of evenings searching, I honestly could not find anything that fit the bill on the open Internet. That is to say, it was not as if I could not find any help… simply that I could not find anything that already provided me with exactly what I was looking for or event a close template that I could pilfer and customize.

Thus, it became necessary for me to slog through the process myself. The purpose of this entry is to simply communicate what I found and did so that another wayward traveler might be saved from some time and effort… although, I can imagine that what I provide below is not precisely what someone else is looking for, either.

Nevertheless, and once you review my code, you may conclude “that really was not anything special… why did it take him so long to create that, and why so difficult to find comparable models that he could reuse?”

The answer to these questions is that oftentimes those pursuing the process need to educate themselves. Thus, it was not merely me taking another individual’s sample code…it was about me understanding that sample code. The understanding is often the hardest part of the challenge.

To begin…

The objective is to create and X-Y plot, wherein the X-data and Y-data are columns in an MS Excel Worksheet, as shown in the figure below:

Columns of data in a simple MS Excel worksheet, showing three dependent columns (y1(t), y2(t), y3(t)) and one independent column, time.

These data can be plotted either one dependent variable at a time or any combination of dependent columns versus the independent column. Accomplishing this manually using MS Excel is a straightforward task. Yet, if one wishes to develop a standardized template or create a common format, particularly if the plotting is to be repeated many times, performing this manually becomes overwhelmingly tedious.

The routine I developed creates a chart with multiple data series displayed versus the common independent axis. The plot generated by the Visual Basic code from these data is as shown below:

The code draws upon four columns of data: three dependent variables and one independent variable.

The code follows:

Sub plotData()
' Purpose: Plots 3 functions versus time in Excel using
' visual basic programming.
' 2017-12-31.
' J. Zaleski

' how many rows?

 Dim rCount As Long
 rCount = ActiveSheet.Cells(9, 10)

' axis dimensions

 Dim xaxis As Range
 Dim yaxis As Range
 Dim yaxis2 As Range
 Dim yaxis3 As Range

 Set xaxis = Range("$a$3", "$a" & rCount)
 Set yaxis = Range("$b$3", "$b" & rCount)
 Set yaxis2 = Range("$c$3", "$c" & rCount)
 Set yaxis3 = Range("$d$3", "$d" & rCount)

 ' dimension chart

 Dim c As Chart
 Set c = ActiveWorkbook.Charts.Add
 Set c = c.Location(Where:=xlLocationAsObject, Name:="test")

With c
 .ChartType = xlXYScatterLines 'A scatter plot, not a line chart!
 ' set other chart properties
 End With

' add data series to chart

With c
 ' assign x and y value ranges to series 1

 .SeriesCollection(1).Name = Worksheets("test").Cells(2, 2)
 .SeriesCollection(1).Values = yaxis
 .SeriesCollection(1).XValues = xaxis
 .SeriesCollection(1).MarkerBackgroundColor = RGB(0, 0, 0)
 .SeriesCollection(1).MarkerForegroundColor = RGB(0, 0, 0)
 .SeriesCollection(1).MarkerSize = 2
 .SeriesCollection(1).MarkerStyle = 3
 .SeriesCollection(1).Format.Line.Weight = 1#
 .SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0, 0, 0)
 .SeriesCollection(1).Format.Line.BackColor.RGB = RGB(0, 0, 0)

' assign x and y value ranges to series 2

 .SeriesCollection(2).Name = Worksheets("test").Cells(2, 3)
 .SeriesCollection(2).Values = yaxis2
 .SeriesCollection(2).XValues = xaxis
 .SeriesCollection(2).MarkerBackgroundColor = RGB(128, 0, 0)
 .SeriesCollection(2).MarkerForegroundColor = RGB(128, 0, 0)
 .SeriesCollection(2).MarkerSize = 2
 .SeriesCollection(2).MarkerStyle = 4
 .SeriesCollection(2).Format.Line.Weight = 1#
 .SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(128, 0, 0)
 .SeriesCollection(2).Format.Line.BackColor.RGB = RGB(128, 0, 0)

' assign x and y value ranges to series 3

 .SeriesCollection(3).Name = Worksheets("test").Cells(2, 4)
 .SeriesCollection(3).Values = yaxis3
 .SeriesCollection(3).XValues = xaxis
 .SeriesCollection(3).MarkerBackgroundColor = RGB(128, 128, 0)
 .SeriesCollection(3).MarkerForegroundColor = RGB(128, 128, 0)
 .SeriesCollection(3).MarkerStyle = 5
 .SeriesCollection(3).MarkerSize = 2
 .SeriesCollection(3).Format.Line.Weight = 1#
 .SeriesCollection(3).Format.Line.ForeColor.RGB = RGB(128, 128, 0)
 .SeriesCollection(3).Format.Line.BackColor.RGB = RGB(128, 128, 0)

End With

'adjust major unit on x axis

With c.Axes(xlCategory)
 .MajorUnit = Worksheets("test").Cells(8, 10)
 End With

'adjust major unit on y axis

With c.Axes(xlValue)
 .MajorUnit = Worksheets("test").Cells(5, 10)
 End With

' find maximum y value

Dim maxY As Integer
 maxY = Worksheets("test").Cells(3, 10)

' find minimum y value

Dim minY As Integer
 minY = Worksheets("test").Cells(4, 10)

' find maximum x value

Dim maxX As Integer
 maxX = Worksheets("test").Cells(6, 10)

' find minimum x value

Dim minX As Integer
 minX = Worksheets("test").Cells(7, 10)

With c
 'locate chart

.ChartArea.Top = 50
 .ChartArea.Left = 400

'adjust width and height

.ChartArea.Width = 400
 .ChartArea.Height = 200

'adjust y-axis Scale

.Axes(xlValue).MinimumScale = minY
 .Axes(xlValue).MaximumScale = maxY

'adjust x-axis Scale

.Axes(xlCategory).MinimumScale = minX
 .Axes(xlCategory).MaximumScale = maxX

'adjust font

.ChartArea.Format.TextFrame2.TextRange.Font.Size = 8
 .ChartArea.Format.TextFrame2.TextRange.Font.Name = "Arial Narrow"

'adjust font color

.Axes(xlCategory).TickLabels.Font.Color = RGB(0, 0, 0)
 .Axes(xlValue).TickLabels.Font.Color = RGB(0, 0, 0)

'adjust decimal places on x-axis

.Axes(xlCategory).TickLabels.NumberFormat = "0.00"
 .Axes(xlValue).TickLabels.NumberFormat = "0.00"

'adjust plot labels

.Axes(xlCategory, xlPrimary).HasTitle = True
 .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = Worksheets("test").Cells(2, 1)
 .Axes(xlValue, xlPrimary).HasTitle = True
 .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Three Functions versus Time"
 .HasLegend = True
 End With

End Sub

The code is written as a macro and the data are maintained in a worksheet called “test”. ┬áThe macro is associated with a button placed on the worksheet which, when pressed, creates a plot of the three dependent variables. The routine is not intended to be all-encompassing in terms of capability. But, to the uninitiated, the code serves as a building block for tailoring and customization. For instance, the routine requires three specific dependent columns of data. The .SeriesCollection field is specified with three indices. This can be generalized to any number of plots using a for loop indexing variable. Furthermore, the color schemes, tick labels, size, location, etc. are all customizable.

Leave a Reply

Your email address will not be published. Required fields are marked *