Introduction about VBA in MS Excel.
Introduction
Microsoft Excel is a spreadsheet application that provides simple to advanced means of creating and managing any type of list. To even make it more powerful and production oriented,MS Exel, like all members of the Microsoft Office family, ships with a computer language and a programming environment.Visual Basic for Applications (VBA) is a computer language based on Microsoft Visual Basic. It allows you to write code that can automatically perform actions on a workbook, its worksheets, their columns, their rows, and/or their cells. To write the necessary code, Microsoft Excel also ships with a programming environment called Microsoft Visual Basic that provides all the regular features you need to automate actions on a worksheet.
Before writing VBA code, you must first open Microsoft Excel. To write code, you must open Microsoft Visual Basic. To do that, on the main menu, you can click Tools -> Macro-> Visual Basic Editor. This would open the Microsoft Visual Basic programming environment:
Starting Microsoft Excel
1. Start Microsoft Excel
2. To save the workbook, on the Standard toolbar, click the Save button
3. Locate your Microsoft Excel exercises folder and display it in the Save In combo box
4. Change the File Name to Pizza Store and click Save
5. On the main menu of Microsoft Excel, click Tools -> Macro -> Visual Basic Editor
Using VBA
When it opens, like any regular Windows application, Microsoft Visual Basic displays a title bar in the top section. The title bar follows the same description as Microsoft Excel. Under the title bar, the application displays a menu, followed by a Standard toolbar. They also follow the same descriptions as Microsoft Excel.
By default, Microsoft Visual Basic displays three main windows. In the top-left section, the Project window shows a list of the code segments that are available to your worksheet. It is usually positioned to the top-left section but you can move it to another section of the screen. To move it, click its title bar and drag it away it from there:
To put the window back where it was previously, you can double-click its title bar.
The Properties window, in the bottom-left section of the screen shows the characteristics of an object that is selected. Like any other window, to move the Properties window from its position, drag its title bar:
The main area of Microsoft Visual Basic uses a gray background. This area is gray because, in reality, MSVB is a multiple document interface (MDI) that can be used to display various windows at the same time. At times, this gray area will be occupied with other windows.
Modules
A module is a blank window that resembles a piece of paper on which you write code. When you use Microsoft Excel and work on a worksheet, a default module is automatically allocated for it, whether you use it or not. You can also create a module that is independent of any worksheet.
To create a module, on the main menu of Visual Basic, you can click Insert -> Module.
Creating a Module
1. On the main menu of Microsoft Visual Basic, click Insert -> Module
2. Notice that a blank window with a blinking caret appears
The Immediate Window
To help you test code, Microsoft Visual Basic provides a special window called the Immediate Window. This window is not automatically available when MSVB opens. If you want to use it, you must request it.
To display the Immediate Window, on the main menu of Microsoft Visual Basic, you can click View -> Immediate Window.
Displaying the Immediate Window
1. To display the Immediate Window, on the main menu of Visual Basic, click View -> Immediate Window
2. Notice that a window with an Immediate title bar appears at the bottom with a blinking caret
A computer application, such as those that run on Microsoft Windows, is equipped with objects called Windows controls. These are the objects that allow a person to interact with the computer.
The Form
The primary control used on most applications is called a form. Because Microsoft Excel is not primarily a programming environment, it doesn't automatically provide a form. If you want to use a form in your application or to associate a form with your workbook, on the main menu of Visual Basic, you can click Insert -> UserForm. This would add a form to your application.
The form is primarily used as a platform on which you add other controls. Such a control is called a container. By itself, a form is not particularly useful: You can add other objects to it.
When you create or add a form, a module is also automatically created for it. To access the module associated with a form, you can right-click the form and click View Code.
Creating a Form
1. To display Visual Basic again, on the Taskbar, click the Microsoft Visual Basic button
2. To create a form, on the main menu, click Insert -> UserForm
3. To access its associated module, right-click the form and click View Code
4. To return to the form, on the main menu, click Window and click the menu item that has (UserForm)
Introduction to Windows Controls
The main objects used to help a person interact with the computer are Windows controls. There are two main ways you can access these objects when working with Microsoft Excel.
If you are working in Microsoft Excel, besides the columns, rows, and cells, you can add or position some Windows controls on the worksheet. To do that, on the main menu of MS Excel, you can click View -> Toolbars -> Control Toolbox. This would display the Control Toolbox. By default, the Control Toolbox appears in two columns. To change its display, you can click and drag one of its borders:
If you are working on a form, Visual Basic provides its own Toolbox equipped with a few controls.
Accessing Windows Controls
1. In the Visual Basic Window, notice that a Toolbox appears next to the form
2. To return to Microsoft Excel, on the Taskbar, click Microsoft Excel
3. To display the Toolbox, on the main menu, click View -> Toolbars -> Control Toolbox
The Control Toolbox and Additional Objects
The Control Toolbox in Microsoft Excel provides the most regularly used controls. These controls are enough for any normal Microsoft Excel workbook you are developing. Besides the objects in the Control Toolbox, other controls are left out but are still available. To use one or more of these left out controls, on the Control Toolbox, click the More Controls button. This would open a window:
You can scroll up and down in the window to locate the desired control. If you see a control you want to use, click it and click the worksheet.
In Visual Basic, to access more controls, on the main menu, you can click Tools -> Additional Controls...
The Names of Controls
Every control on the tool boxes has a specific name. You may be familiar with some of these controls. If you are not sure, you can position the mouse on a control and a tool tip will come up. In our lessons, we will use the tool tip of a control to name it. The names we will use are:
Control Design
To use one of the controls on the Control Toolbox, you can click it. If you then simply click the worksheet, the control would be added where you clicked and with some default dimensions. If you want, instead of clicking and releasing the mouse, you can click and drag. This allows you to "draw" the control and give the dimensions of your choice. If the control has already been added but you want it to assume different dimensions, you can click it to select it and then drag one of its border handles.
The above technique is used to add one control at a time. If you want to add the same control again, you must click it in the Toolbox and click the worksheet or the form again. If you plan to add the same control many times, in the Toolbox, double-click it and click the worksheet or the form as many times as necessary. When you have reached the desired number of copies of that control, to dismiss it, in the Toolbox, click the same control again or another control. If you are working in Visual Basic, to dismiss the control, in the Toolbox, click the Select Objects button.
Accessing Windows Controls
1. To add a control to the worksheet, on the Toolbox, click the CommandButton button and click a cell (any cell, no need for precision at this time)
2. To add another control, on the Toolbox, click the Combo Box button and click somewhere on the worksheet away from the previously added button
3. On the Taskbar, click Microsoft Visual Basic to return to it
4. On the Toolbox, click the CommandButton and click somewhere in the top-left section of the form (no need for precision at this time)
5. On the Toolbox, click the ComboBox and click somewhere in the middle-center section of the form (no need for precision at this time)
6. On the Toolbox, click the CheckBox and click somewhere in the lower-right section of the form (no need for precision at this time)
7. Return to Microsoft Excel
Control Selection: Single Control Selection
After you have added a control to a worksheet or a form, in order to perform any type of configuration on the control, you must first select it. Sometimes you will need to select a group of controls. To select a control on a worksheet, first, on the Control Toolbox, click the Design Mode button
. If you are working in Visual Basic, to select a control, click it on the form.
A control that is selected indicates this by displaying 8 small squares, also called handles, around it. Here is an example:
In Visual Basic, between the border handles, the control is surrounded by dotted rectangles.
Selecting Controls
1. Position the mouse on CommandButt and click. Notice that you are able to select the button
2. On the Control Toolbox, click the Exit Design Mode button
3. On the worksheet, click the button again
4. Click the combo box. Notice that, this time, you cannot select the controls
5. To return to controls to edit mode, on the Control Toolbox, click the Design Mode button
6. Return to Microsoft Visual Basic
Multiple Control Selection
To select more than one control on the form, click the first. If you are working on a worksheet, press and hold Shift. If you are working in Visual Basic, press and hold Ctrl. Then click each of the desired controls. If you click a control that should not be selected, click it again. After selecting the group of controls, release either Shift or Ctrl that you were holding:
If you are working in Visual Basic, another technique you can use to select various controls consists of clicking on an unoccupied area on the form, holding the mouse down, drawing a fake rectangle, and releasing the mouse:
Every control touched by the fake rectangle or included in it would be selected:
Selecting and Using Various Controls
1. On the form, click one of the controls
2. Press and hold Ctrl
3. Click one of the other controls
4. Release Ctrl
Control Deletion
If there is a control on your form or your worksheet but you don't need it, you can remove it. To delete a control, first select it and then press Delete. You can also right-click a control and click Cut.
To remove a group of controls, first select them, then press Delete or right-click the selection and click Cut.
Deleting Controls
1. While still in Microsoft Visual Basic, press Ctrl + A to select all controls
2. Press Delete to remove them
3. Return to Microsoft Excel
4. Right-click the combo box and click Cut
5. To close Microsoft Excel, press Alt, F, X
6. When asked whether you want to save the changes, press Enter
Writing Coding
Code Indentation
Indentation is a technique that allows you to write easily readable code. It consists of visually showing the beginning and end of a section of code. Indentation consists of moving your code to the right side.
The easiest and most common way to apply indentation consists of pressing Tab before typing your code. By default, one indentation, done when pressing Tab, corresponds to 4 characters. This can be automatically set using the Tab Width text box of the Editor property page in the Options dialog box. To change it, on the main menu of Microsoft Visual Basic, you can click Tools -> Options and click the Editor tab:
If you don't want the pressing of Tab to be equivalent to 4 characters, change the value of the Tab Width text box to a reasonable integer value and click OK. Otherwise, it is (strongly) suggested that you keep to its default of 4 characters.
Comments
A comment is a piece of text in code that would not be considered when reading your code. As such, a comment can be written any way you want.
In Visual Basic, the line that contains a comment can start with a single quote. Here is an example:
Private Sub Form_Click()
' This line will not be considered as part of the code
End Sub
Alternatively, you can start a comment with the Rem keyword. Anything on the right side of rem, Rem, or REM would not be read. Here is an example:
Private Sub Form_Click()
' This line will not be considered as part of the code
Rem I can write anything I want on this line
End Sub
Comments are very useful and you are strongly suggested to use comments regularly.
Line Continuation
You will sometimes need to expand your code on more than two lines. This happens regularly if you are writing an expression that involves many entities that must belong to a group.
To continue a piece of code from one line to the next, type an empty space followed by an underscore symbol, then continue your code on the next line.
Note: To many for Excel Application such as use for Do Oracle Finance reporting. Fast, Easy, Drill down and use for Cpk Sofware for MS Excel
ไม่มีความคิดเห็น:
แสดงความคิดเห็น