Microsoft Excel Development: VBA Examples on John's Finance Page

VBA and Excel!!!
I have now updated this page to include sample code, program, and examples of integrating Visual Basic 6 with Visual Basic for Applications. Send me any feedback on this page, as it would be greatly appreciated.

Ok I should tell you that to make any of these examples run you will need to have at least one or a combination of Excel 97 or greater, and Visual Basic 6. That said, I think that you will learn a lot from these examples. I am working on a Excel/VBA/Monte Carlo Simulation as I know that many of you are here looking for just that....so stay tuned! To Get Started: Right now I suggest you open Microsoft Excel, and once a spreadsheet appears, press ALT and F11 at the same time to enter into the Visual Basic Editor. Click on Insert file menu and select User Form. It is through this form that most of my examples come from. If you don't already see a tool box floating on your screen, select View and toolbox and you will be able to place the objects like textboxes, and buttons onto your form. Let's get started.

Message Boxes Etc.

Where do I begin?

By far the most simplest task to do in both visual basic or in VBA for Excel is the message box.

The syntax in its simplest form is this:

MsgBox "text in message box", vbprompt, "box title"

But you can also have the box display another objects property, like say this:

MsgBox TextBox1.Value, vbprompt, TextBox2.Value

Where on your form you have placed 2 textboxes, and a command button. So try it! If you want a simple message use quotes, or if you would like to have a dynamic message box, use other object's properties. Once you start typing, Visual Basic will prompt you with all of the available options...it is sooo easy. Here is an example, and how you can do it:

Place two textboxes named on a form.
Place a command button on the form
Double click on the command button and insert this code between the Sub and End Sub points.
Press F5 to run the form, and type any text you want into the two textboxes
Press the command button to view your message box.

Experiment using message boxes for different purposes. I have used them for such things as:

warnings that an illegal procedure was done
information on how to proceed
help buttons

Linking forms to spreadsheets

Where do I begin?

Probably the most asked question and poorly explained answers occur for this topic. Baisically it is very easy to do this, but be aware that any text linked is entered to the sheet, and that might mean letters and funny symbols. To get around this you can later use my validation code for numbers. But here is the simplified syntax for any spreadsheet reference linked to a text box:

Range("A1").Value = TextBox1.Value

Or you can do things like selecting a range, and printing it. Here is an a example:

Range("A1:A10").Select
Selection.PrintOut Copies:=1

Or how about using a message box, that tests the data on a spreadsheet. Try this:

On a form place one textbox and a command button
Double click on the text box and place this code in between the Sub and End Sub
Double click on the commnad button and place this code
Press F5 to run the form, and enter a number into the textbox, press the command button and your number is greater than 50 you will get a message box prompt, or if it is less than 50 nothing will happen.

Vailidate data in a text box for numbers only

Where do I begin?

This is a tricky one, but once you see the code it should appear easy at least to manipulate it. You would want to validate a text box entry for numbers for many reasons, but the biggest reason is probably so it won't mess up your code or spreadsheet. This example will validate an entry for numbers, given a defined range, and allow certain tasks to be executed if the test returns True. Here is what you do:

Place two textboxes called TextBox1 and TextBox2 onto a form
Click on the General tab, and select declarations as this picture shows:

Copy this code into the delcarations.
Double click on TextBox1 and delete the Sub TextBox1_Change and End Sub so your screen is clear
Then copy this code into the cleared area
Press F5 to run the code, and enter a leter into TextBox1 and tab to the next TextBox...you should be alerted that letters are not allowed.

VB Yes or No

Where do I begin?

Using this example you can ask the user a question, and based on a yes or no response, do a given task or procedure. So lets's say that you have to ask the user if they have 5 dollars in their pocket, if they answer yes a textbox says COOL, and if they answer NO, a textbox says DAMN. Here is what you do?

Place a textbox on a form called TextBox1
Place a command button called CommandButton1 also on the form.
Double click on the commandbutton and enter this code in between the Sub CommandButton1_Click and End Sub
Press F5 to have the form run, and then click the button see how this code works.

Activate a program on the hard drive

Let's say you want to open Word from wthin Excel to make a few notes about how your day has been going...this example will show you how. This is the syntax:

Shell("Filepathname", delayinseconds i.e. 1)

Here is what you do:

Place a command button on a form called CommandButton1
Double click on the button
Enter this code, and change the link to any file on your hard drive. I have placed this changable area in italics.

Solver Addin Syntax

This is probably the most powerfull Add-In in Excel, and will come in handy for pricing options and generally solving very complex equations that must be solved by trial and error (IRR anyone?). O.k. here is an example and I will explain the references according to footnote number. Click here for an explanation.

Or try this example:

On a spreadsheet (Sheet 1) Enter the following:

Enter the number 100 into cell D1
Enter the formula =A1*B1 into the cell C1
Enter the number 5 in cell B1

Now for the Visual Basic:

First you have to make REFERENCE to the solver. Do this by selecting Tools, References, and then locate and select the Solver Add-In. If it is not available you will have to install it using your Office 97/2000 disk. Here is what you should see:

Then place a command button called CommandButton1 onto a form, and double click on it, then place this code into the area between Sub CommandButton1_Click and End Sub.
Press F5 to run the form, and click on the button
Close the form and you should see the number 20 in cell A1

Change Excels Appearance and Properties

You can change the properties of the Excel Window using the name

Application.property

Any property available will be prompted to you as soon as you type the period after application in a macro. Try this:

Place a command button onto a form, and double click on the button.
Type the word application followed by a period and view all of the properties you can change. Try this as an example: Application.Caption = "Excel is a Cool Program!"
Press F5 to run the form, and then click your button to have the caption display in the top left of the Excel screen.

Other things you can do include making tool bars invisible, minimizing Excel to the taskbar. Like this:

Application.WindowState = xlminimized

Excel will prompt you as you type your code!!!

Please note I have so much more that will be posted soon!!!!!

Double click on the command button you inserted, and copy this code into the area between Sub CommandButton1_Click() and End Sub:


This ensures that blank text boxes are not allowed 
If TextBox1.Value = "" Or TextBox2.Value = "" Then 

    MsgBox "Nothing entered!", vbExclamation, "Test Message" 

Else 

    ' this is what should happen if there is text in the boxes 
    ' and remember that any words following a " ' " are excluded from VB code 
    MsgBox TextBox1.Value, vbExclamation, TextBox2.Value 

End If

Go back up

Double click the textbox and insert this code:

Range("A1").Value = TextBox1.Value
Go back up

Double click on the command button and insert this code between the subs:


If Range("A1").Value > 50 then 

    ' do nothing!!! 

Else 

MsgBox "You just entered " & Range("A1").Value & " into textbox1 and cell A1", vbinfo, "Cool Huh?" 

End If

Go back up

Copy this code into the declarations, and you can change the minimum and maximum values


    Const CurMax = 1000000 
    Const CurMIn = 0 

    Dim uNum As Variant

Go back up

Double click on TextBox1, delete all text that you see, and paste all of this code into the cleared area:


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) 

        ' Define the textbox you want to test 
        uNum = TextBox1.Value 

'If uNum is blank than do nothing 
If uNum = "" Then 

'If it is not blank check to see a number was entered 
Else 

        If IsNumeric(uNum) Then 

            ' Now if the entry is within the defined ranges 
            If uNum >= CurMIn And uNum <= CurMax Then 

                ' if it is within the defined ranges 
		' do something with the number like this 
                Range("A1").Value = TextBox1.Value 

            End If 

   ' If the entry is less than the defined 
   ' range tell the user and enter 0 as the default entry 
   If uNum < CurMIn Then 

   MsgBox "The value entered is less than zero", vbExclamation, "Validation" 
   TextBox1.Value = 0 
   Range("A1").Value = TextBox1.Value 

        Else 

            ' if the number was greater than the 
	    ' defined range tell the user and  enter 0 as a default 
            If uNum > CurMax Then 

            MsgBox "Higher the max value", vbExclamation, "Validation" 
            TextBox1.Value = 0 
            Range("A1").Value = TextBox1.Value 

            End If 

        End If 

    Else 

    ' if the entry was an alpha character 
    ' tell the user what went wrong and correct the error 
    MsgBox "Alphabetic values are not allowed", vbExclamation, "Validation" 
    TextBox1.Value = 0 
    Range("A1").Value = TextBox1.Value 
    End If 

End If 

End Sub

Go back up

Copy this code into the area between Sub CommandButton1_Click and End Sub


'Declare the varaibles for this macro 
Dim Msg, Style, Title, fivebucks 

' Define here your message,  and the title of the yes/no question prompt 
Msg = "Do have have 5 bucks in your pocket?" 
Style = vbYesNo 
Title = "Five buck question!!!" 

    ' Let's call this question fivebucks since it sounds cool 
    fivebucks = MsgBox(Msg, Style, Title) 

' So now the message "Do you have..." will be diplayed 
' and the user has a yes or no choice 

If Validate = vbYes Then 

    TextBox1.Value = "COOL" 

        Else 

    TextBox1.Value = "DAMN" 

End If

Go back up

Copy this in between the Sub CommandButton1_Click and End Sub

On Error Resume Next 
MyAppID = Shell("C:\Program Files\Microsoft Office\Office\word.exe", 1) 
AppActivate MyAppID

Go back up

Try to read and understand the code that follows, and the footnotes are at the end of the actual code:


SolverReset¹

SolverOk² SetCell:="$A$1", MaxMinVal:=1³, ValueOf:="0", ByChange:="$A$1"
SolverAdd CellRef:="$C$1", Relation:=2⁴, FormulaText:="$D$1"⁵
SolverOk SetCell:="$A$1", MaxMinVal:=1, ValueOf:="0", ByChange:="$A$1"
SolverSolve⁶ UserFinish:=True⁷

Firstly reset your solver so that any old entries are delted
The first line calls the solver to open
The first $A$1 refers to the area where the answer will be displayed, and the second $A$1 refers to the cell that the solver will use as the missing link in a given equation to solve
The second line refers to the boundaries of the equation. In this case it states that the $B$1 has to equal $B$2. There are three relations in the solver, they are: less than is represented by 1, equal to is represented by 2, and greater than is represented by 3.
This value could be a cell reference, as in this case $B$2, or it could be an actual number like 5
This forces the solver to solve
This command closes the solver so that it is never acutually seen by the user, what it acually does is select ok within visual basic for the user.

Go back up

Copy this code into the area between Sub CommandButton1_Click and End Sub


SolverReset

    SolverOk SetCell:="$A$1", MaxMinVal:=1, ValueOf:="0", ByChange:="$A$1"
    SolverAdd CellRef:="$C$1", Relation:=2, FormulaText:="$D$1"
    SolverOk SetCell:="$A$1", MaxMinVal:=1, ValueOf:="0", ByChange:="$A$1"
    SolverSolve UserFinish:=True

Go back up