Using MS Excel Object in Visual Basic

<Previous lesson | Table of Content | Content of this Chapter | Next lesson>

To use MS Excel object in VB, first you need to add Excel object as your project reference. Then you can refer the Excel object as Excel.Application. In the following project you will learn the basic manipulation Excel object. The purpose is just to introduce how to control and automate your work in Excel using VB. Download the code here and make sure you follow the procedure below step by step (especialy step no 8 on how to use). Click here to know the explanation how it work.

1. Open a new Standard Exe VB Project
2. In the VB menu, select Project > References… > in the dialog scroll down and check the Microsoft Excel Object Library and press OK
3. Change the Caption property of the Form into Excel Controller.
4. In the general declaration section, type the following code

Option Explicit
Private appExcel As Excel.Application
Private wBook As Workbook
Private mySheet As Worksheet
Private myChart As Chart
Private myRange As Range
Dim myMatrix
Dim sheetNo As Integer
Dim startRow As Integer, startCol As Integer
Dim endRow As Integer, endCol As Integer

5. Drag and drop one Frame control, and then add 9 Command buttons outside the frame and one command button inside the frame. Drag and drop 5 Textboxes, 5 Label control and 2 option button inside the frame.
6. Change the properties of the controls as in table below. Make sure that you type the Name and Caption properties correctly.

   Control	Name	Caption	Text	Value	Location
   Text1	txtSheetNo	-	1	-	Inside Frame1
   Text2	txtStartRow	-	1	-	Inside Frame1
   Text3	txtStartCol	-	2	-	Inside Frame1
   Text4	txtEndRow	-	4	-	Inside Frame1
   Text5	txtEndCol	-	5	-	Inside Frame1
   Option1	optPutMatrix	-	-	TRUE	Inside Frame1
   Option2	optGetMatrix	-	-	-	Inside Frame1
   Label1	Label1	Sheet No	-	-	Inside Frame1
   Label2	Label2	Start Row	-	-	Inside Frame1
   Label3	Label3	Start Col	-	-	Inside Frame1
   Label4	Label4	End Row	-	-	Inside Frame1
   Label5	Label5	End Col	-	-	Inside Frame1
   Command1	cmdPutGetMatrix	Put	-	-	Inside Frame1
   Command2	cmdNew	New	-	-	Form1
   Command3	cmdOpen	Open	-	-	Form1
   Command4	cmdHideShow	Hide	-	-	Form1
   Command5	cmdSave	Save	-	-	Form1
   Command6	cmdQuitExcel	Quit Excel	-	-	Form1
   Command7	cmdCreateMatrix	Create Matrix	-	-	Form1
   Command8	cmdChart	Chart	-	-	Form1
   Command9	cmdEvaluateExpression	Evaluate	-	-	Form1
   Command10	cmdQuit	Quit	-	-	Form1
   Frame1	Frame1	Get or Put Matrix	-	-	Form1

7. Copy and paste the code below:
   

   Private Sub Form_Load()
   ' set the name
   Set appExcel = New Excel.Application
   appExcel.Application.Visible = True
   End Sub

   
   Private Sub cmdNew_Click()
   Set wBook = appExcel.Workbooks.Add
   Set mySheet = appExcel.Sheets(1)
   End Sub

Private Sub cmdOpen_Click()
' Dialog to open spreadsheet from Excel
Dim fName
Dim sheetNo As Integer

fName = appExcel.GetOpenFilename
sheetNo = Val(txtSheetNo.Text)
If fName <> False Then
Set wBook = appExcel.Workbooks.Open(fName)
Set mySheet = appExcel.Sheets(1) ' set the name
End If
End Sub

Private Sub cmdHideShow_Click()
If cmdHideShow.Caption = "Hide" Then
appExcel.Visible = False
cmdHideShow.Caption = "Show"
Else
appExcel.Visible = True
cmdHideShow.Caption = "Hide"
End If
End Sub

Private Sub cmdSave_Click()
On Error GoTo errHandle

appExcel.DisplayAlerts = True
appExcel.Save
Exit Sub

errHandle:
MsgBox Err.Description
End Sub

Private Sub cmdQuitExcel_Click()
On Error Resume Next
appExcel.DisplayAlerts = False ' don't ask to save
appExcel.Application.Quit ' quit Excel
Set appExcel = Nothing ' release reference to object
End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
Call cmdQuitExcel_Click 'remove excel too if user quit this program
End Sub

Private Sub optGetMatrix_Click()
txtEndRow.Visible = True
txtEndCol.Visible = True
cmdPutGetMatrix.Caption = "Get"
End Sub

Private Sub optPutMatrix_Click()
txtEndRow.Visible = False
txtEndCol.Visible = False
cmdPutGetMatrix.Caption = "Put"
End Sub

Private Sub cmdPutGetMatrix_Click()
Call GetValuesFromText
If cmdPutGetMatrix.Caption = "Put" Then
Call PutMatrix
Else ' Get matrix
myMatrix = GetMatrix
End If
appExcel.Sheets(sheetNo).Activate
End Sub

Private Sub GetValuesFromText()
' Put this values into private variables
sheetNo = Val(txtSheetNo.Text)
startRow = Val(txtStartRow.Text)
startCol = Val(txtStartCol.Text)
endRow = Val(txtEndRow.Text)
endCol = Val(txtEndCol.Text)
End Sub

Private Function GetMatrix()
' Get matrix from Excel

On Error GoTo errHandle
GetMatrix = mySheet.Range(mySheet.Cells(startRow, startCol), mySheet.Cells(endRow, endCol))
Exit Function

errHandle:
MsgBox Err.Description
End Function

Private Sub PutMatrix()
' Put matrix in a sheet of Excel
Dim shtActive
Dim i As Integer, j As Integer
Dim endRow As Integer, endCol As Integer
On Error GoTo errHandle

endRow = UBound(myMatrix, 1) + startRow - 1
endCol = UBound(myMatrix, 2) + startCol - 1
Set shtActive = appExcel.Sheets(sheetNo)
For i = startRow To endRow
For j = startCol To endCol
shtActive.Cells(i, j).Value = myMatrix(i - startRow + 1, j - startCol + 1)
Next j
Next i
Exit Sub

errHandle:
MsgBox Err.Description
End Sub

Private Sub cmdCreateMatrix_Click()
Dim row As Integer, col As Integer
Call GetValuesFromText ' Put textboxes values into private variables
ReDim myMatrix(1 To endRow - startRow + 1, 1 To endCol - startCol + 1)
For row = 1 To UBound(myMatrix, 1)
For col = 1 To UBound(myMatrix, 2)
myMatrix(row, col) = Rnd * 100
Next col
Next row
End Sub

Private Sub cmdChart_Click()
' create new chart
Set myChart = appExcel.Charts.Add

'Determine the size of the range and store it.
Set myRange = mySheet.Range(mySheet.Cells(startRow, startCol), mySheet.Cells(endRow, endCol))

'Format the chart.
With myChart
' Specify chart type.
.ChartType = xlBarOfPie

' Set the range of the chart.
.SetSourceData Source:=myRange, PlotBy:=xlColumns

' Specify that the chart is located on a new sheet.
.Location Where:=xlLocationAsNewSheet
End With
Call myChart.Move ' to other workbook
End Sub

Private Sub cmdEvaluateExpression_Click()
Dim expression
On Error GoTo errHandle
expression = InputBox("Enter mathematics expression " & vbCr & _
"e.g. Sin(Cos(log(23)+30))", "Math Evaluator", "Sin(Cos(log(23)+30))")
MsgBox expression & " = " & appExcel.Evaluate(expression), , "Result of Your Expression"
Exit Sub

errHandle:
MsgBox "I think you mistype your expression. Please try again", , "Error"
End Sub

Private Sub cmdQuit_Click()
End
End Sub

8. Run the program: to use it, start with clicking New button, Create the matrix, Put into Excel, Get the matrix, Change the Sheet number, then Put again the matrix in the new sheet. After that you Chart it. Error may happen if you do not follow how to use in this step.
9. Try the Evaluation button and put any expression on it.

How it works

You can also trace how the program works by pressing F8 every time. Here is the explanation on how the program works. The private variables in the declaration section, I separated into two parts: the first part with keyword Private, and the second part with keyword Dim. The two keywords have the same meaning in this case because both of them declare variables into private variables. The first part of variables has Excel Objects data type (i.e. Application, Workbook, worksheet, and Range) while the second part are VB data type (i.e. Integer, variant). The separation is only for you to distinguish the two data types.

When the program starts, it calls Form_Load event procedure where we set the shorter name for Excel Application object and at the same time, we put keyword New to create this object. Since we want to see the Excel, we set it to be visible. Though you can see the Excel, it does not contain any workbook. Thus you need to specify the workbook and the worksheet of the Excel object. When user click New command button, cmdNew_Click event procedure is called and we add new workbook and specify the name of the worksheet. Similarly, if user presses the Open command button, cmdOpen_Click event procedure is requested to call Open dialog in Excel. In here user will point to the existing Excel workbook.

In cmdHideShow_Click event procedure, you will see a switch between hide and show of the Excel. We use the Caption property of the command button to indicate the state of hide or show. We assume that at the first time, the Excel application is visible.

When user click the Save button, cmdSave_Click event procedure is called to save the Excel. Display alert property is set to True to show any message (such as replace existing file) when user save the data. The code “On error Goto ErrHandle” will direct any error into label errHandle below. Make sure to exit the sub procedure before putting any label. The error handle will simply show description on why the error happens.

In cmdQuitExcel_Click() event procedure, we let the Excel to quit without saving or any warning, and we release the reference to Excel object. This procedure is referred by Form_QueryUnload. The Form_QueryUnload event procedure will be called when user ended our program. Without this event procedure, suppose the user forget to close the Excel, the Excel program will remain after the Excel controller quit. If the Excel is in invisible state, it will make the matter worst.

The optGetMatrix_Click and optPutMatrix_Click event procedure will simply switch the Caption of the command button and the visibility of the two textboxes. Put matrix procedure, does not need EndRow and EndCol because it can be detected from the matrix size. The cmdPutGetMatrix_Click will direct to call either PutMatrix or GetMatrix procedures and activate the sheet number specified. The procedure GetValuesFromText is called by more than one procedure. Rather than typing the same code again and again, we separate this code into single procedure and call with a single line. Function GetMatrix will return Range object from Excel. The procedure PutMatrix is to demonstrate how we can put the values of a matrix into Excel worksheet using Cells (row, col). Shorter codes using Range is also possible but please remembers that Range is an object and is not a simple two-dimensional matrix.

The procedure cmdCreateMatrix_Click is creating matrix with the size as the user specified in the text boxes, contain random number.

In the cmdChart_Click event procedure, we add new chart object, then specify the range and chart it for some type of chart, then move the chart into other workbook.

The cmdEvaluateExpression_Click demonstrate the ability to use Excel functions directly from VB using appExcel.Evaluate(expression).

The last command button is to terminate our Excel Controller program. Before ending the session, it will automatically call the Form_QueryUnload event procedure.


<Previous lesson | Table of Content | Content of this Chapter | Next lesson>

See also:

How to automate Microsoft Excel from Visual Basic