VBA: An Advanced Add-in to Open a CSV File and Create Chart with Data Specified by User

Introduction

This article builds upon a previous post, VBA: A Simple Tool to Open a CSV File and Create Chart with Data Specified by User, which showed how to build the barest possible VBA procedure for opening a CSV file and making a chart from its data. This article will show how to make the VBA procedure more robust, easier to use, and resistant to errors. It is assumed that you’ve already built the simple VBA tool from the earlier article.

Advanced Solution

Rather than the simple workbook we created in the previous solution, the advanced solution will use an add-in. This integrates the add-in’s functionality more smoothly into Excel, making the solution a more professional option.

Add-ins are like regular Excel workbooks, complete with worksheets and other Excel objects, but they have a different file extension, .xlam, and they are not visible in the Excel interface. They can be opened like regular workbooks when their functionality is needed, or they can be installed so that they are always available.

Code Workbook (Add-In)

Saved Information: Named Range Containing Chart Types and Styles

The worksheet contains a list of chart types that our program will create. These are the simplest and most useful charts built into Excel. The list has three columns, the first being the integer values of Excel’s chart type constants (e.g., xlArea = 1, xlXYScatter = -4169), the second the default chart styles in Excel 2013 (ignored in earlier versions), and the third the chart type names that will appear in the dialog. To modify the eerlier worksheet, simply insert a column between the Value and Type values, and insert the Style values.

The range A2:C12 has been named “ChartTypes”, so it will be easy for the code to put these values into the dialog. We can save information like this even in the invisible worksheets of an invisible add-in.

Create an Add-In

Now that we’ve updated the chart type data, we can save our workbook as an add-in.

With the workbook active, go to File tab > Save As, browse to a folder, change the Save As Type to Excel Add-In (*.xlam), and note that the dialog changes to the default user add-in library (C:\Users\[username]\AppData\Roaming\Microsoft\AddIns). This is a convenient place to store add-ins, because they are easier to find when you want to install them. However, if you have an orderly file system hierarchy, you may want to store the add-in with related files. If so, navigate to the desired folder. Then click Save.

The worksheets of an add-in are not visible in the Excel interface. However, you can temporarily change an add-in back to a regular workbook. In the VB Editor’s Project Explorer, select the ThisWorkbook object of the add-in’s VB project, choose the IsAddIn property in the Property Window, and change it to False. The add-in workbook is now visible like any other workbook. Make any adjustments, then change the IsAddIn property back to True.

Starting the Program: The Excel Ribbon

Since the sheet is not visible, there is no button to start the code. We’ll use a button on the ribbon instead.

Many web pages and blog posts have been written about writing XML code for custom ribbon controls. As far as I know, only two books have been dedicated to the ribbon.

One is RibbonX: Customizing the Office 2007 Ribbon (affiliate link), written by two Excel MVPs and one Access MVPs. This is a very comprehensive volume that covers more than you could possibly want to know about customizing your ribbon. On a dollars-per-page basis, it also has to be about the cheapest Excel reference I’ve ever purchased.

The other book about the Office ribbon is RibbonX For Dummies, and I must admit I know very little about it.

To make it easy to add custom ribbon elements to your workbook or add-in, you should download the Custom UI Editor from the Open XML Developer web site. Open the add-in or workbook in the Custom UI Editor, insert an Office 2007 Custom UI Part from the Insert menu, and paste the code below into the Custom UI Part.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab idMso="TabData">
        <group id="gpCSV" label="Process CSV">
          <button id="btnProcessCSV" label="Process CSV File" screentip="Import CSV 
              file, save as Excel workbook, insert chart using dialog-selected type 
              and data" size="large" onAction="RX_OpenCSVFileAndPlotData" 
              imageMso="GroupChartLocation" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

This code adds a custom group, “Process CSV”, to the Data tab of Excel’s ribbon, and adds a custom button, “Process CSV File”, to this group. The button calls a procedure named RX_OpenCSVFileAndPlotData.

The Custom UI Editor can be used to generate the callback for this procedure:

'Callback for btnProcessCSV onAction
Sub RX_OpenCSVFileAndPlotData(control As IRibbonControl)
End Sub

This callback is pasted into a regular code module, and the procedure that we’ve been working on, OpenCSVFileAndPlotData, is called from within the callback. You could simply have called OpenCSVFileAndPlotData from the XML code, but you would have had to add the control As IRibbonControl argument to the procedure definition. This makes it harder to simply call the code from the VB Editor’s Immediate Window.

Here is the finished callback, ready to go:

'Callback for btnProcessCSV onAction
Sub RX_OpenCSVFileAndPlotData(control As IRibbonControl)
  OpenCSVFileAndPlotData
End Sub

The button looks like this, at the right end of the Data tab of the ribbon:

Code Module

Here are the adjustments to the MChartFromCSVFile code module.

1. Get CSV File Name

There are several things we can do to make sure the program is nice to the users.

First of all, we need to exit gracefully if the user cancels the GetOpenFileName dialog. Canceling the dialog returns False, so we need to prevent the error that will occur when the program tries to open a file named “False”.

We should check whether the file is already open in Excel, and offer to work on the open file.

We should check whether the file is even a CSV file. If it is not a CSV file, it will not open in Excel with the expected column structure. It is possible to make the program smart enough to open any text, and split the data into columns using any delimiter (commas, tabs, pipe characters), but that is for another exercise.

If the user decides to abort at any point, the execution proceeds to a label named ExitProcedure at the end of the main procedure.

  Do
    sCSVFullName = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , _
        "Select a CSV file", , False)
    
    '' check whether user cancels
    If sCSVFullName = "False" Then GoTo ExitProcedure
    
    '' check that file isn't already open
    bOpenFile = False
    For Each wbTest In Workbooks
      If wbTest.FullName = sCSVFullName Then
        bOpenFile = True
        Exit For
      End If
    Next
    If bOpenFile Then
      iMsgBoxResult = MsgBox("Data file is already open. Continue with open file?", _
          vbYesNo + vbQuestion, "File Already Open")
      If iMsgBoxResult = vbYes Then
        Set wb = wbTest
        Exit Do
      End If
    End If
    
    '' ensure that it's a CSV file
    If LCase$(Right$(sCSVFullName, 4)) <> ".csv" Then
      iMsgBoxResult = MsgBox("That doesn't appear to be a CSV file. Continue anyway?", _
          vbYesNo + vbQuestion, "Not a CSV File")
      If iMsgBoxResult = vbYes Then Exit Do
    Else
      Exit Do
    End If
  Loop

2. Open CSV file

The only thing to add here is a check whether our workbook wb has already been defined (that is, the CSV file was already open when the user started the program).

  If wb Is Nothing Then
    Workbooks.Open sCSVFullName
    Set wb = ActiveWorkbook
  End If

3. Save as workbook

There are many little changes we can do to make a user’s life easier. We want to save the workbook with the same name as the original CSV file, but we don’t want to bother the user with any duplicate file names. If we encounter a duplicate, we’ll append “_1″ to the file name; if that’s also a duplicate, we’ll append “_2″, and so on.

We need to check for duplicate file names among files already open in Excel, because two open workbooks cannot share the same file name. Then we need to check for duplicate file names in the directory we will save the workbook in.

  sFileRoot = Left$(sCSVFullName, InStrRev(sCSVFullName, ".") - 1)
  sWbkFullName = sFileRoot & ".xlsx"
  iFile = 0
  Do
    '' check for duplicate file (already open)
    bOpenFile = False
    For Each wbTest In Workbooks
      If wbTest.FullName = sWbkFullName Then
        bOpenFile = True
        Exit For
      End If
    Next
    
    '' check for duplicate file (in folder)
    If Not bOpenFile Then
      If Len(Dir(sWbkFullName)) = 0 Then Exit Do
    End If
    
    '' increment file name
    iFile = iFile + 1
    sWbkFullName = sFileRoot & "_" & CStr(iFile) & ".xlsx"
  Loop
  
  wb.SaveAs sWbkFullName, xlWorkbookDefault

All of this checking may seem excessively compulsive, but being a good programmer means preventing user frustration. Remember, the user may know where you live.

4. Parse file

We can improve the dialog display slightly, by not trying to show the first four rows of data if there are less than three rows in the CSV file.

While we’re making life easier for the user, let’s check the first row for text values, so we can precheck the dialog box if it looks like the file starts with column headers.

  Set ws = wb.Worksheets(1)
  Set rng = ws.UsedRange
  vRng = rng.Value2
  iRows = rng.Rows.Count
  iCols = rng.Columns.Count
  
  '' info to display: column number, first few rows of column
  ReDim vChartData(1 To iCols, 1 To 2)
  For iCol = 1 To iCols
    vChartData(iCol, 1) = iCol
    sTemp = ""
    For iRow = 1 To 4
      If iRow > iRows Then Exit For
      sTemp = sTemp & vRng(iRow, iCol) & ", "
    Next
    sTemp = Left$(sTemp, Len(sTemp) - 2)
    vChartData(iCol, 2) = sTemp
  Next

  '' first row header labels if all cells in first row are text
  For iCol = 1 To iCols
    bFirstRowHeaders = Not IsNumeric(vRng(1, iCol))
    If Not bFirstRowHeaders Then Exit For
  Next

5. Show dialog

When we load the dialog (UserForm), we pass in the array that shows the first few rows of data using the ChartData property, and also pass in our best guess about whether the first row of the has header labels in the FirstRowHeaders property.

After the user dismisses the dialog, we check whether the user canceled using the Cancel property. If the user did not cancel, we get chart type, first row headers, and X and Y columns using the appropriate properties.

Now we’re done with the UserForm, so we can unload it. If the user has canceled, we jump to the ExitProcedure label at the end of our program.

  Set frmChartFromCSVFile = New FChartFromCSVFile
  With frmChartFromCSVFile
    '' pass in information we know
    .ChartData = vChartData
    .FirstRowHeaders = bFirstRowHeaders
    
    .Show
    
    '' check whether user canceled
    bCancel = .Cancel
    If Not bCancel Then
      '' get information selected by user
      myChartType = .ChartType
      bFirstRowHeaders = .FirstRowHeaders
      vX = .Xcolumns
      vY = .YColumns
    End If
  End With
  Unload frmChartFromCSVFile

  If bCancel Then GoTo ExitProcedure

6. Draw chart

We define the chart series data as in the earlier program. We select a blank cell so the chart we insert has no data plotted in it.

We insert the chart using the Excel 2007 syntax, so the code runs as expected in Excel 2007 and 2010. After we assign the selected chart type in Excel 2013, we apply the default Excel 2013 chart style for that chart type, to obtain the nicer Excel 2013 styles.

One by one we add each series, and if there are more than one series, we add a legend to our chart.

  '' define some series parameters
  If IsEmpty(vX) Then
    nX = 0
  Else
    nX = UBound(vX, 1) + 1 - LBound(vX, 1)
  End If
  nY = UBound(vY, 1) + 1 - LBound(vY, 1)
  nSrs = nY
  If nX > nY Then nSrs = nX
  If bFirstRowHeaders Then
    Set rCht = rng.Offset(1).Resize(iRows - 1)
  Else
    Set rCht = rng
  End If
  
  '' select blank cell before inserting chart
  rng.Offset(iRows + 1, iCols + 1).Resize(1, 1).Select
  
  Set cht = ws.Shapes.AddChart.Chart '' Excel 2007+
  
  '' chart type and style
  With cht
    If myChartType <> 0 Then
      .ChartType = myChartType
      If Val(Application.Version) >= 15 Then
        '' Excel 2013 ChartStyles
        If iChartStyle > 0 Then
          .ChartStyle = iChartStyle
        End If
      End If
    End If
    
    '' add series
    For iSrs = 1 To nSrs
      Set srs = .SeriesCollection.NewSeries
      With srs
        ' X values
        If nX = 0 Then
          ' no X values specified
        ElseIf nX = 1 Then
          ' all series share X values
          .XValues = rCht.Columns(CLng(vX(0, 0)))
        Else
          ' each series has unique X values
          .XValues = rCht.Columns(CLng(vX(iSrs - 1, 0)))
        End If
        ' Y values
        If nY = 1 Then
          ' all series share Y values
          .Values = rCht.Columns(CLng(vY(0, 0)))
        Else
          ' each series has unique Y values
          .Values = rCht.Columns(CLng(vY(iSrs - 1, 0)))
        End If
        ' series name
        If bFirstRowHeaders Then
          If nSrs = nY Then
            .Name = "=" & rng.Cells(1, CLng(vY(iSrs - 1, 0))). _
                Address(True, True, xlA1, True)
          ElseIf nSrs = nX Then
            .Name = "=" & rng.Cells(1, CLng(vX(iSrs - 1, 0))). _
                Address(True, True, xlA1, True)
          End If
        End If
      End With
    Next
    
    If iSrs > 1 Then
      .HasLegend = True
    End If
  End With

7. Save file

Let’s select cell A1 before saving, just to satisfy our obsession with an orderly worksheet.

  ws.Range("A1").Select
  wb.Save

UserForm

The UserForm looks much like the earlier version, with the addition of a Default “label button” (a label with a raised appearance that functions as a button). This uses the first column of the CSV file as the chart’s X values, and each subsequent column as Y values for another series in the chart.

There also is a Cancel button if the user decides to bail out.

The lstChartType listbox now has three columns, two hidden columns for the chart type codes and the newly added chart style codes, and a visible column for the chart type names. This simply requires changing the ColumnWidths property to 0 pt;0 pt;112 pt. There is a new ChartStyles property used to get the appropriate Excel 2013 chart style codes for the selected chart type.

There are many changes to the code that make things run more smoothly.

Clicking the OK button will invoke some validation of selected columns, to ensure that the selected data is appropriate for the selected chart type. Data entry controls will be shaded light red if the data is not correctly validated and a message to the user will explain the problem.

Clicking the Cancel button sets a variable so that our code knows when the user canceled the process. In addition, code is added so that clicking the red X in the top right corner of the dialog doesn’t lead to error messages.

The X label button will do a little thinking before adding selected columns to the X listbox: if the chart type is a scatter chart, all selected columns will be added as X value columns, but if not, the list of X value columns will be cleared and only the first selected column will be added.

New variables in the UserForm module:

Dim mbCancel As Boolean
Const miBAD_COLOR As Long = &HC0C0FF
Const miGOOD_COLOR As Long = &H80000005

Validation added to the OK button:

Private Sub btnOK_Click()
  Dim bValid As Boolean
  Dim sInvalid As String
  
  bValid = True
  mbCancel = False
  
  'validate
  Me.lstX.BackColor = miGOOD_COLOR
  Me.lstY.BackColor = miGOOD_COLOR
  If Me.lstY.ListCount < 1 Then
    bValid = False
    sInvalid = "There must be at least one Y range in any chart."
    Me.lstY.BackColor = miBAD_COLOR
  End If
  Select Case Me.ChartType
    Case -4169, 75, 74
      If Me.lstX.ListCount = Me.lstY.ListCount Then
      ElseIf Me.lstX.ListCount <= 1 And Me.lstY.ListCount > 0 Then
      ElseIf Me.lstY.ListCount = 1 And Me.lstX.ListCount > 0 Then
      Else
        bValid = False
        Me.lstX.BackColor = miBAD_COLOR
        Me.lstY.BackColor = miBAD_COLOR
        sInvalid = sInvalid & "For a " & Me.lstChartTypes.List _
            (Me.lstChartTypes.ListIndex, 1) & " chart, there must be" & vbNewLine
        sInvalid = sInvalid & "- equal numbers of X and Y ranges," & vbNewLine
        sInvalid = sInvalid & "- at most 1 X range and multiple Y ranges, or" _
            & vbNewLine
        sInvalid = sInvalid & "- multiple X ranges and one Y range." & vbNewLine
      End If
    Case Else
      If Me.lstX.ListCount > 1 Then
        bValid = False
        sInvalid = sInvalid & "There can be at most one X range for a " & _
            Me.lstChartTypes.List(Me.lstChartTypes.ListIndex, 1) & " chart." & vbNewLine
        Me.lstX.BackColor = miBAD_COLOR
      End If
  End Select
  
  If bValid Then
    Me.Hide
  Else
    'Me.Repaint
    MsgBox sInvalid, vbExclamation, "Invalid Data Selections"
  End If
End Sub

The Cancel button code:

Private Sub btnCancel_Click()
  mbCancel = True
  Me.Hide
End Sub

This code ensures that clicking the red X closes the form as if the user had clicked the Cancel button:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = 0 Then
    Cancel = True
    btnCancel_Click
  End If
End Sub

Here’s the code for the Default label button:

Private Sub lblbtnDefault_Click()
  Dim iLst As Long
  
  Me.lstX.Clear
  Me.lstY.Clear

  Me.lstX.AddItem 1
  For iLst = 2 To Me.lstChartData.ListCount
    Me.lstY.AddItem iLst
  Next
End Sub

Here is the updated X label button code:

Private Sub lblbtnX_Click()
  Dim iLst As Long
  Dim myChartType As XlChartType
  
  myChartType = Me.ChartType
  For iLst = 1 To Me.lstChartData.ListCount
    If Me.lstChartData.Selected(iLst - 1) Then
      Select Case myChartType
        Case -4169, 75, 74 ' scatter
        Case Else ' not scatter
          Me.lstX.Clear
      End Select
      Me.lstX.AddItem iLst
      Select Case myChartType
        Case -4169, 75, 74 ' scatter
        Case Else ' not scatter
          Exit For
      End Select
    End If
  Next
End Sub

ChartStyle property

Public Property Get ChartStyle() As Long
  With Me.lstChartTypes
    If .ListIndex > -1 Then
      ChartStyle = CLng(.List(.ListIndex, 1))
    End If
  End With
End Property

Code Module MChartFromCSVFile

Here is the complete listing of MChartFromCSVFile:

Option Explicit

'Callback for btnProcessCSV onAction
Sub RX_OpenCSVFileAndPlotData(control As IRibbonControl)
  OpenCSVFileAndPlotData
End Sub

Sub OpenCSVFileAndPlotData()
  Dim sCSVFullName As String, sWbkFullName As String, sWbkFileName As String, _
      sFileRoot As String
  Dim iFile As Long
  Dim bOpenFile As Boolean
  Dim wb As Workbook, wbTest As Workbook, ws As Worksheet, rng As Range, vRng As Variant
  Dim rCht As Range, cht As Chart, srs As Series
  Dim iRows As Long, iCols As Long, iRow As Long, iCol As Long
  Dim sTemp As String
  Dim vChartData As Variant
  Dim bFirstRowHeaders As Boolean
  Dim myChartType As XlChartType, iChartStyle As Long
  Dim vX As Variant, vY As Variant
  Dim iX As Long, iY As Long, iSrs As Long
  Dim nX As Long, nY As Long, nSrs As Long
  Dim bCancel As Boolean
  Dim iMsgBoxResult As VbMsgBoxResult
  Dim frmChartFromCSVFile As FChartFromCSVFile
  
  ' 1. Get CSV file name
  Do
    sCSVFullName = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , _
        "Select a CSV file", , False)
    
    '' check whether user cancels
    If sCSVFullName = "False" Then GoTo ExitProcedure
    
    '' check that file isn't already open
    bOpenFile = False
    For Each wbTest In Workbooks
      If wbTest.FullName = sCSVFullName Then
        bOpenFile = True
        Exit For
      End If
    Next
    If bOpenFile Then
      iMsgBoxResult = MsgBox("Data file is already open. Continue with open file?", _
          vbYesNo + vbQuestion, "File Already Open")
      If iMsgBoxResult = vbYes Then
        Set wb = wbTest
        Exit Do
      End If
    End If
    
    '' ensure that it's a CSV file
    If LCase$(Right$(sCSVFullName, 4)) <> ".csv" Then
      iMsgBoxResult = MsgBox("That doesn't appear to be a CSV file. Continue anyway?", _
          vbYesNo + vbQuestion, "Not a CSV File")
      If iMsgBoxResult = vbYes Then Exit Do
    Else
      Exit Do
    End If
  Loop
  
  ' 2. Open CSV file
  If wb Is Nothing Then
    Workbooks.Open sCSVFullName
    Set wb = ActiveWorkbook
  End If
  
  ' 3. Save as workbook
  sFileRoot = Left$(sCSVFullName, InStrRev(sCSVFullName, ".") - 1)
  sWbkFullName = sFileRoot & ".xlsx"
  iFile = 0
  Do
    '' check for duplicate file (already open)
    bOpenFile = False
    For Each wbTest In Workbooks
      If wbTest.FullName = sWbkFullName Then
        bOpenFile = True
        Exit For
      End If
    Next
    
    '' check for duplicate file (in folder)
    If Not bOpenFile Then
      If Len(Dir(sWbkFullName)) = 0 Then Exit Do
    End If
    
    '' increment file name
    iFile = iFile + 1
    sWbkFullName = sFileRoot & "_" & CStr(iFile) & ".xlsx"
  Loop
  
  wb.SaveAs sWbkFullName, xlWorkbookDefault
  
  ' 4. Parse file
  Set ws = wb.Worksheets(1)
  Set rng = ws.UsedRange
  vRng = rng.Value2
  iRows = rng.Rows.Count
  iCols = rng.Columns.Count
  
  '' info to display: column number, first few rows of column
  ReDim vChartData(1 To iCols, 1 To 2)
  For iCol = 1 To iCols
    vChartData(iCol, 1) = iCol
    sTemp = ""
    For iRow = 1 To 4
      If iRow > iRows Then Exit For
      sTemp = sTemp & vRng(iRow, iCol) & ", "
    Next
    sTemp = Left$(sTemp, Len(sTemp) - 2)
    vChartData(iCol, 2) = sTemp
  Next
  
  '' first row header labels if all cells in first row are text
  For iCol = 1 To iCols
    bFirstRowHeaders = Not IsNumeric(vRng(1, iCol))
    If Not bFirstRowHeaders Then Exit For
  Next
  
  ' 5. Show dialog (get chart type, X values, Y values)
  Set frmChartFromCSVFile = New FChartFromCSVFile
  With frmChartFromCSVFile
    '' pass in information we know
    .ChartData = vChartData
    .FirstRowHeaders = bFirstRowHeaders
    
    .Show
    
    '' check whether user canceled
    bCancel = .Cancel
    If Not bCancel Then
      '' get information selected by user
      myChartType = .ChartType
      iChartStyle = .ChartStyle
      bFirstRowHeaders = .FirstRowHeaders
      vX = .Xcolumns
      vY = .YColumns
    End If
  End With
  Unload frmChartFromCSVFile
  
  If bCancel Then GoTo ExitProcedure
  
  ' 6. Draw chart
  '' define some series parameters
  If IsEmpty(vX) Then
    nX = 0
  Else
    nX = UBound(vX, 1) + 1 - LBound(vX, 1)
  End If
  nY = UBound(vY, 1) + 1 - LBound(vY, 1)
  nSrs = nY
  If nX > nY Then nSrs = nX
  If bFirstRowHeaders Then
    Set rCht = rng.Offset(1).Resize(iRows - 1)
  Else
    Set rCht = rng
  End If
  
  '' select blank cell before inserting chart
  rng.Offset(iRows + 1, iCols + 1).Resize(1, 1).Select
  
  Set cht = ws.Shapes.AddChart.Chart '' Excel 2007+
  ''Set cht = ws.Shapes.AddChart2.Chart '' Excel 2013 only
  
  '' chart type and style
  With cht
    If myChartType <> 0 Then
      .ChartType = myChartType
      If Val(Application.Version) >= 15 Then
        '' Excel 2013 ChartStyles
        If iChartStyle > 0 Then
          .ChartStyle = iChartStyle
        End If
      End If
    End If
    
    '' add series
    For iSrs = 1 To nSrs
      Set srs = .SeriesCollection.NewSeries
      With srs
        ' X values
        If nX = 0 Then
          ' no X values specified
        ElseIf nX = 1 Then
          ' all series share X values
          .XValues = rCht.Columns(CLng(vX(0, 0)))
        Else
          ' each series has unique X values
          .XValues = rCht.Columns(CLng(vX(iSrs - 1, 0)))
        End If
        ' Y values
        If nY = 1 Then
          ' all series share Y values
          .Values = rCht.Columns(CLng(vY(0, 0)))
        Else
          ' each series has unique Y values
          .Values = rCht.Columns(CLng(vY(iSrs - 1, 0)))
        End If
        ' series name
        If bFirstRowHeaders Then
          If nSrs = nY Then
            .Name = "=" & rng.Cells(1, CLng(vY(iSrs - 1, 0))). _
                Address(True, True, xlA1, True)
          ElseIf nSrs = nX Then
            .Name = "=" & rng.Cells(1, CLng(vX(iSrs - 1, 0))). _
                Address(True, True, xlA1, True)
          End If
        End If
      End With
    Next
    
    If iSrs > 1 Then
      .HasLegend = True
    End If
  End With
  
  ' 7. Save file
  ws.Range("A1").Select
  wb.Save
  
ExitProcedure:
  
End Sub

blah

UserForm Module FChartFromCSVFile

Here is the complete listing of FChartFromCSVFile:

Option Explicit

Dim mbCancel As Boolean
Const miBAD_COLOR As Long = &HC0C0FF
Const miGOOD_COLOR As Long = &H80000005

Private Sub btnCancel_Click()
  mbCancel = True
  Me.Hide
End Sub

Private Sub btnOK_Click()
  Dim bValid As Boolean
  Dim sInvalid As String
  
  bValid = True
  mbCancel = False
  
  'validate
  Me.lstX.BackColor = miGOOD_COLOR
  Me.lstY.BackColor = miGOOD_COLOR
  If Me.lstY.ListCount < 1 Then
    bValid = False
    sInvalid = "There must be at least one Y range in any chart."
    Me.lstY.BackColor = miBAD_COLOR
  End If
  Select Case Me.ChartType
    Case -4169, 75, 74
      If Me.lstX.ListCount = Me.lstY.ListCount Then
      ElseIf Me.lstX.ListCount <= 1 And Me.lstY.ListCount > 0 Then
      ElseIf Me.lstY.ListCount = 1 And Me.lstX.ListCount > 0 Then
      Else
        bValid = False
        Me.lstX.BackColor = miBAD_COLOR
        Me.lstY.BackColor = miBAD_COLOR
        sInvalid = sInvalid & "For a " & Me.lstChartTypes.List _
            (Me.lstChartTypes.ListIndex, 1) & " chart, there must be" & vbNewLine
        sInvalid = sInvalid & "- equal numbers of X and Y ranges," & vbNewLine
        sInvalid = sInvalid & "- at most 1 X range and multiple Y ranges, or" _
            & vbNewLine
        sInvalid = sInvalid & "- multiple X ranges and one Y range." & vbNewLine
      End If
    Case Else
      If Me.lstX.ListCount > 1 Then
        bValid = False
        sInvalid = sInvalid & "There can be at most one X range for a " & _
            Me.lstChartTypes.List(Me.lstChartTypes.ListIndex, 1) & " chart." & vbNewLine
        Me.lstX.BackColor = miBAD_COLOR
      End If
  End Select
  
  If bValid Then
    Me.Hide
  Else
    'Me.Repaint
    MsgBox sInvalid, vbExclamation, "Invalid Data Selections"
  End If
End Sub

Private Sub lblbtnDefault_Click()
  Dim iLst As Long
  
  Me.lstX.Clear
  Me.lstY.Clear

  Me.lstX.AddItem 1
  For iLst = 2 To Me.lstChartData.ListCount
    Me.lstY.AddItem iLst
  Next
End Sub

Private Sub lblbtnReset_Click()
  Me.lstX.Clear
  Me.lstY.Clear
End Sub

Private Sub lblbtnX_Click()
  Dim iLst As Long
  Dim myChartType As XlChartType
  
  myChartType = Me.ChartType
  For iLst = 1 To Me.lstChartData.ListCount
    If Me.lstChartData.Selected(iLst - 1) Then
      Select Case myChartType
        Case -4169, 75, 74
        Case Else
          Me.lstX.Clear
      End Select
      Me.lstX.AddItem iLst
      Select Case myChartType
        Case -4169, 75, 74
        Case Else
          Exit For
      End Select
    End If
  Next
End Sub

Private Sub lblbtnY_Click()
  Dim iLst As Long

  For iLst = 1 To Me.lstChartData.ListCount
    If Me.lstChartData.Selected(iLst - 1) Then
      Me.lstY.AddItem iLst
    End If
  Next
End Sub

Public Property Let ChartData(vData As Variant)
  Me.lstChartData.List = vData
End Property

Property Let FirstRowHeaders(bFirstRowHeaders As Boolean)
  Me.chkFirstRowHeaders.Value = bFirstRowHeaders
End Property

Property Get FirstRowHeaders() As Boolean
  FirstRowHeaders = Me.chkFirstRowHeaders.Value
End Property

Public Property Get ChartType() As XlChartType
  With Me.lstChartTypes
    If .ListIndex > -1 Then
      ChartType = CLng(.List(.ListIndex, 0))
    End If
  End With
End Property

Public Property Get ChartStyle() As Long
  With Me.lstChartTypes
    If .ListIndex > -1 Then
      ChartStyle = CLng(.List(.ListIndex, 1))
    End If
  End With
End Property

Public Property Get Xcolumns() As Variant
  Xcolumns = Me.lstX.List
End Property

Public Property Get YColumns() As Variant
  YColumns = Me.lstY.List
End Property

Public Property Get Cancel() As Boolean
  Cancel = mbCancel
End Property

Private Sub UserForm_Initialize()
  Dim vChartTypes As Variant
  
  vChartTypes = ThisWorkbook.Names("ChartTypes").RefersToRange.Value2
  Me.lstChartTypes.List = vChartTypes
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = 0 Then
    Cancel = True
    btnCancel_Click
  End If
End Sub

Add-In with Working Code

You can download the advanced add-in My_CSV_Data_Processor.xlam to see all of this code in one place, and to see how it works.

Using the Add-In

You could open the add-in like a regular workbook, by double-clicking on it in Windows Explorer, dragging it onto the Excel window from Windows, or by using Excel’s File Open dialog. Alternatively you could install it as an add-in, so it is always available:

  • In Excel, click on the File tab or in Excel 2007 the big round Office button in the top left corner
  • Select Options (or Excel Options) from the list
  • Click Add-Ins in the dialog
  • Click the Go button
  • Click Browse and navigate to the folder containing .xlam add-in file
  • Select the .xlam file and click OK a couple times to return to Excel

However the add-in is used, a new button is added to the end of the Data tab on the ribbon. Click the button to launch the code.

Peltier Tech Chart Utility

VBA: A Simple Tool to Open a CSV File and Create Chart with Data Specified by User

In a recent Mr Excel post, a member asked how to convert a CSV File to Excel File with Dynamic Graphing Capability. This is a great topic for a tutorial, but too long for a forum answer.

Problem Statement

The simple statement was this:

  • Convert a CSV file to an Excel workbook
  • Create a chart based on
    • User-specified chart type
    • User-specified columns for X and Y

I expanded on this a little bit:

  • Open a user-selected CSV file
  • Save as an Excel workbook
  • Display dialog for user to select
    • Chart type
    • Columns containing X and Y values for chart
  • Create the desired chart

This is a pretty easy project, which would generally take me half a day or less, depending on other distractions.

Approach

Essentially, I started with this simple VBA procedure, and a similarly simple UserForm (dialog).

Sub OpenCSVFileAndPlotData()
' 1. Get CSV file name
' 2. Open CSV file
' 3. Save as workbook
' 4. Parse file
' 5. Show dialog (select chart type, X values, Y values)
' 6. Draw chart
' 7. Save file
End Sub

My first time through development, I did the least I had to do to make it work. The second time through, I added a bunch of nice embellishments that should make things easier for users to just pick it up and run with it.

For both levels, the following sections have pieces of code describing what is happening, the complete code, and an example workbook.

Building a Simple Solution

Code Workbook

I’m using a macro-enabled Excel workbook named My_CSV_Data_Processor.xlsm. It has a single worksheet names Parameters.

Saved Information: Named Range Containing Chart Types

The worksheet contains a list of chart types that our program will create. These are the simplest and most useful charts built into Excel. The list has two columns, the first being the integer values of Excel’s chart type constants (e.g., xlArea = 1, xlXYScatter = -4169), the second being the chart type names that will appear in the dialog.

The range A2:B12 has been named ChartTypes, so it will be easy for the code to put these values into the dialog.

Starting the Program: Form Control Button

To add a button, the Developer tab must be visible on the ribbon. If you don’t see it, right-click on a blank place in the ribbon, choose Customize the Ribbon. In the list on the right (Main Tabs), click the box in front of Developer.

On the Developer tab, in the Controls group, click the Insert dropdown, then under Form Controls, click on the first icon, Button. Draw a button on the worksheet.

A dialog will pop up asking which VBA procedure to assign to the button; you can revisit this dialog by right-clicking on the button. Select OpenCSVFileAndPlotData and press OK. Then type a caption on the button and resize it as needed.

Code Module

We need to put the code into a regular code module. In the VB Editor, find the workbook’s project in the Project Explorer pane, right click anywhere on it, and choose Insert > Module. When a new code module opens up in the editor, rename it to something useful like MChartFromCSVFile using the Properties pane. The exact name you use isn’t critical, but it should be somewhat descriptive.

On the View menu, click Project Explorer (Ctrl+R) or Properties (F4) if these panes are not visible.

Getting Ready

The first line of the module should be

Option Explicit

If this statement doesn’t appear, type it in, then go to Tools menu > Options, and check the Require Variable Declaration box. While you’re in the Options dialog, uncheck Auto Syntax Check.

After a blank line or two, type Sub and the name of the procedure. The VB Editor automatically skips another line and types End Sub for you:

Sub OpenCSVFileAndPlotData()

End Sub

The main program will be typed between Sub and End Sub. All the variables will be declared at the top using Dim statements. Variables don’t need to be declared at the top of a routine, but VBA offers no just-in-time benefit to declaring them right where you start using them, and I like having them in one place so I can find their declarations easily. In the sections below I will insert a simple Dim statement for the variables used.

1. Get CSV File Name

We can use Excel’s GetOpenFileName to allow the user to select a CSV file from his computer.

  Dim sCSVFullName As String
  sCSVFullName = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , _
      "Select a CSV file", , False)

We’ve specified the CSV file filter, indicated a title for the Open File dialog, and said False to selecting multiple files.

2. Open CSV file

This part is easy, we just need to open the file named in the previous step. Then we’ll set a workbook object variable to this file, so it’s easy to reference later in the code.

  Dim wb As Workbook
  Workbooks.Open sCSVFullName
  Set wb = ActiveWorkbook

3. Save as workbook

We need to remove the file extension from the CSV file name (which includes the file path), which we do by retaining only text up to the last period in the file name. Then we add the Excel workbook file extension, and save the file, specifying the default workbook format.

  Dim sWbkFullName As String, sFileRoot As String
  sFileRoot = Left$(sCSVFullName, InStrRev(sCSVFullName, ".") - 1)
  sWbkFullName = sFileRoot & ".xlsx"
  wb.SaveAs sWbkFullName, xlWorkbookDefault

4. Parse file

Now the file is open, so we’ll extract some information to place in the dialog. We’ll use a two-column display, where the first column has the column number, and the second a simple comma-delimited list of the first few values in that column.

  Dim ws As Worksheet, rng As Range, vRng As Variant
  Dim iRows As Long, iCols As Long, iRow As Long, iCol As Long
  Dim sTemp As String
  Dim vChartData As Variant

  Set ws = wb.Worksheets(1)
  Set rng = ws.UsedRange
  vRng = rng.Value2
  iRows = rng.Rows.Count
  iCols = rng.Columns.Count
  
  '' info to display: column number, first few rows of column
  ReDim vChartData(1 To iCols, 1 To 2)
  For iCol = 1 To iCols
    vChartData(iCol, 1) = iCol ' column number
    sTemp = ""
    For iRow = 1 To 4 ' first 4 values
      sTemp = sTemp & vRng(iRow, iCol) & ", "
    Next
    sTemp = Left$(sTemp, Len(sTemp) - 2) ' remove last comma
    vChartData(iCol, 2) = sTemp
  Next

5. Show dialog

We need to instantiate the UserForm (i.e., load it into memory), pass in the array of column numbers and initial column values, and display the form. At this point, code stops and waits for the user to make his selections and dismiss the form.

When the form is dismissed, we need to get the user’s selections: chart type, whether the CSV file has header labels in the first row, and the columns to be used for X and Y values in the chart. Then we remove the UserForm from memory.

  Dim bFirstRowHeaders As Boolean
  Dim myChartType As XlChartType
  Dim vX As Variant, vY As Variant
  Dim frmChartFromCSVFile As FChartFromCSVFile

  Set frmChartFromCSVFile = New FChartFromCSVFile
  With frmChartFromCSVFile
    '' pass in information we know
    .ChartData = vChartData
    
    .Show
    
    '' get information selected by user
    myChartType = .ChartType
    bFirstRowHeaders = .FirstRowHeaders
    vX = .Xcolumns
    vY = .YColumns
  End With
  Unload frmChartFromCSVFile

6. Draw chart

We need to figure out how to separate the data into separate series, then we need to get the data into the chart.

In XY charts, we let the user create one or more series, where (a) all series share the same set of X values (or no X values if the user didn’t specify them, and Excel will use 1, 2, 3, etc. as X values) and each series has a unique set of Y values, (b) all series share the same set of Y values and each has a unique set of X values, or (c) each series has its own unique sets of X and Y values.

For other chart types, the only relevant combination of X and Y is (a), since Excel uses the same X values for all series regardless of how we specify them. We will deal with this in the Advanced version of this program.

Excel 2013 introduced AddChart2 as an improvement over Excel 2007’s AddChart method. AddChart is hidden in Excel 2013, but AddChart2 will crash Excel 2007 and 2010, so we will use AddChart. In the Advanced program, we will enhance the code to use Excel 2013’s improved default styles while still using the error-free AddChart method.

After adding the chart, we add one series at a time, adding its Y values, X values, and name separately.

  Dim iX As Long, iY As Long, iSrs As Long
  Dim nX As Long, nY As Long, nSrs As Long
  Dim rCht As Range, cht As Chart, srs As Series

  '' define some series parameters
  If IsEmpty(vX) Then
    nX = 0
  Else
    nX = UBound(vX, 1) + 1 - LBound(vX, 1)
  End If
  nY = UBound(vY, 1) + 1 - LBound(vY, 1)
  nSrs = nY
  If nX > nY Then nSrs = nX
  If bFirstRowHeaders Then
    Set rCht = rng.Offset(1).Resize(iRows - 1)
  Else
    Set rCht = rng
  End If
  
  '' select blank cell before inserting chart
  rng.Offset(iRows + 1, iCols + 1).Resize(1, 1).Select
  
  Set cht = ws.Shapes.AddChart.Chart '' Excel 2007+
  
  '' chart type
  With cht
    If myChartType <> 0 Then
      .ChartType = myChartType
    End If
    
    '' add series
    For iSrs = 1 To nSrs
      Set srs = .SeriesCollection.NewSeries
      With srs
        ' X values
        If nX = 0 Then
          ' no X values specified
        ElseIf nX = 1 Then
          ' all series share X values
          .XValues = rCht.Columns(CLng(vX(0, 0)))
        Else
          ' each series has unique X values
          .XValues = rCht.Columns(CLng(vX(iSrs - 1, 0)))
        End If
        ' Y values
        If nY = 1 Then
          ' all series share Y values
          .Values = rCht.Columns(CLng(vY(0, 0)))
        Else
          ' each series has unique Y values
          .Values = rCht.Columns(CLng(vY(iSrs - 1, 0)))
        End If
        ' series name
        If bFirstRowHeaders Then
          If nSrs = nY Then
            .Name = "=" & rng.Cells(1, CLng(vY(iSrs - 1, 0))). _
                Address(True, True, xlA1, True)
          ElseIf nSrs = nX Then
            .Name = "=" & rng.Cells(1, CLng(vX(iSrs - 1, 0))). _
                Address(True, True, xlA1, True)
          End If
        End If
      End With
    Next
  End With

7. Save file

Simple: save changes.

  wb.Save

UserForm

Right click on the workbook’s project in the Project Explorer pane, click Insert > UserForm. When the UserForm appears, give it the name FChartFromCSVFile in the properties pane. This name is descriptive, and is how your code references the UserForm.

UserForm Controls

The Userform contains the following important controls, with the important properties shown below:

btnOK – OK button
Default: True

lstChartType – Listbox with two columns, one hidden.
ColumnCount: 2
ColumnWidths: 0 pt;112 pt
Width: 120
Height: 150

lstChartData – Listbox with two columns, extended multiselect
ColumnCount: 2
ColumnWidths: 42 pt;145 pt
Width: 195
Height: 150
MultiSelect: 2 - fmMultiSelectExtended

lstX, lstY – simple listboxes

lblbtnX, lblbtnY, lblbtnReset – “Label Buttons” or Labels with raised appearance (like regular buttons)
SpecialEffect: 1 - fmSpecialEffectRaised

chkFirstRowHeaders – Checkbox

Plus a few labels that help the user understand the dialog.

UserForm Code

Right click on the UserForm in the Project Explorer, and click on View Code. The Dialog’s code module will appear. Much of this code responds to events on the UserForm, events like clicking buttons. This code also includes properties, which allow the calling procedure to pass information into the UserForm and get information back from it.

As with the regular code module, the UserForm module should begin with

Option Explicit

If a control on the UserForm is to have code associated with it, double-click on the control and the VB Editor will insert a short stub of code into the module. For example, if you double-click on the OK button (named btnOK), the Editor will insert this:

Private Sub btnOK_Click()

End Sub

To make the code useful, we only need to insert our statements within this stub.

When the user clicks the OK button, we want the form to be hidden but remain in memory:

Private Sub btnOK_Click()
  Me.Hide
End Sub

When the UserForm is first loaded, we want the information from the named range ChartTypes to appear in the listbox lstChartTypes. The UserForm_Initialize code runs when the UserForm is loaded, and the code shown here does the magic:

Private Sub UserForm_Initialize()
  Dim vChartTypes As Variant
  
  vChartTypes = ThisWorkbook.Names("ChartTypes").RefersToRange.Value2
  Me.lstChartTypes.List = vChartTypes
End Sub

The button-formatted labels need some simple code attached, so that clicking them will populate the X and Y column lists. Click the lblbtnX label button to populate the lstX listbox:

Private Sub lblbtnX_Click()
  Dim iLst As Long
  
  For iLst = 1 To Me.lstChartData.ListCount
    If Me.lstChartData.Selected(iLst - 1) Then
      Me.lstX.AddItem iLst
    End If
  Next
End Sub

Click the lblbtnY label button to populate the lstY listbox:

Private Sub lblbtnY_Click()
  Dim iLst As Long

  For iLst = 1 To Me.lstChartData.ListCount
    If Me.lstChartData.Selected(iLst - 1) Then
      Me.lstY.AddItem iLst
    End If
  Next
End Sub

Click the lblbtnReset lable button to clear the X and Y listboxes and start over:

Private Sub lblbtnReset_Click()
  Me.lstX.Clear
  Me.lstY.Clear
End Sub

We need the ChartData property to pass the information to display in the lstChartData listbox of the UserForm:

Public Property Let ChartData(vData As Variant)
  Me.lstChartData.List = vData
End Property

We also needproperties to let us extract information from the UserForm: whether the first row of the CSV file has header labels, the selected chart type, and the X and Y columns to be plotted:

Property Get FirstRowHeaders() As Boolean
  FirstRowHeaders = Me.chkFirstRowHeaders.Value
End Property

Public Property Get ChartType() As XlChartType
  With Me.lstChartTypes
    If .ListIndex > -1 Then
      ChartType = CLng(.List(.ListIndex, 0))
    End If
  End With
End Property

Public Property Get Xcolumns() As Variant
  Xcolumns = Me.lstX.List
End Property

Public Property Get YColumns() As Variant
  YColumns = Me.lstY.List
End Property

The Simple Tool

Code Module MChartFromCSVFile

Here is the complete listing of MChartFromCSVFile:

Option Explicit

Sub OpenCSVFileAndPlotData()
  Dim sCSVFullName As String, sWbkFullName As String, sFileRoot As String
  Dim wb As Workbook, ws As Worksheet, rng As Range, vRng As Variant
  Dim rCht As Range, cht As Chart, srs As Series
  Dim iRows As Long, iCols As Long, iRow As Long, iCol As Long
  Dim sTemp As String
  Dim vChartData As Variant
  Dim bFirstRowHeaders As Boolean
  Dim myChartType As XlChartType
  Dim vX As Variant, vY As Variant
  Dim iX As Long, iY As Long, iSrs As Long
  Dim nX As Long, nY As Long, nSrs As Long
  Dim frmChartFromCSVFile As FChartFromCSVFile
  
  ' 1. Get CSV file name
  sCSVFullName = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , "Select a CSV file", , False)

  ' 2. Open CSV file
  Workbooks.Open sCSVFullName
  Set wb = ActiveWorkbook
  
  ' 3. Save as workbook
  sFileRoot = Left$(sCSVFullName, InStrRev(sCSVFullName, ".") - 1)
  sWbkFullName = sFileRoot & ".xlsx"
  wb.SaveAs sWbkFullName, xlWorkbookDefault
  
  ' 4. Parse file
  Set ws = wb.Worksheets(1)
  Set rng = ws.UsedRange
  vRng = rng.Value2
  iRows = rng.Rows.Count
  iCols = rng.Columns.Count
  
  '' info to display: column number, first few rows of column
  ReDim vChartData(1 To iCols, 1 To 2)
  For iCol = 1 To iCols
    vChartData(iCol, 1) = iCol
    sTemp = ""
    For iRow = 1 To 4
      If iRow > iRows Then Exit For
      sTemp = sTemp & vRng(iRow, iCol) & ", "
    Next
    sTemp = Left$(sTemp, Len(sTemp) - 2)
    vChartData(iCol, 2) = sTemp
  Next
  
  ' 5. Show dialog (get chart type, X values, Y values)
  Set frmChartFromCSVFile = New FChartFromCSVFile
  With frmChartFromCSVFile
    '' pass in information we know
    .ChartData = vChartData
    
    .Show
    
    myChartType = .ChartType
    bFirstRowHeaders = .FirstRowHeaders
    vX = .Xcolumns
    vY = .YColumns
  End With
  Unload frmChartFromCSVFile
  
  ' 6. Draw chart
  '' define some series parameters
  If IsEmpty(vX) Then
    nX = 0
  Else
    nX = UBound(vX, 1) + 1 - LBound(vX, 1)
  End If
  nY = UBound(vY, 1) + 1 - LBound(vY, 1)
  nSrs = nY
  If nX > nY Then nSrs = nX
  If bFirstRowHeaders Then
    Set rCht = rng.Offset(1).Resize(iRows - 1)
  Else
    Set rCht = rng
  End If
  
  '' select blank cell before inserting chart
  rng.Offset(iRows + 1, iCols + 1).Resize(1, 1).Select
  
  Set cht = ws.Shapes.AddChart.Chart '' Excel 2007+
  ''Set cht = ws.Shapes.AddChart2.Chart '' Excel 2013 only
  
  '' chart type
  With cht
    .ChartType = myChartType
    
    '' add series
    For iSrs = 1 To nSrs
      Set srs = .SeriesCollection.NewSeries
      With srs
        ' X values
        If nX = 0 Then
          ' no X values specified
        ElseIf nX = 1 Then
          ' all series share X values
          .XValues = rCht.Columns(CLng(vX(0, 0)))
        Else
          ' each series has unique X values
          .XValues = rCht.Columns(CLng(vX(iSrs - 1, 0)))
        End If
        ' Y values
        If nY = 1 Then
          ' all series share Y values
          .Values = rCht.Columns(CLng(vY(0, 0)))
        Else
          ' each series has unique Y values
          .Values = rCht.Columns(CLng(vY(iSrs - 1, 0)))
        End If
        ' series name
        If bFirstRowHeaders Then
          If nSrs = nY Then
            .Name = "=" & rng.Cells(1, CLng(vY(iSrs - 1, 0))).Address(True, True, xlA1, True)
          ElseIf nSrs = nX Then
            .Name = "=" & rng.Cells(1, CLng(vX(iSrs - 1, 0))).Address(True, True, xlA1, True)
          End If
        End If
      End With
    Next
  End With
  
  ' 7. Save file
  wb.Save
  
ExitProcedure:
  
End Sub

UserForm Module FChartFromCSVFile

Here is the complete listing of FChartFromCSVFile:

Option Explicit

Private Sub btnOK_Click()
  Me.Hide
End Sub

Private Sub lblbtnReset_Click()
  Me.lstX.Clear
  Me.lstY.Clear
End Sub

Private Sub lblbtnX_Click()
  Dim iLst As Long
  
  For iLst = 1 To Me.lstChartData.ListCount
    If Me.lstChartData.Selected(iLst - 1) Then
      Me.lstX.AddItem iLst
    End If
  Next
End Sub

Private Sub lblbtnY_Click()
  Dim iLst As Long

  For iLst = 1 To Me.lstChartData.ListCount
    If Me.lstChartData.Selected(iLst - 1) Then
      Me.lstY.AddItem iLst
    End If
  Next
End Sub

Public Property Let ChartData(vData As Variant)
  Me.lstChartData.List = vData
End Property

Property Get FirstRowHeaders() As Boolean
  FirstRowHeaders = Me.chkFirstRowHeaders.Value
End Property

Public Property Get ChartType() As XlChartType
  With Me.lstChartTypes
    If .ListIndex > -1 Then
      ChartType = CLng(.List(.ListIndex, 0))
    End If
  End With
End Property

Public Property Get Xcolumns() As Variant
  Xcolumns = Me.lstX.List
End Property

Public Property Get YColumns() As Variant
  YColumns = Me.lstY.List
End Property

Private Sub UserForm_Initialize()
  Dim vChartTypes As Variant
  
  vChartTypes = ThisWorkbook.Names("ChartTypes").RefersToRange.Value2
  Me.lstChartTypes.List = vChartTypes
End Sub

Workbook with Working Code

You can download the simple workbook My_CSV_Data_Processor.xlsm to see all of this code in one place, and to see how it works.

Advanced Version

The next blog post has a number of enhancements that make things easier for the user and prevent various warning messages. Stay tuned…

 

Peltier Tech Chart Utility

Stick a Shape to a Point in an Excel Chart

This article was inspired by the Mr Excel forum. A member asked Charts: Any way to make a shape stick to a point in a scatterplot, not an absolute location on the chart?

The answer, of course, is Yes, but you have to know the trick, which I’m about to share. Not for the first time.

The Problem – Shapes Won’t Stick to Points in the Chart

Suppose I have some simple X-Y data:

With this data I make a simple X-Y chart:

Now I draw a couple shapes on the chart to highlight two points with larger values than the others.

So far, so good. But when I add some more data…

… the chart’s axes rescale. The points move to stay at the same numerical position along the axis, but the shapes stay where they were originally placed.

So now I have to drag those shapes around whenever the chart axis scales change.

The Solution – Making Shapes Stick to Points

There is actually a pretty simple way to make the shapes stick to the points. The trick is to add a second series to the chart, with data duplicating only the points you want to draw attention to, and use the desired shape as the markers for this series.

Here is the data, with a third column containing the Y values I want to highlight. Also visible is the shape I’ll use.

Here is the original X-Y chart, with blue circles as markers.

Here is the same chart with the second series added, as orange squares that indicate which points will be highlighted.

The trick is simple. Copy the shape, select the series that I want to use the shape as its markers, and paste using a simple Ctrl+V.

Now when I add data, including another highlighted Y value in column C…

… the chart’s axis rescales, but the highlighted points keep their highlighting shape, and the added Y value is highlighted as well.

Enhancements to the Technique

You could use formulas in column C to place values where highlighting is desired, and #N/A errors where it isn’t desired. Below the formula in cell C2 might be

=IF(B2>4,B2,NA())

or better, the threshold for highlighting might be placed into another cell, say E2, to make it easier to modify the highlighting:

=IF(B2>$E$2,B2,NA())

These formulas are then copied down column C as far as needed.

You don’t even need to draw a special shape if all you want to do is circle selected points. Instead of copying the shape and pasting it onto a series, simply format the series to use large circles as markers. Below the circular markers are size 17, with 2.25 point borders and no fill.

Peltier Tech Update

It’s been a busy few months for me, and I haven’t blogged very much lately. I spent a week in Amsterdam at the Excel Summit, where I met a lot of smart Excel users and Excel MVPs for the first time, and caught up with some old friends. I also did some on-site Excel VBA training for a couple companies, and this has reminded me that I want to do more activities like that.

I’ve also spent some time planning and working on new Peltier Tech products and services. Excel 2016 for both Windows and Macintosh will be released by Microsoft in the next several months, and I will release a major upgrade to the Peltier Tech Chart Utility.

Since traveling to training sites is a pain for me and for trainees, I’ve begun work on some live hour-long video training topics. Topics will include “Tricks to Make Excel Charting Less Painful” (based on a session at the Amsterdam Excel conference), “Getting Started with Excel VBA” (based on my recent training workshops), and advanced topics in charting, programming, and programming charts.

Peltier Tech Chart Utility

Removing Recent Colors from Microsoft Excel

Recent Colors

When you customize a color and apply it to an object in Microsoft Office, the color is stored as part of the Office file, and presented in a “Recent Colors” category on the various colors dropdowns. Up to ten of these custom colors are displayed, with the most recent at the left of the list and the oldest moving further to the right until a new color pushes it off the list.

The screenshot below shows three custom colors applied to cells B4:B6 of a worksheet, and where they appear in the color dropdown. The pink color was the latest applied, so it is furthest left. It is highlighted with a thin red outline because it is also the fill color of the active cell.

The numerical and alphanumeric codes in columns C and D are the decimal and hexadecimal representations of these colors. They were generated using the following VBA procedure:

Sub OutputColorInfo()
  Dim c As Range
  
  For Each c In Selection.Cells
    c.Offset(, 1).Value = c.Interior.Color
    c.Offset(, 2).Value = WorksheetFunction.Dec2Hex(c.Interior.Color)
  Next
End Sub

Colors are the combination of the three color channels (red, green, and blue), and each channel can have a value between 0 and 255 (or between 0 and FF in hexadecimal notation). The yellow color above has a blue channel value of 66, and red and green channel values of FF, so the code for this yellow is 66FFFF. Note that VBA presents the channels in BGR order, but it’s still called “RGB”.

A customer asked how to get rid of some but not all recent colors used in Excel. This is not too hard to do, but you should remember that any time you format an object with a new custom color, this new color becomes a new Recent Color.

Where are Recent Colors Stored?

Recent Colors appear in the color dropdowns only when the file they were defined for is the active file in its Office application (in this case, the active workbook in Excel). This means they must be stored somewhere within the document.

The first step in modifying the Recent Colors is to close the Office file. Below we see the workbook “recent colors.xlsm” in a Windows Explorer window.

Office files are really zip files which contain xml and other files within a well-defined directory structure. You can access this structure by simply adding the file extension “.zip” to the file name, and dismissing the warning about changing file extensions.

Double click the zip file to see the top-level structure of the file.

Double click on the “xl” directory within the file.

Finally, copy the “styles.xml” file, navigate out of the zip file to the directory the Office file was saved in, and paste the xml file here.

Open “styles.xml” in your favorite text editor. I use Notepad++, which can apply syntax-based color coding to files it recognizes. This is the entire content of my particular styles.xml file. Note the <mruColors> tag near the end of the file. This contains three <color rgb="blahblah"> tags that contain the definitions of our MRU (Most Recently Used) colors.

Changing Recent Colors

Microsoft Office doesn’t bother making the XML easy to see by using line feeds and spaces, but you can go ahead and apply your own, as I’ve done below.

Note that the RGB values in the <color> tags begin with an extra FF (for the transparency channel), and list the red, green, and blue pairs in the RGB order, not the BGR order produced by our VBA routine above. No big deal, Office uses the XML RGB for its user interface and VBA uses BGR, and they don’t talk to each other, so they don’t confuse each other, they only confuse us.

Delete the <color> tag(s) you don’t want (you could also change the color or add new ones if you can determine the desired RGB values). Save the xml file, and copy it back into the “xl” folder in the zip file. Delete the “.zip” extension you added to the Office file earlier, so it is left with its original Office extension (“.xlsm” for our workbook), and reopen the file.

The screenshot below shows the three colored cells, but only the two non-pink Recent Colors in the dropdown. We have successfully removed the recent color from the workbook’s remembered list, but we haven’t removed the color from any objects we formatted with it.

Note that reapplying the pinkish custom color to any object in the workbook will reestablish it as one of our Recent Colors.

Thanks to PowerPoint MVP Steve Rindsberg for showing me the <mruColors> tag I was too busy to notice the first time I looked. Check out Steve’s PowerPoint FAQ Pages and his PPTools – PowerTools for PowerPoint.

Peltier Tech Chart Utility

Select Data to Display in an Excel Chart With Option Buttons

The Problem

A member of the MrExcel forum wanted to know about using form controls to select columns from a data table to populate a graph. Essentially he wanted his users to be able to choose between two charts, one comparing their scores to district and regional scores, the other comparing their scores to the top 20%. This is a retelling of my suggested approach to his question.

Here is some dummy data I used for his problem, and a chart showing all of the data.

Data and Preliminary Line Chart

My suggested approach will add some columns (F:I) to the existing data range with formulas that show or don’t show data based on which of two option buttons is selected, and my chart will use all of this data. When the formulas don’t show the values, they will not appear in the chart.

The Data

The extended data range is shown below. At first the formula in cell F2 (and copied into the entire range F2:I13) is simply

=B2

To give the user a choice, add two option buttons and label them Chart A and Chart B. Right click on one of them (doesn’t matter which) and select Format Control, and on the Control tab, click in the Cell Link box, and select cell K1. This cell shows which of the buttons is selected, 1 or 2.

Extended Data and Option Buttons

You don’t need to show all of this to the user. You could put the chart and option buttons on the active sheet, and all of the data (and the option button linked cell) can go onto another sheet, and you can hide this other sheet if you want. Or you can place the original data on the same sheet as the chart and option buttons, and the formulas onto another sheet, a hidden sheet if desired. You could replace the two option buttons with one listbox that shows both options; the linked cell works the same way.

The Chart

Set up the chart first, before adjusting the formulas to show the result of the option button selection.

Select A1:A13, then hold Ctrl while selecting F1:I13, and insert a line chart.

Preliminary Line Chart

Delete the legend, because it will always show all series, even if some don’t appear because of the formulas. Instead, label the last point in each series with the series name, and use label text that matches each series color. This is more user-friendly than using a legend, anyway. (I also shortened the axis tick labels so they could all be displayed horizontally.)

Cleaned Up Line Chart

The Formulas

Now adjust the formulas. Keep the formulas in column F the way they are, since this series appears whichever option is selected. The formula in G2 (copied into G2:H13) is

=IF($K$1=1,C2,NA())

This means if the first option button is selected, show the corresponding value, otherwise show #N/A. #N/A isn’t plotted in a line or scatter plot.

Similarly the formula in I2 (and filled into I2:I13) is

=IF($K$1=2,E2,NA())

That is, if the second option button is selected, show the value, otherwise show #N/A.

The Finished Product

Here is the data and chart if the Chart A option button is selected:

Data and Chart 'A'

Here is the data and chart if the Chart B option button is selected:

Data and Chart 'B'

You can download a sample workbook by clicking on this link: MrExcelOptionButtonChart.xlsx.

Peltier Tech Chart Utility

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.