Tuesday, May 19, 2009

Excel VBA Objects

Most programming languages today deal with objects, a concept called object oriented programming. Although Excel VBA is not a truly object oriented programming language, it does deal with objects. VBA object is something like a tool or a thing that has certain functions and properties, and can contain data. For example, an Excel Worksheet is an object, cell in a worksheet is an object, range of cells is an object, font of a cell is an object, a command button is an object, and a text box is an object and more.

An Excel VBA object has properties and methods. Properties are like the characteristics or attributes of an object. For example, Range is an Excel VBA object and one of its properties is value. We connect an object to its property by a period(a dot or full stop). The following example shows how we connect the property value to the Range object.

Example 8.1

Private Sub CommandButton1_Click()
Range("A1:A6").Value = 10
End Sub

In this example, by using the value property, we can fill cells A1 to A6 with the value of 10. However, because value is the default property, it can be omitted. So the above procedure can be rewritten as

Example 8.2

Private Sub CommandButton1_Click()
Range("A1:A6")= 10
End Sub

To learn more about Excel VBA, log on to VBA tutorial site:


Saturday, May 9, 2009

Excel VBA

VBA is the acronym for Visual Basic for Applications. It is an integration of the Microsoft's event-driven programming language Visual Basic with Microsoft Office applications such as Microsoft Excel, Microsoft Word, Microsoft PowerPoint and more. By running Visual Basic IDE within the Microsoft Office applications, we can build customized solutions and programs to enhance the capabilities of those applications.

Among the Visual Basic for applications, Microsoft Excel VBA is the most popular. There are many reasons why we should learn VBA for Microsoft Excel, among them is you can learn the fundamentals of Visual Basic programming within the MS Excel environment, without having to purchase a copy of Microsoft Visual Basic software. Another reason is by learning Excel VBA; you can build custom made functions to complement the built-in formulae and functions of Microsoft Excel. Although MS Excel has a lot of built-in formulae and functions, it is still not enough for certain complex calculations and applications. For example, it is very hard to calculate monthly payment for a loan taken using Excel's built-in formula, but it is relatively easy to program a VBA for such calculation. This book is written in such a way that you can learn VBA for MS Excel in an easy manner, and everyone shall master it in a short time!

You can program Excel VBA in every version of Microsoft Office, including MS Office 97, MS Office2000, MS Office2002, MS Office2003 and MS Office XP. The reason VBA is needed is due to the limitations in using the built-in functions of MS Excel and macro recording. By using VBA, you can build some very powerful tools in MS Excel, including financial and scientific applications such as getting financial data from the Internet as well as linear programming.

You can learn about Excel VBA in my VBA tutorial at:


Tuesday, May 5, 2009

Prime Number Tester

Prime numbers are easy to determined when they are less than 100. However, once they are more than 100, it maybe difficult to determine. Fortunately, in Visual Basic, you can write a program to test the numbers.

In this program, I use the Select Case ......End Select statement to determine whether a number entered by a user is a prime number or not. For case 1, all numbers that are less than 2 are prime. In Case 2, if the number is 2, it is a prime number. In the last case, if the number N is more than 2, I need to divide this number by all the numbers from 3,4,5,6,........up to N-1, if it can be divided by any of these numbers, it is not a prime number, otherwise it is a prime number. I use a Do......Loop While statement to control the program flow. Here I also used a tag="Not Prime' to identify the number that is not prime, so that when the routine exits the loop, the label will display the correct answer.

Please refer to the link below for the source code.