Visual Basic for Applications Excel Terminology

Explanation Have you ever wanted to customize Excel? For example, you might want to display a dialog box asking a user to enter specific data for a worksheet. 
Maybe you want to perform a decision-based task, such as displaying a message when a user activates a worksheet. You can do these things, and more, by programming in Visual Basic for Applications (VBA).

VBA is a programming language that’s part of the Microsoft Office suite. You can use VBA to create programs that work within Microsoft Office applications, such as Microsoft Excel and Microsoft Word. 
VBA is based on the concept of Object-Oriented Programming (OOP). OOP is a programming concept in which all the elements of a program are considered as objects.

VBA makes decision-based tasks easier, because its code can execute automatically in response to an action. For example, you can write VBA code to accept values and prompt the user to enter values if mandatory fields are skipped, ensuring the integrity of your data. 

VBA terminology

Using VBA, you can directly access objects to control and manipulate the behavior of the application. However, before you start coding in VBA, you need to be familiar with some key terms associated with it.

The following table describes some of these terms:

Term and Description

Object 

Any element of an application with specific characteristics and behavior. It’s a component that combines code and data. For example, workbooks, worksheets, ranges, and charts are all objects.

Property 

A characteristic or named attribute of an object. For example, Name and StandardWidth are the properties of the Worksheet object.

Method 

A behavior or action that’s performed by an object. For example, Calculate is a method that updates the values in a Worksheet object containing formulas.

Procedure 

A named sequence of instructions that performs a specific task. For example, you can create a procedure to save the changes made before closing a workbook.

Comment 

A line of text within a procedure, which you use to describe each line of code or the entire procedure. Comments always start with an apostrophe.

Module 

A file in which you can write and edit procedures and other VBA codes.

Collection and container objects

Objects can be either collection objects or container objects. 

A collection object 

is a set of related objects having the same properties. For example, the Worksheets collection object represents all the worksheets in a workbook.

A container object 

contains one or more objects, which may or may not be related. For example, Workbook is a container object that contains the Worksheet objects. The outermost container object is the Application object that contains all other Excel objects, such as Worksheets and Workbooks.

VBA and macros

Macros that you create and record using the Excel interface are written by Excel using VBA code. But there’s a difference between macros and VBA procedures that you write from scratch. 

A macro performs a set of instructions, then stops. For this reason, macros are very useful for automating repetitive tasks.

But macros follow a single execution path. A procedure written in VBA code can evaluate conditions, make decisions based on those conditions, and then alter the flow of execution.

Do it! 

A-1: Discussing VBAQuestions and answers

1 What’s the main advantage of VBA compared to macros?

VBA code can evaluate conditions, make decisions, and then execute automatically in response to those decisions. Recorded macros can only follow a single execution path.

2 Lets say you want to purchase a car. You can select a yellow, red, or black one. Identify the object and its property in this case.

•The car is the object 

•The color is the property

3 What’s a procedure?

A procedure is a named set of instructions that performs specified actions. For example, you can write a procedure that saves changes made before closing an Excel workbook.

4 You want a worksheet name to change automatically when text is entered in a specific cell. Would you record a macro or use VBA? Why?

You’d use VBA, because the code used to change the name would run automatically when the user enters text in a cell.

Read more »

Teknik Perulangan For --- Next

Dalam pemrograman, sering ditemukan pekerjaan yang harus terus menerus diulang. Pekerjaan yang berulang ini dapat menyebabkan :

1.  Pekerjaan menjadi membosankan

2.  Pekerjaan memiliki potensi melakukan kesalahan

3.  Pekerjaan menjadi tidak efisien dan lama.

Untuk melakukan otomatisasi pekerjaan yang dilakukan berulang ulang pada VBA Excel dapat digunakan Teknik Perulangan. Ada dua teknik perulangan yang digunakan yaitu :

1.  Teknik Perulangan For ... Next

2.  Teknik Perulangan Do ... Loop Until

Read more »

Teknik Pencabangan List Box

Penggunaan Teknik Pencabangan If --- Then --- Else --- End If dan Select Case menggunakan data yang harus ditulis dalam Skrip pemrograman. Seandainya user sudah memiliki Data yang akan dipilih, VBA for Excel menyediakan fasilitas berikutnya yaitu : Listbox.

Sebagai contoh : User memiliki data sebagai berikut :

Akan ditampilkan menjadi sebagai berikut :

Langkah langkah membuat programnya adalah sebagai berikut :
1. Dimulai dari membuat Listbox dan selanjutnya membuat tampilan output.
2. Untuk membuat listbox, klik Tab Developer

3. Selanjutnya Klik Insert (yang di dalam Developer)

4. Klik List Box (Form Control)

5. Akan muncul tanda " + " dan letakkan pada posisi yang diinginkan
6. Klik sekali di posisi tersebut, akan muncul gambar di bawah ini :

7. Ubah ukurannya sesuai dengan kebutuhan 
8. Klik kanan akan muncul gambar di bawah :

9. Klik Format Control, akan muncul gambar di bawah ini

10. Pada kotak Input Range klik kotak kecil bagian kanan, akan muncul :

11. Pilih Data yang akan dimasukkan, seperti di bawah ini

12. Blok Kolom B3 sampai dengan B15
13. Tekan <Enter> akan muncul gambar di bawah ini

14. Selanjutnya pada kotak input Cell link klik kotak kecil sebelah kanannya
15. Letakkan kursor di sel C2
16. Akan muncul gambar di bawah ini

17. Tekan <Enter> akan muncul gambar di bawah ini

18. Klik  <Ok> akan muncul gambar di bawah ini

19. Klik salah satu pilihan dari daftar di atas, akan muncul angka di sel C2

20. Ketika yang dipilih baris ke 3, maka sel C2 muncul tulisan angka 3

21. Selanjutnya dibuat format yang lebih bagus agar lebih menarik misalnya :
22. Kolom C3, C4 dan C5 dibuat secara otomatis dengan fungsi VLOOKUP, misalnya :

Kolom C3 = VLOOKUP(C2,'Data Materi Pembelajaran'!A3:D14,2)

Kolom C4 = VLOOKUP(C2,'Data Materi Pembelajaran'!A3:D14,3) 

Kolom C5==VLOOKUP(C2,'Data Materi Pembelajaran'!A3:D14,4)

23. Hasilnya seperti berikut ini 

Ditulis oleh : Kustiyadi, ST

Read more »

How to use the forms controls on a worksheet in Excel

Summary

Microsoft Excel provides several controls for dialog sheets that are useful for selecting items from a list. Examples of controls are list boxes, combo boxes, spin buttons, and scroll bars.

For more information about form controls in Excel, see Overview of forms, form controls, and ActiveX controls on a worksheet.

More information

The following methods show how to use list boxes, combo boxes, spin buttons, and scroll bars. The examples use the same list, cell link, and Index function.

Enable the Developer tab

To use the form controls in Excel 2010, you have to enable the Developer tab. To do this, follow these steps:

1. Click File, and then click Options.
   
   
   
2. Click Customize Ribbon in the left pane.
   
   
   
3. Select the Developer check box under Main Tabs on the right, and then click OK.

To use the forms controls in Excel 2007, you must enable the Developer tab. To do this, follow these steps:

1. Click the Microsoft Office Button, and then click Excel Options.
   
   
   
2. Click Popular, select the Show Developer tab in the Ribbon check box, and then click OK.
   
   
   

Set up the list, the cell link, and the index

1. In a new worksheet, type the following items in the range H1:H20:

         H1 : Roller Skates
         H2 : VCR
         H3 : Desk
         H4 : Mug
         H5 : Car
         H6 : Washing Machine
         H7 : Rocket Launcher
         H8 : Bike
         H9 : Phone
         H10: Candle
         H11: Candy
         H12: Speakers
         H13: Dress
         H14: Blanket
         H15: Dryer
         H16: Guitar
         H17: Dryer
         H18: Tool Set
         H19: VCR
         H20: Hard Disk
        

2. In cell A1, type the following formula:
   =INDEX(H1:H20,G1,0)

List box example

1. To add a list box in Excel 2010 and Excel 2007, click the Developer tab, click Insert in the Controls group, and then click List Box Form (Control) under Form Controls.
   
   
   
   
   
   To add a list box in Excel 2003 and in earlier versions of Excel, click the List Box button on the Forms toolbar. If the Forms toolbar is not visible, point to Toolbars on the View menu, and then click Forms.
2. Click the worksheet location where you want the upper-left corner of the list box to appear, and then drag the list box to where you want the lower-right corner of the list box to be. In this example, create a list box that covers cells B2:E10.
3. In the Controls group, click Properties.
   
   
   
4. In the Format Object window, type the following information, and then click OK.
   1. To specify the range for the list, type H1:H20 in the Input range box.
   2. To put a number value in cell G1 (depending on which item is selected in the list), type G1 in the Cell link box.
      
      Note The INDEX() formula uses the value in G1 to return the correct list item.
   3. Under Selection type, make sure that the Single option is selected.
      
      Note The Multi and Extend options are only useful when you are using a Microsoft Visual Basic for Applications procedure to return the values of the list. Note also that the 3-D shading check box adds a three-dimensional look to the list box.
      
      
      
5. The list box should display the list of items. To use the list box, click any cell so that the list box is not selected. If you click an item in the list, cell G1 is updated to a number that indicates the position of the item that is selected in the list. The INDEX formula in cell A1 uses this number to display the item's name.

Combo box example

1. To add a combo box in Excel 2010 and Excel 2007, click the Developer tab, click Insert, and then click Combo Box under Form Controls.
   
   
   
   
   
   To add a combo box in Excel 2003 and in earlier versions of Excel, click the Combo Box button on the Forms toolbar.
2. Click the worksheet location where you want the upper-left corner of the combo box to appear, and then drag the combo box to where you want the lower-right corner of the list box to be. In this example, create a combo box that covers cells B2:E2.
   
   
   
3. Right-click the combo box, and then click Format Control.
   
   
   
4. Type the following information, and then click OK:
   1. To specify the range for the list, type H1:H20 in the Input range box.
   2. To put a number value in cell G1 (depending on which item is selected in the list), type G1 in the Cell link box.
      
      Note The INDEX formula uses the value in G1 to return the correct list item.
   3. In the Drop down lines box, type 10. This entry determines how many items will be displayed before you have to use a scroll bar to view the other items.
      
      Note The 3-D shading check box is optional. It adds a three-dimensional look to the drop-down or combo box.
      
      
      
5. The drop-down box or combo box should display the list of items. To use the drop-down box or combo box, click any cell so that the object is not selected. When you click an item in the drop-down box or combo box, cell G1 is updated to a number that indicates the position in the list of the item selected. The INDEX formula in cell A1 uses this number to display the item's name.

Spin button example

1. To add a spin button in Excel 2010 and 2007, click the Developer tab, click Insert, and then click Spin Button under Form Controls.
   
   
   
   
   
   To add a spinner in Excel 2003 and in earlier versions of Excel, click the Spinner button on the Forms toolbar.
2. Click the worksheet location where you want the upper-left corner of the spin button to appear, and then drag the spin button to where you want the lower-right corner of the spin button to be. In this example, create a spin button that covers cells B2: B3.
3. Right-click the spin button, and then click Format Control.
   
   
   
4. Type the following information, and then click OK:
   1. In the Current value box, type 1.
      
      This value initializes the spin button so that the INDEX formula will point to the first item in the list.
   2. In the Minimum value box, type 1.
      
      This value restricts the top of the spin button to the first item in the list.
   3. In the Maximum value box, type 20.
      
      This number specifies the maximum number of entries in the list.
   4. In the Incremental change box, type 1.
      
      This value controls how much the spin button control increments the current value.
   5. To put a number value in cell G1 (depending on which item is selected in the list), type G1 in the Cell link box.
      
      
      
5. Click any cell so that the spin button is not selected. When you click the up control or down control on the spin button, cell G1 is updated to a number that indicates the current value of the spin button plus or minus the incremental change of the spin button. This number then updates the INDEX formula in cell A1 to show the next or previous item.
   
   The spin button value will not change if the current value is 1 and you click the down control, or if the current value is 20 and you click the up control.

Scroll bar example

1. To add a scroll bar in Excel 2010 and Excel 2007, click the Developer tab, click Insert, and then click Scroll Bar under Form Controls.
   
   
   
   
   
   To add a scroll bar in Excel 2003 and in earlier versions of Excel, click the Scroll Bar button on the Forms toolbar.
2. Click the worksheet location where you want the upper-left corner of the scroll bar to appear, and then drag the scroll bar to where you want the lower-right corner of the scroll bar to be. In this example, create a scroll bar that covers cells B2:B6 in height and is about one-fourth of the width of the column.
   
   
   
3. Right-click the scroll bar, and then click Format Control.
   
   
   
4. Type the following information, and then click OK:
   1. In the Current value box, type 1.
      
      This value initializes the scroll bar so that the INDEX formula will point to the first item in the list.
   2. In the Minimum value box, type 1.
      
      This value restricts the top of the scroll bar to the first item in the list.
   3. In the Maximum value box, type 20. This number specifies the maximum number of entries in the list.
   4. In the Incremental change box, type 1.
      
      This value controls how many numbers the scroll bar control increments the current value.
   5. In the Page change box, type 5. This value controls how much the current value will be incremented if you click inside the scroll bar on either side of the scroll box).
   6. To put a number value in cell G1 (depending on which item is selected in the list), type G1 in the Cell link box.
      
      
      
      
      
      Note The 3-D shading check box is optional. It adds a three-dimensional look to the scroll bar.
5. Click any cell so that the scroll bar is not selected. When you click the up or down control on the scroll bar, cell G1 is updated to a number that indicates the current value of the scroll bar plus or minus the incremental change of the scroll bar. This number is used in the INDEX formula in cell A1 to show the item next to or before the current item. You can also drag the scroll box to change the value or click in the scroll bar on either side of the scroll box to increment it by 5 (the Page change value). The scroll bar will not change if the current value is 1 and you click the down control, or if the current value is 20 and you click the up control.

Read more »