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
The other book about the Office ribbon is RibbonX For Dummies
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 SubThis 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 SubThe 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
Loop2. 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 If3. 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, xlWorkbookDefaultAll 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
Next5. 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 ExitProcedure6. 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 With7. Save file
Let’s select cell A1 before saving, just to satisfy our obsession with an orderly worksheet.
ws.Range("A1").Select
wb.SaveUserForm
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 = &H80000005Validation 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 SubThe Cancel button code:
Private Sub btnCancel_Click()
mbCancel = True
Me.Hide
End SubThis 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 SubHere’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 SubHere 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 SubChartStyle property
Public Property Get ChartStyle() As Long
With Me.lstChartTypes
If .ListIndex > -1 Then
ChartStyle = CLng(.List(.ListIndex, 1))
End If
End With
End PropertyCode 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 Subblah
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 SubAdd-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.
Tania says
Thank you for your posts – very useful!
Please, have you got any example of dynamic charts on a VBA userform (Excel 2010)? I am trying to develop an interactive tool so users can change the charts on userform (set time period, set type of main error (+ all its suberrors will be displayed), then comment it through a textbox on the form. Then print the report as a word document.
Nico says
Thank you for your posts – very very useful!
But I need your help/advice.. if a few row of csv file is not a number, how to reflect this situation into the Code Module MChartFromCSVFile?
Jon Peltier says
Nico –
What does the data look like (could you post a representative few rows)? How would you like the code to treat the nonnumeric data?