Visual Basics for Microsoft Excel: how to creat Macros by - Iloka Benneth Chiemelie

DISCUSSION

Visual Basic for Excel

Visual Basic for Excel is the simple programming language that can be used within Excel to develop macros and complex programs.

How to create Macro

1.  Open Visual Basic Editor by go to Tools...Macro...Visual Basic Editor or just simply press the [Alt] and [F11] keys at the same time.

2. In the Insert menu on top of the Visual Basic Editor, select Module to open the Module window (code window).

3.  In the Module window, type the following:
Sub showMessage()
    MsgBox "Hello World!"
End Sub

4. Click the Run button,, press [F5], or go to Run..Run Sub/UserForm to run the program

5.  The message box pops up with the "Hello World!" greeting.

This is your first VBA programmer.

How to record Macro

Microsoft Excel has a build-in macro recorder that translates your actions into VBA macro commands.  After you recorded the macro, you will be able to see the layout and syntax.  Before you record or write a macro, plan the steps and commands you want the macro to perform.  Every actions that you take during the recording of the macro will be recorded - including the correction that you made.

In this example, we will record a macro that sets the cell background color to light yellow.  To record the macro, follow the steps below:

1.  Select Record New Macro... under Tools...Macro

2.  In the Record Macro dialog box, type "SetBackgroundColor" in the Macro Name textbox to set the macro name.  Leave all other option by default then click the Ok button.  This will start the macro recording.

3.  In the Background Color Panel, select the Light Yellow color box.  This action will set the background of the current cell (A1) in light yellow color.

4.  To stop the macro recording, click the Stop button (the navy blue rectangle) on the Macro Recorder toolbar.

Now you have recorded a macro that set cell background to light yellow.

How to see the recorded syntax

The recorded macro is ready for use.  Before we run the macro, let's look into the syntax.

1. To load the Visual Basic Editor, press [Alt] and [F11] at the same time.  (Remember from our prior lesson?)  The Visual Basic Editor comes up.

2.  Expand the Modules folder in the Project Explorer by clicking on the plus (+) sign.

3.  Double click the Module1 folder to see the sub routine (marco).

As the figure shows, the name of the sub routine is "SetBackgroundColor".   The color index for the light yellow is 36.  The background pattern is solid

How to run the recorded Macro

In our prior example, we created the "Hello World!" macro.  We ran the macro within the Visual Basic Editor.  This time we will run the recorded macro in the worksheet.

1.  On any worksheet, select from D3 to E6.

2.  Run the recorded macro by select Tools...Macro...Macros... or press [Alt] and [F8] at the same time.

3.  The Macro dialog box displayed.  Since there is only one macro in the module, by default the only macro, SetBackgroundColor is selected.  Click the Run button  to run the macro. 

4.   Cells D3 to E6 now have light yellow background color.

CONTROL STRUCTURES

Rank: this is used to rank the companies according to their performances and revenue. The code is =rank (number, ref,[return order])

This is a function that performs the activity of comparing all the numbers entered and putting them in a descending order from the highest to the lowest.

VlookUp: this is used to compare company’s sales against their production cost and weigh whether they made a profit or loss.

The formula is =vlookup(number, table, with a $ sign for drag)

USER DEFINED PROCEDURE, FUNCTIONS AND FORMULAS

First Macro

Sub Macro1()

'

' Macro1 Macro

' max number of employees

'

' Keyboard Shortcut: Ctrl+l

'

    Range("Y23").Select

    ActiveCell.FormulaR1C1 = "=MAX(R[-15]C[-7]:R[84]C[-6])"

    Range("Y23").Select

End Sub 

This is a Maximum (=Max) formula used to determine the company that employees the highest number of workers. It compares all the companies and the number of employees they have, then returns the company that has the highest number of employee. This function can be carried out with the ctrl and l key pressed together. This function is essential because it reduces the inconveniences of performing the max formula function.

Second Macro

Sub Macro2()

'

.

' Macro2 Macro

'

' Keyboard Shortcut: Ctrl+s

'

    ActiveWindow.SmallScroll Down:=3

    Range("AC21:AE21").Select

    ActiveCell.FormulaR1C1 = "=MIN(R[-13]C[-11]:R[86]C[-10])"

    Range("AC22").Select

End Sub

This is a Minimum (=Min) formula used to determine the company that employees the lowest number of workers. It compares all the companies and the number of employees they have, and then returns the company that has the lowest number of employee. This function can be carried out with the ctrl and s key pressed together. This function is essential because it reduces the inconveniences of performing the min formula function

Third Macro

Sub Macro6()

'

' Macro6 Macro

'

' Keyboard Shortcut: Ctrl+k

'

    Selection.Font.Bold = True

    With Selection.Font

        .Color = -10477568

        .TintAndShade = 0

    End With

End Sub

This is a a user defined function that changed the text color to blue and bold the text as well. The user defined procedure for this function is by clicking the ctrl and k key together. It is also designed to give ease of use and minimize time wasting in bolding text. This was basically used to change the company names to blue color and bold them as well

OUR SYSTEM

Our project was built using Microsoft window vista. But we believe it can be viewed in windows XP as well. The Microsoft Excel is 2007 version.

• Our system comprises of one workbook and 3 work sheets.
• All our information were typed and not copied.
• They information are accurate as of the time we obtained it.
• The information where obtained from fortunes magazine and Google.
• Our system meets the minimum requirement of three Macros and two control structure.

CONCLUSION

Microsoft Excel is one of the programming languages that make our life easy today. It ranges from coordinated recording and documentation of data and answering our numerical question that formally took us hours to answer in seconds.

Our project is a clear example of how useful Microsoft Excel is in our modern society. It is believed that after going through the following documentation and our project, new users can start to appreciate the importance of Microsoft Excel while old users will understand how to perform more function.

This project has guided us in many ways towards improving on our knowledge of Microsoft Excel and understanding the importance of its application in our society as well as learning new application codes.

The above documentation includes a combination of well researched documents and accurate procedures for performing new function.

REFERENCES 

Fortune magazine company ranking 2009, online, accessed on 11-10-2009.                   http://money.cnn.com/magazines/fortune/global500/2009/

Microsoft Official website 2009, online, accessed on 11-10-2009

                             www.microsoft.com/excel/2007