VB and Excel
Visual Basic code can be written to interact with Microsoft Excel in a variety of ways. The most common technique is to write VB code that is embedded in or associated with an Excel worksheet. Such code can be used to perform a variety of tasks.
One way of adding VB code to a spreadsheet follows.
From Excel, click on Tools, then Macro, then Visual Basic Editor:
That sequence will bring up the VB Editor, where you click Insert, then Module.
The Module window on the right is where you type your VB code. As usual, your code will be part of a sub, so provide a sub name. If you do, when you try to save the worksheet you will be asked for a macro name, but it will not bracket your code correctly.
The code below is used to demonstrate a sub. The worksheet for which is was written had data in cells b1, b2, and b3. Read the comments for details.
Sub sumSub( )
Worksheets("Sheet1").Activate
' Copy the value in each cell to the cell in the next column
' and double the new value.
Worksheets("Sheet1").Range("c1").Value = _
Worksheets("Sheet1").Range("b1").Value * 2
Worksheets("Sheet1").Range("c2").Value = _
Worksheets("Sheet1").Range("b2").Value * 2
Worksheets("Sheet1").Range("c3").Value = _
Worksheets("Sheet1").Range("b3").Value * 2
' Put a formula into two cells, using the Formula property
Worksheets("Sheet1").Range("b5").Formula = "=sum(b1:b3)"
Worksheets("Sheet1").Range("c5").Formula = "=sum(c1:c3)"
' Increase the
column widths
Worksheets("Sheet1").Columns("b").ColumnWidth
= 10
Worksheets("Sheet1").Columns("c").ColumnWidth
= 10
End Sub
---------------------
An Excel worksheet can also be created from within VB. The following commands declare an object variable to hold the worksheet, and then uses the CreateObject command to create an Excel spreadsheet using Excel.Sheet:
' Declare an object variable to hold the object
' reference. Dim as Object causes late binding.
Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")
Once the spreadsheet object has been created, it must be made visible, just as we saw in control arrays, by setting the Visible property to True:
ExcelSheet.Application.Visible = True
After that, values and formulas can be placed in the spreadsheet cells, the spreadsheet can be saved, and then closed.
The following program is an adaptation of your Mortgage program that writes the Mortgage screen to an Excel file and saves it.
Option Explicit
'************************************************************
'* CIS 220 -- Program 1a
'*
'* Mortgage Calculator-- Calculates how many payments must be
'* made to pay off a loan.
'************************************************************
Private Sub cmdCalculate_Click()
' Declares all of the variables
Dim loan As Double, paySize As Double
Dim percent As Single, years As Integer, payments As Integer
Dim factor As Double, totalPayments As Integer
Dim decIntPerPayment As Single, decIntPerYr As Single
'Read variables, and convet percentage if necessary
If (Val(txtPercent.Text) < 1) Then
percent = txtPercent.Text * 100
Else
percent = txtPercent.Text
End If
loan = txtLoan.Text
years = txtYears.Text
payments = txtPayment.Text
decIntPerYr = percent / 100 ' Gets decimal interest per year
decIntPerPayment = decIntPerYr / payments ' Gets dec
int per pmt
totalPayments = years * payments ' Gets the num of
ttl pmts
factor = (1 - (1 + decIntPerPayment) ^ (-totalPayments)) / decIntPerPayment
paySize = loan / factor ' Gets amt to be paid for each
pmt
txtPaySize.Text = Format$(paySize, "Currency") ' Displays
pmt amt
cmdQuit.SetFocus
Call Excel_Setup(loan, percent, years, payments, paySize)
End Sub
Private Sub cmdQuit_Click()
End ' Terminate program
End Sub
'************************************************************************
'* Displays data on excel spreadsheet
'************************************************************************
Private Sub Excel_Setup(ByVal loan As Double, ByVal percent As Single, ByVal years As Integer, ByVal payments As Integer, ByVal paySize As Double)
' Declare an object variable to hold the object
' reference. Dim as Object causes late binding.
Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")
' Make Excel visible through the Application object.
ExcelSheet.Application.Visible = True
' Place headings in specified cells of the sheet.
ExcelSheet.Application.Cells(4, 1).Value = "Loan Amount"
ExcelSheet.Application.Cells(5, 1).Value = "Interest Percent Per Year"
ExcelSheet.Application.Cells(6, 1).Value = "Loan Duration in Years"
ExcelSheet.Application.Cells(7, 1).Value = "Number of Payments per
Year"
ExcelSheet.Application.Cells(9, 1).Value = "Monthly Payment Amount"
' Place data in
proper cells of spreadsheet
ExcelSheet.Application.Cells(4, 2).Value = FormatCurrency(loan, 2)
ExcelSheet.Application.Cells(5, 2).Value = Format(percent / 100, "0%")
ExcelSheet.Application.Cells(6, 2).Value = years
ExcelSheet.Application.Cells(7, 2).Value = payments
ExcelSheet.Application.Cells(9, 2).Value = FormatCurrency(paySize, 2)
' Increase the column
widths
ExcelSheet.Application.Columns("a").ColumnWidth = 25
ExcelSheet.Application.Columns("b").ColumnWidth = 12
' To right justify
text:
'ExcelSheet.Application.cells(2, 2).HorizontalAlignment = xlRight
' To change font
characteristics
' ExcelSheet.Application.cells(2, 2).Font.Bold = True
' Save the sheet to \test.xls directory.
ExcelSheet.SaveAs App.Path & "\TEST.XLS"
' Close Excel with the Quit method on the Application object.
ExcelSheet.Application.Quit
' Release the object variable.
Set ExcelSheet = Nothing
End Sub
Another Approach
Option Explicit
Private Sub cmdLoad_Click( )
Dim excel_app As Excel.Application
Dim row As Integer
Screen.MousePointer = vbHourglass
DoEvents
' Create the Excel application.
Set excel_app = CreateObject("Excel.Application")
' Uncomment this line to make Excel visible.
excel_app.Visible = True
' Create a new spreadsheet.
excel_app.Workbooks.Add
' Insert data into Excel.
With excel_app
.Range("A1").Select
.ActiveCell.FormulaR1C1 = "Title"
.Columns("A:A").ColumnWidth = 35
With .Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 5
End With
.Columns("B:B").ColumnWidth = 13
.Range("B1").Select
.ActiveCell.FormulaR1C1 = "ISBN"
With .Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 5
End With
row = 2
.Range("A" & Format$(row)).Select
.ActiveCell.FormulaR1C1 = "'Advanced Visual Basic Techniques"
.Range("B" & Format$(row)).Select
.ActiveCell.FormulaR1C1 = "'0-471-18881-6"
row = row + 1
.Range("A" & Format$(row)).Select
.ActiveCell.FormulaR1C1 = "'Ready-to-Run Visual Basic Algorithms"
.Range("B" & Format$(row)).Select
.ActiveCell.FormulaR1C1 = "'0-471-24268-3"
row = row + 1
.Range("A" & Format$(row)).Select
.ActiveCell.FormulaR1C1 = "'Custom Controls Library"
.Range("B" & Format$(row)).Select
.ActiveCell.FormulaR1C1 = "'0-471-24267-5"
row = row + 1
.Range("A" & Format$(row)).Select
.ActiveCell.FormulaR1C1 = "'Bug Proofing Visual Basic"
.Range("B" & Format$(row)).Select
.ActiveCell.FormulaR1C1 = "'0-471-32351-9"
row = row + 1
.Range("A" & Format$(row)).Select
.ActiveCell.FormulaR1C1 = "'Ready-to-Run Visual Basic Code Library"
.Range("B" & Format$(row)).Select
.ActiveCell.FormulaR1C1 = "'0-471-33345-X"
row = row + 1
.Range("A" & Format$(row)).Select
.ActiveCell.FormulaR1C1 = "'Visual Basic Graphics Programming"
.Range("B" & Format$(row)).Select
.ActiveCell.FormulaR1C1 = "'0-471-35599-2"
' Save the results.
.ActiveWorkbook.SaveAs FileName:=txtExcelFile, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
End With
' Comment the rest of the lines to keep
' Excel running so you can see it.
' Close the workbook without saving.
excel_app.ActiveWorkbook.Close False
' Close Excel.
excel_app.Quit
Set excel_app = Nothing
Screen.MousePointer = vbDefault
MsgBox "Ok"
End Sub
Private Sub Form_Load( )
Dim file_path As String
file_path = App.Path
If Right$(file_path, 1) <> "\" Then file_path = file_path & "\"
txtExcelFile.Text = file_path &
"Books.xls"
End Sub