Friday, August 20, 2010

VBA Introduction from Youtube Video.

Installed VBA for Microsoft Excel 2007

If you are using Microsoft Excel 2007, its hurt when by default Microsoft are not included this feature during normal Office 2007 installation. Please follow below step to install it by yourself. You may need the original installation CD to perform this.

How to install VBA for Excel 2007 from your Office CD

1. From the Start button on your bottom left of the screen goto "Control Panel".

2. From "Control Panel" goto "Add or Remove Programs".

3. Select to your "Microsoft Office 2007" and click to "Change" button.

4. Select "Add or Remove Features" and click on "Continue"

5. Installing option windows prompt out and click on "Office Shared Features".

6. Once "Office Shared Features" selected, goto "Visual Basic for Applications" enable and "Continue".

7. Wait until your computer complete installing VBA.
8. VBA is installed and you need to show the "Developer" item on your toolbar. Click on office Toolbar on the upper left corner of your Excel application. See below Image.

9. Then select "Excel Option" as below.

10. Now Excel Option window prompt out and make sure all item on below picture are checked.
11. Now open "Microsoft Excel 2007" and you will notice a new Menu shows "Developer" on the screen. As shown on below is the features for Office 2007 VBA.

Change "Macro Security" level
On "Developer" menu you'll notice a "Macro Security" menu click this to set your security as below.

Tuesday, August 17, 2010

Understanding VBA Window

View of default VBA Windows or VBE.

Windows version Excel's supports programming  for Visual Basic for Applications (VBA), which is apart of Visual Basic. It allows spreadsheet manipulation that is impossible to do with standard Excel techniques.

Programmers may write code on VBA window known as Visual Basic Editor (VBE). Instead of writing a script, users also may do some Excel recording activity and the recording will be wrote on macro's recorder.

VBE Toolbar
Try to memorize the icon as you will use them to execute and control your code later.

The Project Explorer

Normally, it is located at the top-left of the VBE. Used to navigate on the elements in the programming environment, you may double click on the object to switch. Other than worksheet object, Project explorer also may included with form object or modules:

Watch Window.

Watch window works during execution activity where users may select some parameters to monitor their value keep on every line program executed.

VBE important features for Programming.
Now from the default VBE window go to View and your may see the other Drop-down menu. You may select or deselect the windows by yourself for your understanding about VBE but there are 3 important sub-window that may help us during programming session. Project Explorer, Watch Window and Properties Window.

The use of watch window already being explain before, now select the property window.
When the propery window loaded in your VBE, double click on each items in project explorer. You'll realize once the item selected, the property window also will change. So we can conclude here, property window works to show the user about the object properties during design time process.

Monday, August 16, 2010

Understanding EXCEL Window

Excel’s basic unit is the worksheet, every worksheet divided to 256 columns and 65,536 rows. The intersection of rows and columns is called "cell", so each worksheet contains 256 x 65,536 = 16,777,216 cells. Excel uses the A1 cell to refer to columns, rows, and cells:
  • Columns are designated from A to IV.
  • Rows are numbered from 1 to 65536.
*cells A1, A2, Z1234, IV65536 and etc is called the cell address.

But this is not I want to explain here, my main thing is to start using VBA application in Excel.

VBA is stand for Visual Basic for Application. If you are using Excel 2003, you're lucky when VBA is included in normal office package but if you are using Excel 2007 you have to do add/ins from your original installation CD. View here for add/ins procedure.

How to start using VBA?
For shortcut you may press ALT-F11 or follow on below picture to open VBA. Remember this is in Microsoft Excel Windows.
Once you complete the task VBA window will appear.