my computer lessons

Wednesday, February 23, 2011

Loops and Arrays in VBA

http://www.classanytime.com/mis333k/sjloopsarrays.html#array

Loops provide for the repetition of tasks in a program. When you are coding a program and find yourself doing the same thing over and over, you should think about how a loop or a subprogram might be used instead. Using a loop will save you lines and lines of programming if the task or operation has to be repeated (or even if it only has the potential to be repeated). This document presents a basic overview for Access 2000 VBA programmers of loops and arrays through a series of examples. If you have questions about the syntax of the examples, you will find a short reference section borrowed from Access Help at the end of this document.

* Why We Need Loops
* Do ...Loops
* For ...Next
* For ...Each
* Arays and Loops
* Arrays in Procedures
* LBound and UBound
* Option Base
* Multi-dimensional Arrays
* LBound, UBound and Multi-dimensional Arrays
* Array Elements and Dimensions

Why We Need Loops

Lets look at an example of a repetitious task where loops would save lines of code.

strAnswer = InputBox("Are you ready to continue? (Y/N)")
' check for Y or N
If strAnswer = "Y" then Call Continue
If strAnswer = "N" then Exit Sub
' Y or N was not found so continue asking the user
strAnswer = InputBox("Are you ready to continue? (Y/N)")
If strAnswer = "Y" then Call Continue
If strAnswer = "N" then Exit Sub
'... and so on and on and on...

Do ...Loop Loops
To save lines and lines of programming above place the InputBox() and If statements in a loop.

Do
strAnswer = InputBox("Are you ready to continue? (Y/N)")
If strAnswer = "Y" then Call Continue
If strAnswer = "N" then Exit Sub
Loop Until strAnswer = "Y" or strAnswer = "N"

The advantage of the Do ...Loop Until loop seems very clear in this example. This is not always the case in programming situations, however, so we will use examples to make the practical side of loops apparent. The remainder of this document is organized into the syntax choices for programming with loops. As seen above, examples provide most of the information. A short reference section borrowed from Access Help is also available.

The Do ...Loop above had the logic at the bottom of the loop, and this structure forced it to run a minimum of one time. When the logic is positioned at the top of the loop, however, then the loop can potentially be skipped altogether. In the next example, a loop can execute as many times as necessary, and it will only execute if the user fails to follow instructions. This illustrates how loops can act as If statements.

strAnswer = InputBox("Are you ready to continue? (Y/N)")
Do While (strAnswer <> "Y" And strAnswer <> "N")
strAnswer = InputBox("Answer Y or N") ' executed only if user doesn't type Y or N
Loop
If strAnswer = "Y" then Call Continue
If strAnswer = "N" then Exit Sub

Do Loop syntax supports positioning logic at the beginning of the loops (Do While... Loop) instead of at the ending of the loops (Do... Until Loop). Positioning logic at the beginning or ending of a loop determines whether your program should or should not execute the code in the loop at least once.

Another syntax, called the While Wend loop, positions logic at the beginning of the loop. It is really no different from the Do While so only brief mention is made here and an example follows shortly.

Do Loops and While Wend loops are not always the best choice of loop syntax. Because the loop does not have a built in counter, you would have to program it needed for the loop. This works, of course, but requires two unnecessary lines of code.

intCounter = 0
While intCounter < 10
intCounter = intCounter + 1
StrBanner = strBanner & "** Happy Millenium **"
Wend

For ...Next Loops

Suppose you need a variable that consists of "** Happy Millenium **" repeated 10 times for a banner. Here is how the For...Next loop can fill that need.

For intCounter = 1 to 10
StrBanner = strBanner & "** Happy Millenium **"
Next

The Do Loop and For Next both work well in these programming examples. Since they are both loops, however, couldn't we use a For in place of a Do, and vice versa? The advantages and disadvantages of the two structures should become apparent in the examples below.
Using For when Do is appropriate. Right now you may think either For or Do is okay, but if you found the constant 999 in your program two months after you had written it, it would probably seem puzzling. In this case, Do is preferable, in that it is more self-documenting (i.e., the condition for satisfying the loop and continuing in the program will be clearer).

For intCounter = 1 to 999
strAnswer = InputBox("Are you ready to continue? (Y/N)")
If strAnswer = "Y" then Call Continue
If strAnswer = "N" then Exit Sub
Next

For Each ...Next Loops

Another loop format is For Each... Next. Access 2000 provides for both indexed (or numbered) and non-indexed references to its object collections. Forms, tables, queries, buttons, labels, text boxes, list boxes and so forth are objects in Access collections. By using loops, you can reference the objects without knowing their specific object names. This would be very handy, for example, if you wanted to make all labels and text boxes in your application appear in Tahoma font, size 12. Setting these values on all the property sheets by hand is hit and miss, but a loop will not miss a single object.

Let us now create a procedure to force all labels and text boxes to show in size 12 Tahoma font. This example uses the For Each...Next loop to search the Is Label property and the Is TextBox property of all controls on the open form. This illustrates another type of loop that has no counter.

Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is Label Or TypeOf ctl Is TextBox Then
ctl.FontName = "Tahoma"
ctl.FontSize = 12
End If
Next ctl

The code above can be placed in the Sub Form_Load event procedure for each form. It can be used exactly as it appears, or it can be placed in a public procedure in a standard module and then called by a line of code in Form_Load, such as: Call Tahoma12(Me). In the latter case, you change Me.Controls to frm.Controls and give Sub Tahoma12 one argument (frm as Form). Almost without exception, it is better programming practice to place code that will pertain to more than one form in a general procedure that appears inside a standard module.
Arrays and Loops

If you are not already familiar with arrays, you may want to think of an array as nothing more than a list. We all have lists in our lives, such as shopping lists, to-do lists, birthday lists, lists of stock prices for every day last week, and so forth. All items in an array, like the items in a list, have a position somewhere between first and last. Items are numbered from lowest to highest in arrays, which makes the For Next loop particularly useful in programming arrays.

The following example would calculate the weekly average price for a stock, based on an array containing the stock's price from each of five days. In these array examples the array variable is bolded.

For intCounter = 1 to 5
AveragePrice = AveragePrice + Price(intCounter)
Next
AveragePrice = AveragePrice / 5

Suppose you want the name of each day of the week to appear in an array. The following function creates an array of names in a Variant and then provides the names to the caller. Variants can be integers, strings, and so forth or arrays of these.

Public Function DayOfWeek(intDay As Integer) As String
Dim WeekDay As Variant
WeekDay = Array("Sun","Mon","Tue","Wed","Thu","Fri","Sat")
DayOfWeek = WeekDay(intDay)
End Function

Another practical example involves an array of fiscal quarter closing dates for a business. In the following program, an array of dates is built in a Variant and formatted, and one date is then returned to the caller.

Public Function DateQuarterEnds(intQuarter As Integer) As Date
Dim DateQuarter As Variant
DateQuarter = Array(Format("03/31/00", "Short Date"), _
Format("06/30/00", "Short Date"), _
Format("09/30/00", "Short Date"), _
Format("12/31/00", "Short Date"))
DateQuarterEnds = DateQuarter(intQuarter)
End Function

How to Use Arrays in Procedures

Another very important use for arrays is as arguments in function and sub calls, as shown in the following example. The purpose here is to allow you to use the procedure with an unknown number of array elements (from 0 to N) in the argument list.

Here is a function that returns the position number of the smallest numeric value in an array of values. Two noteworthy benefits of arrays in this case are: (1) that the array can be of any length; and (2) that all items in the array can be referenced by their positions and values. Here is code that would allow you to find the lowest grade in a list (or array) of student grades.

Public Function Smallest(Grades() As Single) As Integer
' find the position of the lowest grade in an array of grades
Dim intPosition As Integer, intLowestPosition As Integer
Dim sglLowest As Single
sglLowest = 9999999.9
For intPosition = LBound(Grades) To UBound(Grades)
If sglLowest > Grades(intPosition) Then
sglLowest = Grades(intPosition)
intLowestPosition = intPosition
End If
Next
Smallest = intLowestPosition
End Function

' next an example of how to include Arrays in calls
Public Sub TestSmallest()
Dim Grades(0 To 2) As Single
Grades(0) = 95#
Grades(1) = 75#
Grades(2) = 50#
Debug.Print Smallest(Grades); Grades(Smallest(Grades))
' Result: 2 50 representing the position and value of the smallest grade
End Sub

In the For Next loop, two new built-in functions are introduced. L- and U-Bound return the lower and upper limits of the array. Because you are using these functions, the program does not have to know the exact size of the Grades array. More about these functions next.
LBound and UBound: Your Good Friends

The functions are extremely useful with arrays. Did you notice in the example that Function Smallest could be used for any number of grades in any course? So, the program will have a longer shelf-life, and will be easier to maintain. Here is some more on LBound and UBound for one-dimension arrays.

'LBound(array) returns the starting or smallest index of the one-dimension array
'UBound(array) returns the ending or largest index of the one-dimension array

For intPosition = LBound(Grades) To UBound(Grades)

Option Base

In the Declarations section of VBA code, you can include Option Base 1. This would instruct Access always to start numbering your arrays with a lower bound of 1. If you do not add this declaration, Access begins its arrays at 0. Even if you omit the declaration, however, you can override zero by using the syntax: Dim Grades(1 to 3).

Option base can be confusing because some of the built-in Access functions use the declaration, while other functions ignore it completely and always begin at zero. The Array function and the Dim function use the Base, but the ParamArray function ignores the base and always uses 0.

Students are sometimes confused with character position numbering and element numbering in arrays. Strings never begin numbering at position 0. In fact, 0 will cause an error.

Good Advice: embrace option base 0. If you elect to use base 1 you will have to keep up with when and where the base 0 defaults occur. If you elect to use base 0 always there are fewer complications.
Multi-dimensional Arrays

Not all arrays you use have to be of only one dimension. As an Access user, you already are familiar with datasheets for displaying data in tables and queries. The datasheets contain columns and rows representing fields and records, respectively. A multi-dimensional array [Dim StateData(0 to 2, 0 to 49)] could contain the state name, a 2-letter abbreviation of the name, and the sales tax rate. Notice it is three fields, but the dimension is two because of Option Base 0. There could be 50 records for the 50 states. If you had this array its best use might be to provide very fast sales tax lookups.

So, how do you get the data into a multidimensional array? Example 10 illustrates the manual method and requires typing and careful proofreading, because the data was included in the logic (see Sub TestSmallest() above). In practice, we keep data in tables and logic in programs. When you mix the two you usually have some bad consequences at a later time: program maintenance. For example, if the state sales tax rate were in a table, then you would not have to change the program whenever the tax rates change.

Access provides a means of converting table data into two-dimension arrays. By using recordset programming, you can have all records or a few records placed into an array. For example, you could get the next 50 records from a table into StateDate(2,49) in this way:

'3 columns and 50 rows into StateDate(2,49)
Dim rs as DAO.Recordset
Set rs = CurrentDB.Openrecordset( _
'SELECT Name, ShortName, TaxRate FROM tblSalesTax', dbOpenDynaset)
StateData = rs.GetRows(50)
rs.Close
Set rs = Nothing

LBound, UBound and Multi-dimensional Arrays

These functions serve multi-dimensional arrays as well as single-dimension arrays. To learn the lower and upper bounds of the columns of the array, ask for LBound(StateData,1) and UBound(StateData,1). The 1 means the first dimension (fields or columns). Similarly, use LBound(StateData,2) and UBound(StateData,2) for the second dimension (rows or records).

intNumberFields = UBound(StateData,1) + 1 ' number of fields/columns

intNumberRows = UBound(StateData,2) + 1 ' number of records/rows

intIndexofFirstCol = LBound(StateData,1) ' fields starting for-loop index

intIndexofLastCol = UBound(StateData,1) ' fields ending for-loop index

intIndexofFirstRow = LBound(StateData,2) ' records starting for-loop index

intIndexofLastRow = UBound(StateData,2) ' records ending for-loop index

Array Elements and Dimensions

We are used to seeing Access datasheets. They are analogous to 2-dimension arrays with columns (fields) and rows (records). A datasheet with a single column (field) is analogous to a 1-dimension array. A cube is analogous to a 3-dimension array. Arrays can have 1, 2, 3, ..., 6 dimensions.

Students sometimes become confused about the number of dimensions and the number of elements per dimension. Elements refer to the number of columns or rows or slices in an array. For example the two-dimension array of sales tax has three fields (3 column elements) and fifty records (50 row elements). In our examples we have been declaring this array with the following syntax: Dim StateData(0 to 2, 0 to 49). It is common for programmers to refer to the tax array as a "3 by 50" array to communicate succinctly the number of dimensions and elements per dimension.
Reference Material from the Access Help System
For...Next Statement

For counter = start To end [Step step]
[statements]
[Exit For]
[statements]
Next [counter]

Part For ... Next statement syntax
counter Required. Numeric variable used as a loop counter. The variable can't be a Boolean or an array element.
start Required. Initial value of counter.
end Required. Final value of counter.
step Optional. Amount counter is changed each time through the loop. If not specified, step defaults to one. The step argument can be either positive or negative. The value of the step argument determines loop processing as follows: (1) Loop executes if counter <= end and Step Value is Positive or 0, and (2) Loop executes if Step Value is Negative and counter >= end");
statements Optional. One or more statements between For and Next that are executed the specified number of times.
Do...Loop Statement

Do [{While | Until} condition]
[statements]
[Exit Do]
[statements]
Loop

' Or, you can use this syntax:
Do
[statements]
[Exit Do]
[statements]
Loop [{While | Until} condition]

Part Do ... Loop statement syntax
condition Optional. Numeric expression or string expression that is True or False. If condition is Null, condition is treated as False.
statements One or more statements that are repeated while, or until, condition is True.
While...Wend Statement

While condition
[statements]
Wend

Part While ... Wend statement syntax
condition Required. Numeric expression or string expression that evaluates to True or False. If condition is Null, condition is treated as False.
statements Optional. One or more statements executed while condition is True.
For Each...Next Statement

For Each element In group
[statements]
[Exit For]
[statements]
Next [element]

Part For ... Each Loop statement syntax
element Required. Variable used to iterate through the elements of the collection or array. For collections, element can only be a Variant variable, a generic object variable, or any specific object variable. For arrays, element can only be a Variant variable.
group Required. Name of an object collection or array (except an array of user-defined types).
statements Optional. One or more statements that are executed on each item in group.

Top of page
made with css Comments to author: Hugh@webPoynor.com
Updated: October 28, 2010

Loops and Arrays in VBA

Optimizing VBA

Accessing Cells In A Range

You do not need to use the .Cells method to access specific cells in a range. For example, you can use

Range("MyRange")(1,2) rather than
Range("MyRange").Cells(1,2)

See Alan Beban's explanation of this method for more details.

Related to this is the shortcut method of refering to cells. VBA will allow you reference cells with [A1] rather than Range("A1"). While the [A1] syntax is easier to type, it is slower to execute than the Range("A1") syntax.

Calculation Mode

Normally, Excel will recalculate a cell or a range of cells when that cell's or range's precedents have changed. This may cause your workbook to recalculate too often, which will slow down performance. You can prevent Excel from recalculating the workbook by using the statement:

Application.Calculation = xlCalculationManual

At the end of your code, you can set the calculation mode back to automatic with the statement:

Application.Calculation = xlCalculationAutomatic

Remember, though, that when the calculation mode is xlCalculationManual, Excel doesn't update values in cells. If your macro relies on an updated cell value, you must force a Calculate event, with the .Calculate method, which may be applied to either a specific range (Range("MyRange").Calculate) or to the entire workbook (Calculate).

Collection Indexes

An individual item of a collection object may be accessed by either its name or by its index into the collection. For example, if you have three worksheets ("Sheet1", "Sheet2", and "Sheet3") in a workbook ("MyWorkbook"), you can reference "Sheet2" with either

Worksheets("Sheet2") or
Worksheets(2)

In general, the index number method (Worksheets(2)) is considerably faster than the index name method (Worksheets("Sheet2")).

However, the number and order of items in a collection may change, so it is usually safer and easier to refer to items in a collection by their name, rather than their index number.

Constants

Whenever you can, declare values as constants, rather than variables. Since their values never change, they are evaluated only once when your code is compiled, rather than each time they are used at run time.

Early Binding

This is closely tied with Specific Object Type Declaration. If you're going to work with another application, such as Word, declare your OLE object directly, rather than as an Object type variable. By doing so, a great deal of overhead is done at compile time ("Early Binding") rather than at run time ("Late Binding"). For example, use

Dim WordObj As Word.Application rather than
Dim WordObj As Object

Also see Using Variables (Properly) In VBA.

FOR EACH Loops

When looping through a collection it is usually faster than the FOR EACH statement rather than using the index. For example, the first code loop is faster than the second:

Dim WS as Worksheet
For Each WS In Worksheets
MsgBox WS.Name
Next WS

Dim i as Integer
For i = 1 To Worksheets.Count
MsgBox Worksheets(i).Name
Next i

Range Objects Not Selection Object

Generally, it is not necessary to select a range before working with it. For example, it is more efficient to use

Range("A1").Font.Bold = True

Rather than

Range("A1").Select
Selection.Font.Bold = True

Screen Updating

You can turn off screen updating so that Excel does not update the screen image as your code executes. This can greatly speed up your code.

Application.ScreenUpdating = FALSE

Be sure to restore the setting to True at the end of your macro. Older version of Excel would automatically restore the setting; Excel97 does not.

Simple Objects Rather Than Compound Objects

If you've got to make repeated references to an object, such a range, declare an object of that type, set it to the target object, and then use your object to refer to the target. For example,

Dim MyCell As Range
Set MyCell = Workbooks("Book2").Worksheets("Sheet3").Range("C3")
'....
MyCell.Value = 123

By referring directly to MyCell , VBA can access the object directly, rather than resolving the complete path to the object each time. This method is useful only when you are accessing an object several times during code execution.

Specific Object Type Declaration

If possible avoid using the Object or Variant data types. These data types require considerable overhead to determine their types. Instead, use explicit data types, such as

Dim MySheet As Worksheet rather than
Dim MySheet As Object
Or
Dim NumRows As Long rather than
Dim NumRows As Variant

This is especially true with index variables in For Next loops, since a Variant type has to be re-evaluated each iteration of the loop.

Also see Using Variables (Properly) In VBA.

WITH Statements

If you are using several statement in a row that apply to the same object, use a WITH statement, rather than fully qualifying the object each time. For example,

With Worksheets("Sheet1").Range("A1")
.Font.Bold = True
.Value = 123
End With

Worksheet Functions

You can use Excel's standard worksheet functions in your VBA code, rather than writing the functions in VBA. Since these are fully executable instructions in native code, rather than interpreted VBA code, they run much faster. For example, use

MySum = Application.WorksheetFunction.Sum(Range("A1:A100"))

rather than

For Each C In Range("A1:A100")
MySum = MySum + C.Value
Next C
+++++++++++++++++++++++
Turn Off Everything But the Essentials While Your Code is Running

This optimization explicitly turns off Excel functionality you don't need to happen (over and over and over) while your code runs. Note that in the code sample below we grab the current state of these properties, turn them off, and then restore them at the end of code execution.

One reason this helps is that if you're updating (via VBA) several different ranges with new values, or copy / pasting from several ranges to create a consolidated table of data, you likely do not want to have Excel taking time and resources to recalculate formulas, display paste progress, or even redraw the grid, especially after every single operation (even more so if your code uses loops). Just one recalculation and one redraw at the end of your code execution is enough to get the workbook current with all your changes.

Here's some sample code that shows how and what to shut off while your code runs. Doing this should help improve the performance of your code:

'Get current state of various Excel settings; put this at the beginning of your code

screenUpdateState = Application.ScreenUpdating

statusBarState = Application.DisplayStatusBar

calcState = Application.Calculation

eventsState = Application.EnableEvents

displayPageBreakState = ActiveSheet.DisplayPageBreaks 'note this is a sheet-level setting

'turn off some Excel functionality so your code runs faster

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting

'>>your code goes here<<

'after your code runs, restore state; put this at the end of your code

Application.ScreenUpdating = screenUpdateState

Application.DisplayStatusBar = statusBarState

Application.Calculation = calcState

Application.EnableEvents = eventsState

ActiveSheet.DisplayPageBreaks = displayPageBreaksState 'note this is a sheet-level setting

Here's a quick description for each of these settings:

Application.ScreenUpdating: This setting tells Excel to not redraw the screen while False. The benefit here is that you probably don't need Excel using up resources trying to draw the screen since it's changing faster than the user can perceive. Since it requires lots of resources to draw the screen so frequently, just turn off drawing the screen until the end of your code execution. Be sure to turn it back on right before your code ends.

Application.DisplayStatusBar: This setting tells Excel to stop showing status while False. For example, if you use VBA to copy/paste a range, while the paste is completing Excel will show the progress of that operation on the status bar. Turning off screen updating is separate from turning off the status bar display so that you can disable screen updating but still provide feedback to the user, if desired. Again, turn it back on right before your code ends execution.

Application.Calculation: This setting allows you to programmatically set Excel's calculation mode. "Manual" (xlCalculationManual) mode means Excel waits for the user (or your code) to explicitly initiate calculation. "Automatic" is the default and means that Excel decides when to recalculate the workbook (e.g. when you enter a new formula on the sheet). Since recalculating your workbook can be time and resource intensive, you might not want Excel triggering a recalc every time you change a cell value. Turn off calculation while your code executes, then set the mode back. Note: setting the mode back to “Automatic” (xlCalculationAutomatic) will trigger a recalc.

Application.EnableEvents: This setting tells Excel to not fire events while False. While looking into Excel VBA performance issues I learned that some desktop search tools implement event listeners (probably to better track document contents as it changes). You might not want Excel firing an event for every cell you're changing via code, and turning off events will speed up your VBA code performance if there is a COM Add-In listening in on Excel events. (Thanks to Doug Jenkins for pointing this out in my earlier post).

ActiveSheet.DisplayPageBreaks: A good description of this setting already exists: http://support.microsoft.com/kb/199505 (Thanks to David McRitchie for pointing this out).

Read/Write Large Blocks of Cells in a Single Operation

This optimization explicitly reduces the number of times data is transferred between Excel and your code. Instead of looping through cells one at a time and getting or setting a value, do the same operation over the whole range in one line, using an array variable to store values as needed.

For each of the code examples below, I had put random values (not formulas) into cells A1:C10000.

Here's a slow, looping method:

Dim DataRange as Range
Dim Irow as Long
Dim Icol as Integer
Dim MyVar as Double
Set DataRange=Range("A1:C10000")

For Irow=1 to 10000
For icol=1 to 3
MyVar=DataRange(Irow,Icol) 'Read values from the Excel grid 30K times
If MyVar > 0 then
MyVar=MyVar*Myvar ' Change the value
DataRange(Irow,Icol)=MyVar 'Write values back into the Excel grid 30K times
End If
Next Icol
Next Irow

Here's the fast version of that code:

Dim DataRange As Variant
Dim Irow As Long
Dim Icol As Integer
Dim MyVar As Double
DataRange = Range("A1:C10000").Value ' read all the values at once from the Excel grid, put into an array

For Irow = 1 To 10000
For Icol = 1 To 3
MyVar = DataRange(Irow, Icol)
If MyVar > 0 Then
MyVar=MyVar*Myvar ' Change the values in the array
DataRange(Irow, Icol) = MyVar
End If
Next Icol
Next Irow
Range("A1:C10000").Value = DataRange ' writes all the results back to the range at once

Note: I first learned of this concept by reading a web page by John Walkenbach found here: http://www.dailydoseofexcel.com/archives/2006/12/04/writing-to-a-range-using-vba/

A previous Excel blog entry by Dany Hoter also compares these two methods, along with a selection / offset method as well: http://blogs.msdn.com/excel/archive/2008/10/03/what-is-the-fastest-way-to-scan-a-large-range-in-excel.aspx

...which leads me to my next point.

Avoid Selecting / Activating Objects

Notice that in the above-referenced blog post, the selection method of updating a range was the slowest. This next optimization minimizes how frequently Excel has to respond to the selection changing in the workbook by minimizing the selection changing as much as possible.

Range Example: Again, see the Excel blog post quoted above. It demonstrates that using selection is the slowest of the 3 methods discussed for reading and writing to ranges.

Shapes Example: Setup: I have 40 shapes on a sheet, and I want to write "Hello" in each of them.

Using the slower "selection" method, the code looks like this:

For i = 0 To ActiveSheet.Shapes.Count

ActiveSheet.Shapes(i).Select

Selection.Text = "Hello"

Next i

The much faster method is to avoid selection completely and directly reference the shape:

For i = 0 To ActiveSheet.Shapes.Count

ActiveSheet.Shapes(i).TextEffect.Text = "Hello"

Next i

The concepts illustrated by the examples above can also be applied to objects other than Ranges and Shapes.

Note: I first learned of this concept, in the context of shapes, by reading a web page by Ron de Bruin found here: http://www.rondebruin.nl/shape.htm

Related Performance Paper

See the "Improving Performance in Excel 2007" paper on MSDN: http://msdn.microsoft.com/en-us/library/aa730921.aspx

This is a fairly detailed and comprehensive paper that introduces the bigger grid and increased limits in Excel 2007, and primarily focuses on Excel calculation performance and debugging calculation performance bottlenecks. There's also a short section on how to write faster VBA macros.

Other Performance Optimizations

While the above optimizations are what I consider the most important, there are a few other "honorable mention" optimizations I will mention briefly for you to consider.

Consider the performance gains by implementing your code's functionality via XLL / C-API. An overview and supporting materials for the SDK can be found here: http://msdn.microsoft.com/en-us/library/bb687827.aspx .

Declare variables with explicit types to avoid the overhead of determining the data type (repetitively if used in a loop) during code execution.

For simple functions used by your code in high frequency, implement them yourself in VBA instead of using the WorksheetFunction object.

Use Range.SpecialCells() to scope down the number of cells your code needs to work with.
+++++++++++++++++++++++
Tip #1: minimise traffic between worksheets and VBA.

There is substantial overhead in communications between worksheets and VBA. It works both ways. So if you have a custom VBA function which you call from hundreds of worksheet cells, it could be s-l-o-w. Equally, if you use VBA to read in lots of cells to memory, or write lots of data back to cells, it can be real slow.

When working in VBA, the trick is to read/write data in chunks. We had a real life example, where we needed to read in 1000 records off a worksheet, each of 20 cells, do some calculations, and write back 14 cells at the end of each record. That's 34,000 hits on the worksheet. We cut it to just 15. Here's how.

We read in 125 records at once (that's 125x20 = 2500 cells) into an array, then looped through and calculated the 14 output cells for each of these records, and stored them in a memory array. Then we read the next batch of 125 records, and so on, until we hit empty cells. So far we've only made 8 (input) hits on the worksheet. Then we wrote the 14,000 results back by writing columns instead of rows - in fact, two columns at a time, each containing all the 1000 or so records. That only requires 7 writes to take care of the 14 output columns, or just 15 hits in total. The speed improvement was phenomenal.

You may have some questions about this:

How do you read in/write out lots of cells at once?

'to read in
Dim A as Variant 'MUST be variant, no brackets
A = Range("SomeRange").Resize(10,20) 'reads 10x20 array starting at range SomeRange
'(NB I've used Resize above but you can specify a range of cells any way you want)

'to write back to sheet
Range("SomeRange").Resize(10,20) = A
'A can be any data type but MUST be two dimensional even if you are only writing one
'column or row - first dimension is used for rows, and the second for columns
'this can be slow - see third question below for workaround..

Why read in 125 records at a time? Why not read in all 1000?

Excel 5&7 can't read in more than about 3000 cells at once or you get an error. The same goes for writing back to a worksheet. So we had to do it in chunks.

Reading in is quick, but writing back takes forever.

Excel 5&7 have a very slow method of writing back to the sheet, so someone came up with a neat workaround. This is 5-10 times faster than setting a range equal to a VBA array. (Don't use this in Excel 8, it is faster to say Range = Array).

Sub SuperBlastArrayToSheet(TheArray As Variant, TheRange As Range)
With TheRange.Parent.Parent 'the workbook the range is in
.Names.Add Name:="wstempdata", RefersToR1C1:=TheArray
With TheRange
.FormulaArray = "=wstempdata"
.Copy
.PasteSpecial Paste:=xlValues
End With
.Names("wstempdata").Delete
End With
End Sub

What about VBA functions used in worksheets?

It's harder to minimise traffic when your worksheet is calling VBA functions. However, something you can do is avoid reinitialising or reading in values over and over. Suppose your function needs 5 parameters, 4 of which are set at the top of the sheet, and only one of which is set by the calling cell. You can cut down the calculation load, by loading the 4 parameters the first time only, by putting a test in your function like "If A=0 Then Initialise", to call an initialisation routine. This can be done because VBA keeps variable values in memory once they are set (unless they are dimensioned within a subroutine or function, in which case they disappear when the function ends) until either you close the worksheet or make any change to the VBA code. So you can set the variables once and use them forever.

Apart from this, you will generally find that it pays to avoid VBA functions in big spreadsheets. Personally, I try to use either VBA or worksheet formulae, rather than a mixture.

Tip #2 : Declare variables for speed

This is pretty well known stuff so I'll keep it brief. Variants are powerful but slow. Use them only when you have to or when it doesn't matter. Ideally, use integers or booleans wherever possible, followed by long, single and double.

And watch this trap..

Dim A, B, C As Integer 'only defines C as integer, the others will be variants
Dim A As Integer, B As Integer, C As Integer 'defines all 3 as integer

And this one. The function Function Something() returns a variant, whereas Function Something() As Integer returns an integer. Lesson: define functions as something.

Tip #3 : avoid overuse of worksheet functions in code

Here's an example. VBA doesn't have a Max or Min function, but Excel does. So you could write..

C = Application.Max (A, B) 'uses Excel worksheet function in VBA to return max of A & B

or do it the hard way in code like this

If A>=B Then C = A Else C = B

Now you won't believe the next part if you haven't seen it already. The code method is about 150 times faster (on my PC, anyway). So if you are doing some big loops, avoid worksheet functions if you can, or at least test comparative speed.

Some built-in VBA functions are real slow too. It's worth testing the speed on any you use a lot, just to be sure.

Tip #4 : Turn off screen updating, recalculation

If you're working with multiple sheets, screen refresh can slow you down and distract your user. Put Application.ScreenUpdating = False at the top of your routine and it will freeze screen updating until all your code has stopped executing.

Similarly, you can suspend auto-recalculation with Application.Calculation = xlManual, and return it to auto at the end with Application.Calculation = xlAutomatic

Tip #5 : Don't select sheet objects to use them

The macro recorder selects objects before working with them. This is not necessary and slows down execution. So you can change this ..

Sheets("Sheet2").Select
Range("D9").Select
ActiveCell.FormulaR1C1 = "3"

to

Sheets("Sheet2").Range("D9") = 3

Remember, Excel is pretty smart, so you only need to describe an object enough to make it unambiguous. In fact, it pays (in speed) not to mention unnecessary objects, because every object you mention has to be resolved by VBA.

So Range("Hobbes") is faster than Workbooks("Comic").Sheets("Calvin").Range("Hobbes").

Tip #6 : Psychological slowness - Give the user feedback

Isn't it frustrating when something is taking forever and you have no idea when it will finish? Time seems to crawl. You can use the statusbar to let users know how things are going. Below is an example based on reading in a datafile. It includes a way of measuring the percentage read so far.

'get length of file initially, divide by 128 for reason given later
'we will show percentage completed
LenF = FileLen(MyFile) / 128
Open MyFile For Input As #1
Do While Not EOF(1) ' read in file until finished
Line Input #1,SomeText
N = N + 1 'counts lines read
'update statusbar every 100 records - not every record!
'Loc returns position in file, in multiples of 128 bytes. (That's why we divided the LenF
'variable by 128. Loc / LenF will give us the percentage read in).
'you could equally show the number of records read
If N Mod 100 = 0 Then Application.StatusBar = "Processed " & Format(Loc(1)/LenF,"0%")
Loop
Close
'give statusbar back to Excel
Application.StatusBar = False

In addition, if your spreadsheet loads slowly because of initialisation routines, you might consider showing a splash screen (say, a textbox attractively labelled with the name of your program, etc) for a few seconds. It all helps pass the time for the user.

Tip #7 : Read/write data to file quickly

A couple of quickies here.. To read a lot of data quickly, try this..

Open MyFile For Input As #1
myVar = Input$(10000,1) 'reads 10000 chars OR
myVar = Input$(Lof(1),1) 'reads whole file

To read/write delimited data instantly between a file and a memory array, try this (Excel 8 only, sorry).

'to write the data
'dimension and fill array A, then ....
Open "c:\temp\test.txt" For Binary As #1
Put #1,,A 'writes whole of A to file
Close

'to read it back
Dim A(30,10) As Single
Open "c:\temp\test.txt" For Binary As #1
Get #1,,A 'reads whole of A
Close

Tuesday, February 22, 2011

http://www.anthony-vba.kefra.com/index_002.htm
***********************************************************************
'* Mean *
'***********************************************************************
Function Mean(k As Long, Arr() As Single)
Dim Sum As Single
Dim i As Integer

Sum = 0
For i = 1 To k
Sum = Sum + Arr(i)
Next i

Mean = Sum / k

End Function

'************************************************************************
'* Standard Deviation *
'************************************************************************
Function StdDev(k As Long, Arr() As Single)
Dim i As Integer
Dim avg As Single, SumSq As Single

avg = Mean(k, Arr)
For i = 1 To k
SumSq = SumSq + (Arr(i) - avg) ^ 2
Next i

StdDev = Sqr(SumSq / (k - 1))

End Function

'*************************************************************************
'* Computation *
'*************************************************************************
Sub compute()
Dim Arr(10) As Single
Dim Average As Single
Dim Std_Dev As Single

For i = 1 To 10
Arr(i) = Sheets("Sheet1").Cells(i, 1)
Next i

Average = Mean(10, Arr)
Std_Dev = StdDev(10, Arr)
Sheets("Sheet1").Cells(12, 1) = Average
Sheets("Sheet1").Cells(13, 1) = Std_Dev

End Sub

Sub ExamScore2()
Dim Row As Integer
Dim Score As Double
Dim Award As String

'Loop through 10 rows of scores
'First column contains exam score, second to contain the award
For Row = 1 To 10

'First get the score
Score = ActiveSheet.Cells(Row, 1)

Select Case Score
Case Is < 40
Award = "Fail"
Case 40 To 59
Award = "Pass"
Case 60 To 79
Award = "Merit"
Case Else
Award = "Distinction"
End Select

'Write the award
ActiveSheet.Cells(Row, 2) = Award

Next Row

End Sub

+++++++++++++++++++++++++++
Sub ExamScore()
Dim Row As Integer
Dim Score As Double
Dim Award As String

'Loop through 10 rows of scores
'First column contains exam score, second to contain the award
For Row = 1 To 10

'First get the score
Score = ActiveSheet.Cells(Row, 1)

'Assign the award based on the score
If Score < 40 Then
Award = "Fail"
ElseIf Score < 60 Then
Award = "Pass"
ElseIf Score < 80 Then
Award = "Merit"
Else
'At this point score must be greater than 80%
Award = "Distinction"
End If

'Write the award
ActiveSheet.Cells(Row, 2) = Award

Next Row

End Sub
++++++++++++++++++++++++++++++
Private Sub CommandButton1_Click()

DinnerPlannerUserForm.Show

End Sub
------------
Private Sub UserForm_Initialize()

'Empty NameTextBox
NameTextBox.Value = ""

'Empty PhoneTextBox
PhoneTextBox.Value = ""

'Empty CityListBox
CityListBox.Clear

'Fill CityListBox
With CityListBox
.AddItem "San Fransisco"
.AddItem "Oakland"
.AddItem "Richmond"
End With

'Empty DinnerComboBox
DinnerComboBox.Clear

'Fill DinnerComboBox
With DinnerComboBox
.AddItem "Italian"
.AddItem "Chinese"
.AddItem "Frites and Meat"
End With

'Uncheck DataCheckBoxes
DateCheckBox1.Value = False
DateCheckBox2.Value = False
DateCheckBox3.Value = False

'Set no car as default
CarOptionButton2.Value = True

'Empty MoneyTextBox
MoneyTextBox.Value = ""

'Set Focus on NameTextBox
NameTextBox.SetFocus

End Sub

---------------------------------
Private Sub CancelButton_Click()

Unload Me

End Sub

Assign a macro to the Clear button

To call the Sub UserForm_Initialize when you click on the Clear button, execute the following steps.

1. Launch the Visual Basic Editor.

2. Double click on DinnerPlannerUserForm from the Project Explorer.

3. Double click on the Clear button.

4. Add the following code line:
Private Sub ClearButton_Click()

Call UserForm_Initialize

End Sub

Assign a macro to the OK button

After clicking on the OK button, the information from the Userform will be placed on your worksheet.
Private Sub OKButton_Click()

Dim emptyRow As Long

'Make Sheet1 Active
Sheets(1).Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Export Data to worksheet
Cells(emptyRow, 1).Value = NameTextBox.Value
Cells(emptyRow, 2).Value = PhoneTextBox.Value
Cells(emptyRow, 3).Value = CityListBox.Value
Cells(emptyRow, 4).Value = DinnerComboBox.Value

If DateCheckBox1.Value = True Then Cells(emptyRow, 5).Value = DateCheckBox1.Caption

If DateCheckBox2.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox2.Caption

If DateCheckBox3.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox3.Caption

If CarOptionButton1.Value = True Then
Cells(emptyRow, 6).Value = "Yes"
Else
Cells(emptyRow, 6).Value = "No"
End If

Cells(emptyRow, 7).Value = MoneyTextBox.Value

End Sub
--------------------------
Private Sub MoneySpinButton_Change()

MoneyTextBox.Text = MoneySpinButton.Value

End Sub
-------------------------
Private Sub ClearButton_Click()

Call UserForm_Initialize

End Sub
-----------------------
Private Sub OKButton_Click()

Dim emptyRow As Long

'Make Sheet1 Active
Sheets(1).Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Export Data to worksheet
Cells(emptyRow, 1).Value = NameTextBox.Value
Cells(emptyRow, 2).Value = PhoneTextBox.Value
Cells(emptyRow, 3).Value = CityListBox.Value
Cells(emptyRow, 4).Value = DinnerComboBox.Value

If DateCheckBox1.Value = True Then Cells(emptyRow, 5).Value = DateCheckBox1.Caption

If DateCheckBox2.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox2.Caption

If DateCheckBox3.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox3.Caption

If CarOptionButton1.Value = True Then
Cells(emptyRow, 6).Value = "Yes"
Else
Cells(emptyRow, 6).Value = "No"
End If

Cells(emptyRow, 7).Value = MoneyTextBox.Value

End Sub
-------------------------------
Private Sub MoneySpinButton_Change()

MoneyTextBox.Text = MoneySpinButton.Value

End Sub

Sunday, February 20, 2011

http://www.xlpert.com/vba_index.htm
Activate event

The example below shows a simple procedure that is executed whenever a particular sheet is activated, and display the name of the active worksheet in a message box. The code is enter in the Worksheet_Activate () module.

Private Sub Worksheet_Activate()

MsgBox ¡¡ãYou are viewing ¡¡ã & ActiveSheet.Name

End Sub

ActiveSheet

If you want to refer to the currently selected worksheet in a workbook, you can do so by using the ActiveSheet property. For example, the subroutine

below will enter the value 100 into cell A1 in Worksheets(2) as the current selected worksheet.

Sub ActiveSheetDemo ( )

ActiveSheet.Cells(1,1).Value = 100

End Sub.

Add comments to a Cell (back to top)

In order to add comment to a cell, you can use the AddComment method with the Range object. The macro below will show you how.

Sub AddCommentDemo ( )

Range("A1").AddComment "Prevent Global Warming"

End Sub

Here the word "Prevent Global Warming" is added as a comment for cell A1. To display this comment all the time insert the following code..

Range("A1").Comment.Visible = True

Add Method (back to top)

Add a workbook and input a value in Range A1 of workbook created.

Sub AddWorkbook ()

Workbooks.Add
intWorkbooksCount = Workbooks.Count

Set ReportWorkbook = Workbooks(intWorkbooksCount)
Set sht1 = ReportWorkbook.Worksheets(1)

sht1.Cells(1, 1) = "Welcome"

End Sub

Adding Items to a ComboBox and ListBox

The code below show how to populate a combobox or a listbox .

ComboBox1.AddItem "Boston"
ComboBox1.AddItem "New York"

Address

To specify a range reference in a style we are familiar with, like A1 or E5. The Excel VBA procedure below will find a cell that has formula content

and will display the address of this cell in a message box.

Sub AddressDemo ()

Set MyRange = Range("A1:Z1000")

For Each cell in MyRange

If cell.HasFormula = True Then

MsgBox cell.Address

Exit For

Next cell

End Sub

And Operator (back to top)

Perform a logical conjunction on two expression. In this case, two expression has to be satisfied in order for the macro to continuerunning. The macro below show how:

Sub AndDemo ()

x = 1

y =2

If x = 1 and y = 2 then

z = x * y

MsgBox (z)

End if

End Sub

Areas Collection (back to top)

The Areas collection contains a few block of cells within a specific selection i.e. multiple ranges to create one multi-range. Let's look at an example...

Sub AreasDemo()

Dim rng1 As Range, rng2 As Range, rng3 As Range, myRange As Range
Dim AreasInMyRange As Integer

Set rng1 = Range("A1:B10")
Set rng2 = Range("D1:D10")
Set rng3 = Range("G1:G10")
Set myRange = Union(rng1, rng2, rng3)

AreasInMyRange = myRange.Areas.Count

End Sub

The above macro use the Union function to combine 3 non-contiguous ranges. AreasInMyRange will return 3 as we use the Areas.Count method to calculate the 3 areas in myRange.

Autofill Method

This method enable user to autofill a series of data on the specified range of cells. Look at the example below.

Sub AutofillDemo ( )

Range("A1:B1").Autofill Range("A1:J1")

End Sub

The above will autofills cells C1 through J1 using the source data in cells A1 and B1. If A1 contains 1 and

B1 contains 2, then this code will fill the destination cells with consecutive integers starting at 3 (in cell C1), 4 (in cell D1) and so on

B

Built -in Functions (back to top)

This code will use the Excel built-in function, Average, Max, Min and Standard Deviation.

Option Base 1

Sub BuiltInFunctionDemo ()

Dim MyArray(100) As Integer

For x = 1 to 100

MyArray(x) = Rnd

Next x

average = Application.Average(MyArray)

max = Application.Max(MyArray)

min = Application.Min(MyArray)

std = Application.Stdev(MyArray)

End Sub

C

Calculate a specific cell

Excel will calculate that worksheet. When applied to a specific range, as in:

Worksheets(1).Rows(2).Calculate

With the above syntax, Excel VBA will calculate only row 2 in worksheet 1. If you want to calculate all the formula in worksheet 1 then insert

the following code...

Worksheets(1).Calculate

Your Ad Here
Calling a Worksheet Function from Visual Basic (back to top)

In Visual Basic, the Microsoft Excel worksheet functions are available through the WorksheetFunction object. The following procedure uses the Max worksheet function to determine the largest value in a range of cells¡

Sub FunctionDemo()

Set myRange = Worksheets("Sheet1").Range("A1:B10")

ans = Application.WorksheetFunction.Max(myRange)

MsgBox ans

End Sub

The range A1:B10 on Sheet1 is Set as myRange. Another variable, ans, is assigned the result of applying the Max function to myRange which then show up in a message box when you execute the procedure.

Cells Method (back to top)

To enter the the value 100 into Range("B2"), the code below will do the trick...

ActiveSheet.Cells(2,2).Value = 100

We can also reference a specific cell in a Range object by using the code below...Here the value 100 is enter into Range("C4")

Set MyRange = Range("C3:C10")

MyRange.Cells(2).Value = 100

Change text to proper case

To change text in a selected range to proper case use the code below.

Sub ProperCaseDemo()
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = strconv(cell.text,vbProperCase)
End If
Next
End Sub

Change the name of a Worksheet

You can change the name of a worksheet by using the Name property of the Worksheet object. The macro below will show you how.

Sub ChangeNameDemo ( )

Dim wsName As String

wsName = InputBox("Enter a new worksheet name")

ActiveSheet.Name = wsName

End Sub

Clear method

This method enables you to clear the entire values, formulas and formatting from a range of cells. The procedure below use different Clear method..

Sub ClearDemo ( )

Range("A101:E200").ClearContents 'will clear the cell values and formulas from a range of cells except the formatting

Range("A201:E300").ClearFormats 'to clear the formatting

Range("A1:E100").Clear 'will clear the entire values, formulas and formatting from a range of cells

End Sub

Copying a range (back to top)

This is how you write a simple copy and paste operation...

Sub CopyDemo ()

ActiveSheet.Range("A1:A3"). Copy (ActiveSheet.Range("B1:B3"))

End Sub

Columns properties

With this property, you can select the entire column as the Range object.

RangeSelected = Columns(3)

Here column C is selected. You can also use the Columns property to determine a specific Column within a Range. For example, look at the

procedure below.

Sub ColumnDemo ( )

Set MyRange = Range(¡¡ãB1:E100¡¡À).

MyRange.Columns(2).Select

End Sub

Here you have selected Range(¡¡ãC1:C100¡¡À) as it is the second column within B1:E100.

Comparing two strings (back to top)

The Excel built-in function StrComp is use to compare whether two strings are alike. Let me show you how with the macro below.

Sub CompareString ( )

aStr = ActiveSheet.Range("A1").Value

bStr = ActiveSheet.Range("A2").Value

If StrComp(aStr,bStr) = 0 Then

MsgBox "They match"

Else

MsgBox "They are not the same"

End If

End Sub

Create Chart Sheet

To create a chart sheet, we can use the Add method of the Charts collection to create and add a new chart sheet to the workbook. The

macro below will do the trick.

Sub CreateChartDemo ( )

Dim ch As Chart

Set ch = ThisWorkbook.charts.Add()

ch.Name = "Account"

End Sub

Current Cell Content (back to top)

If you want to know what the cell contains ie dates, text or formulas before a block of code is executed, you can use the code below. Replace this with the code you want to execute.

Sub ContentDemo()

If Application.IsText(ActiveCell) = True Then
MsgBox "Text" 'block of code to be executed

ElseIf ActiveCell = "" Then
MsgBox "Blank cell" 'block of code to be executed

ElseIf ActiveCell.HasFormula Then
MsgBox "Formula" 'block of code to be executed

ElseIf IsDate(ActiveCell.Value) = True Then
MsgBox "Date" 'block of code to be executed

End If

End Sub

Customize background color of cell

You can customize the background of cell and range of cells by using the ColorIndex property with the Interior object.

Sub ColorIndexDemo ( )

Cells(1,1).Interior.ColorIndex = 3 'set to red

Cells(1,2).Interior.ColorIndex = 5 'set to blue

Columns(3).Interior.ColorIndex = 1 'set to black

End Sub

Custom Functions (back to top)

I bet you're familiar with Excel built-in function like Sum, Average, Max, PMT etc. If you feel that's not enough then you can create custom functions by using Excel VBA. Let's create our own cubic function.

Public Function Cubic (num)

Cubic = num ^ 3

End Function

D

Delete (back to top)

To delete a worksheet, use the following code..Worksheet(2) will be deleted

Sub DeleteSheetDemo1 ()

Worksheets(2).Delete

End Sub

When you delete a sheet, Excel will display a message to warn user whether they want to delete the sheet or not. To disable this message, use the code below

Sub DeleteSheetDemo2 ()

Application.DisplayAlerts = False

Worksheets(2).Delete

End Sub

Your Ad Here

Disable the Ctrl + Break and Esc key (back to top)

In order for you to prevent user to stop a macro before it finish running by pressing the Ctrl + Break and Esc key, just insert the code below at

the top of your procedure...

Application.EnableCancelKey = False

DisplayFullScreen

The macro below show you how to display fullscreen using Excel VBA

Application.DisplayFullScreen = True

To exit full screen using VBA then

Application.DisplayFullScreen = False

Do Until loop.

When you use the Do Until loop, the code you enter between the Do and Loop will execute until the specified condition is met.

Sub DoDemo ( )

Dim x As Integer

x = 1

Do Until IsEmpty(Cells(x,1))

Cells(x,1).Font.Bold = True

x = x + 1

Loop

End Sub

The macro will execute until the cell in Column A is empty.

Your Ad Here

E

End Method (back to top)

You can use the End of the Range object to select a particular. See this example...

Sub EndDemo ()

Range(ActiveCell, ActiveCell.End(xlDown)).Select 'select downward the activecell to last non-empty cell

Range(ActiveCell, ActiveCell.End(xlUp)).Select 'select upward the activecell to last non-empty cell

Range(ActiveCell, ActiveCell.End(xlToLeft)).Select 'select to the left of the activecell to last non-empty cell

Range(ActiveCell, ActiveCell.End(xlToRight)).Select 'select to the right of the activecell to last non-empty cell

End Sub

Err

The default syntax of Excel VBA. This example demonstrates error handling by jumping to a label..

Sub ErrDemo ()

Dim x As Integer

On Error GoTo ErrorHandler

x = "abc"

Exit Sub

ErrorHandler:

e = Err.Number & " ...cannot assign integer value to x"

MsgBox (e)

End Sub

Exit Do (back to top)

If you need to jump out from a Do loop, you can do this by using the Exit Do statement. The macro below will do the trick.

Sub ExitDoDemo ( )

x = 1

y = 100

Do While x < y

If x^2 > 100 Then Exit Do

x = x + 1

Loop

End Sub

The procedure will halt when x^2 is more than 100.

Exit Sub (back to top)

The Exit Sub statement is use as a point of exiting a subroutine with running the rest of any other statement in a procedure. The example below will display a message box to prompt the user whether to continue or not. If user choose yes, the subroutine will call the macro MyProcdure and will exit the current subroutine.

Sub ExitSubDemo ( )

msg = "Do you want to continue?"

answer = MsgBox(msg,vbYesNo)

If answer = vbYes Then

Call MyProcedure

Exit Sub

Else

MsgBox "Program will end now."

End if

End Sub

F

Fill Method

You can use the Fill method to fills a range of cells. Let's see how this is implemented below.

Sub FillDemo ( )

Range("A2:A10").FillUp 'this will fill up the value contain in cell A10 to all cell above until A2

Range("A2:A10").FillDown 'this will fill down the value contain in cell A2 to all cell below until A10

End Sub

Find a File (back
to top)

In order to search for a file in your system, you can use the FileSearch object. The macro below will do the trick.

Sub FileSearchDemo ( )

Dim PathInfo As String

With Application.FileSearch

.NewSearch

.FileName = ¡¡ãComputer.xls¡¡À

End With

End Sub

You must make that such file exist, else Excel VBA will return an error message.

Font Object

Properties of this object includes Bold, Size, Italic, Underline and etc. To set the font properties you can enter the code as follow¡

Sub FontDemo ( )

Set SelFont = ActiveSheet.Range(¡¡ãA1¡¡À).Font

SelFont.Font.Size = 12

SelFont.Font.Bold = True

End Sub

The above procedure enables you to set the font of cell A1 to size 12 and bold.

For Next Loop (back to top)

Use this syntax when you want to execute for a determine number of time.

Sub ForNextDemo1 ()

For x = 1 to 10

y = x + 1

Next x

MsgBox (y)

End Sub

Here you'll get y = 11

Look at another example...The code below will for cell value that are positive to bold.

Sub ForNextDemo2 ()

Set MyRange = Range("A1:A100")

y = MyRange.Rows.Count ' y = 100

For x = 1 to y

If MyRange.Cells(x).Value > 0 Then

MyRange.Cells(x).Font.Bold = True

End If

Next x

End Sub

Or you can use the For Each - Next syntax to do the same thing.

Sub ForNextDemo3 ()

Set MyRange = Range("A1:A100")

For Each cell in MyRange

If cell.Value > 0 Then

cell.Font.Bold = True

End if

Next cell

End Sub

G

GoTo (back to top)

When the GoTo syntax is use, you can make Excel VBA to jump to a label and execute the line of code under the label like the On Error GoTo example I've demonstrate above. Let's look at another example...

Sub GotoDemo ()

x = Int(Rnd() * (1 - 10) + 10) 'a random number between 1 and 10

If x < 5 Then

GoTo Less

Else

GoTo More

End If

Exit Sub

Less:

MsgBox x & " is less than 5"

Exit Sub

More:

MsgBox x & " is more than 5"

End Sub

You can also specify a Excel macro to go to a specific range by using the GoTo method. For example, in a worksheet you have name a Range ("Credit Card"). To select this Range use the following statement...

Sub GoToDemo2 ()
Application.Goto Reference:="Credit Card" 'equivalent to Range("Credit Card").Select
End Sub

Goto Reference cell

When you want to go to a specific cell or range, you can instruct Excel to do so by entering the code below...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.Goto Reference:=Worksheets("Loan").Range("B100"), _
Scroll:=True

End Sub

As the Worksheet ("Loan") is activated, cell B100 will be at the top right window.

Your Ad Here

H

Height (back to top)

To change the height userform, you can use the Height property. Look at the example below...

Private Sub UserForm_Initialize()

UserForm1.Height = 100

End Sub

When UserForm1 initialize, it height will be set to 100 points.

Hiding Columns

To hide a range of column the macro below will do the trick.

Sub HideColDemo ( )

StartCol = 3

EndCol = 6

For n = StartCol to EndCol

Columns(n).Hidden = True

Next n

End Sub

With the above procedure column C to F will be hidden

Hiding Rows

To hide a range of rows the macro below will do the trick.

Sub HideRowDemo ( )

StartRow = 3

EndRow = 6

For n = StartRow to EndRow

Rows(n).Hidden = True

Next n

End Sub

With the above procedure Row 3 to 6 will be hidden

Hiding Sheets (back to top)

You can use the following code to enable you to hide your worksheet from users. Name a worksheet Pharmacy.

Sub HideSheetDemo()

Worksheets(Pharmacy).Visible = xlSheetVeryHidden

End Sub

If you hide your sheets this way, users will not be able to unhide them using the Excel menus. Only using VB codes will be able to display the sheets again.

Hide UserForm

However, if you want to hide a userform different statement is required. The macro below will do the trick. If you have rename a userform to Account then the code is

Sub HideUserformDemo ()

Account.Hide

Range(A1).Value = Account.TextBox1.Text

End Sub

You still can access the value store in the userform programmatically. For example, you still can retrieve value on TextBox1 on the form.

Hyperlinks In Excel (back to top)

You may notice that Excel automatically convert URL that you enter into hyperlink. If you don't like this then you can delete the hyperlink

by entering the code below.

Sub DeleteHyperLinksDemo()

Sheet2.Hyperlinks.Delete

End Sub

Here all the hyperlinks in sheet 2 will be deleted

I

If Then statement

This statement checks to see if a specific condition is true, and if true then it will execute all the code between the Then keyword and the End If statement. Look at the example below...

Sub IfThenDemo ( )

Dim x As Integer, MySum As Integer

For x = 1 to 100

If IsNumeric(Range("A" & x)) = True Then

MySum = MySum + Range("A" & x).Value

End If

Next x

MsgBox MySum

End Sub

The above procedure will check whether the value in column A is numerical. If it is numerical, then it will add the cell value into MySum.

Input Box (back to top)

Input Box is used to prompt user for input. The example below will ask the use to enter the year he is born and then a message box will display his age.

Sub InputBoxDemo()

MyInput = InputBox("Which year are you born?")

Age = 2007 - MyInput

MsgBox "You are " & Age & " years old."

End Sub

Let's look at another example...

You can use the Input Box function to prompt specific user input during the execution of a subroutine. Look at the sample code below.

Sub InputBoxDemo2 ( )

UserInput = InputBox("Type something", ,"Enter")

End Sub

A input box will be displayed asking the user to type something. The title of this input box is "Enter"

Inserting value into an existing cell that contain value

Here you can use the Insert method. For example, cell A1 contain the word "Credit". You can insert another word "Card" by using the

subroutine below.

Sub InsertWordDemo ( )

Range("A1").Characters(8,4).Inserts("Card")

End Sub

You insert the word "Card" into cell A1 starting at character 8 with 4 character i.e "Card". As a result, cell A1 now contain the word "Credit Card".

Intersect (back to top)

You can specify the location where two ranges intersect by leaving a space between the two range definitions. For example, Range(A2:F5 D2:G6) specifies a range where the range of of cells A2 to F5 intersect with the range of cells D2 to G6.

Sub IntersectDemo ( )

Range(A2:F5 D2:G6).Select

Selection.Name = "Medicine"

End Sub

Range("D2:F5") is the intersection, will be selected and is name "Medicine".

Invoke another macro on a change event

Automatically invoke a macro after entering data into a particular column. The example below will call the macro myProcedure when there is changes in Column 3. The code is insert into the Worksheet_Change () module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column = 3 Then myProcedure

End Sub

¡¡
Inserting a Worksheet Function into a Cell (back to top)

To insert a worksheet function into a cell, you specify the function as the value of the Formula property of the corresponding Range object. The following example insert the AVERAGE worksheet function into the property of range A1 on Sheet1 in the active workbook.

Sub InsertFormulaDemo()

Worksheets("Sheet1").Range("A1 ").Formula = "=AVERAGE( )"

End Sub

If / Then / Else

You use the If-Then structure when you want to execute one or more statements conditionally. The optional Else clause, enable you to execute one or more statements if the condition you¡¯re testing is false. See the example below...

Sub IfThenElseDemo ()

If Time < 0.5 Then

MsgBox "Good Morning"

Else

MsgBox "Good Afternoon"

End Sub

When Time is less than 0.5, it mean that it is before 12 pm.

IsNumeric (back to top)

This syntax enable to check a specified varaibles whether it is a numerical data. The subroutine below will check each cell in range A1:A100 whether it is a numerical value. If yes then it will be added to the total sum.

Sub IsNumericDemo ( )

Total = 0

For Each cell in Range("A1:A100")

If IsNumeric(cell.Value) Then

Total = Total + cell.Value

End If

Next cell

MsgBox Total

End Sub
**************************
Joint two strings

To joint two string, you can use the following example...

Sub JointStringsDemo ( )

aStr = "fish"

bStr = "miles'

dStr = Left(aStr,2)

eStr = Right(bStr,3)

MsgBox dStr & eStr

End Sub

A message will display the word "files" in this case as the first two alphabets of "fish" is joint with the last three alphabets of "miles"

Jump to a specific location

You can jump to a specified location within your procedure by using the GoTo statement.

Sub GotoDemo ( )

Dim x As Integer

x = Range(“A1”).Value

If x < 100 then

Goto addValue

Else

Goto decreaseValue

End If

Exit Sub

addValue:

x = x + 100

Range(“A1”).Value = x

Exit Sub

decreaseValue:

x = 100

Range(“A1”).Value = x

End Sub

The above procedure will jump to the line addValue when x is less than 100

and jump to the line decreaseValue if x is more than 100.

K
Killing A File

Killing a file is equivalent to deleting file. It is easy to implement However the files you are killing must not be in used. For example you want to delete the file “Demo.xls” in your C drive stored in folder My Documents

Sub Killfile()
Dim MyFile As String

On Error Resume Next

MyFile = "c:\My Document\Demo.xls"

kill MyFile

End Sub

L
Left Function (back to top)

This example below uses the Left function to return a specified number of characters from the left side of a string.

Sub LeftFunctionDemo ()

Dim aStr, bStr

aStr = "Hello World" ' Define string.
bStr = Left(aStr, 1) ' Returns "H".
bStr = Left(aStr, 8) ' Returns "Hello Wo".
bStr = Left(aStr, 11) ' Returns "Hello World".

End Sub

List Box (back to top)

This is how you populate a ListBox with items.Insert a Userform in your VBE. Add a ListBox on the UserForm. Activate the Userform_Initialize code window and enter the statement below...

Private Sub UserForm_Initialize()

'Fill the list box
With ListBox1

.AddItem "Credit Cards"
.AddItem "Personal Loan"
.AddItem "Mortgage"
.AddItem "Hire Purchase"
.AddItem "Car Loan"
.AddItem "Forex Margin"
.AddItem "Share Margin"

End With

End Sub

To determine which item you've selected then enter the following code in the ListBox_Change event code window..

Private Sub ListBox1_Change()

Msg = ListBox1.Value
MsgBox "Msg"

End Sub

Lower Case (back to top)

This example show you how to change text in a selected range to lower case .

Sub ChangeLowerCaseDemo ()

Set myRange = Selection.Cells

For Each cell In myRange

If cell.HasFormula = False And IsEmpty(cell) = False Then

cell = LCase(cell)

End If

Next

End Sub

Label (back to top)

You can change the caption of a label during design time by typing the text you want to change on its properties window and programatically by using the code below...

Sub LabelDemo ()

UserForm1.Label1.Caption = "Home Loan'

UserForm.Label2.Caption = "Mortgage"

End Sub

Your Ad Here

Looping through a range of cells

Sometimes you need to run the same block of statements on each cell in a range of cells. To do this, you must combine a looping statement and one or more methods to identify each cell, one at a time, and run the operation. Let use the statement For Each / next to do this.

Sub LoopDemo ( )

Dim myRange as Range

Set myRange = Range(“A1:A100”)

For Each cell in myRange

If Not cell.HasFormula Then

cell.Value = 100

Next cell

The above procedure will loop through every cell in Range(“A1:A100”). If the activecell contain no formula, the procedure will enter the value into 100 it.

M

Make Excel speak (back to top)

The macro below will make Excel speak ...

In Cell "C1" type text, you want Excel to speak and than run the following macro

Sub SpeakCellDemo()

Cells(1, 3).Speak

End Sub

Menus Collection (back to top)

Let me show you how to add a new menu to a menu bar. The macro below will do the trick...

Sub AddMenuDemo()
MenuBars(xlWorksheet).Menus.Add "&MyMenu", "Help"
Set bmenu = MenuBars(xlWorksheet).Menus("MyMenu")

With bmenu.MenuItems
.Add Caption:="&MyProcedure", _
OnAction:="MyProcedure"
End With

End Sub

This will add a new menu "MyMenu" to the Menu bar before the Help menu. Then a sub menu "GoToDemo" is created. When you select "GoToDemo" it will call the procedure, "MyProcedure".

Message Box

To be used to display message to the user and prompt action from the user as well.

Sub MessageBoxDemo ()

Message = "Do you want to continue? "

Ans = MsgBox(Message, vbYesNo, "Proceed?")

If Ans = vbYes Then MyProcedure

End Sub

The above macro ask the user whether to continue with code execution. If user select yes then the macro will call the procedure MyProcedure, else it will end the program.

Month Function (back to top)

This function is use to obtain the month value from a specified date...

Sub MonthDemo

MyDate = #October 15, 2007#

MyMonth = Month(MyDate) 'result is 10

End Sub

Move Method

Use this statement when you want to move around the worksheets in your workbook. The macro below will move Sheet 2 after Sheet 3

Sub MoveSheetDemo ( )

Worksheets("Sheet2").Move _

after:=Worksheets("Sheet3")

End Sub

To move it back then enter this line of code

Worksheets("Sheet2").Move _

before:=Worksheets("Sheet3")

N

Name (back to top)

The name properties is very easy to implement. Add a worksheet and change the name of a worksheet programmatically with the following code...

Sub NameDemo ()

Worksheets.Add.Name = "Personal"

End Sub

NOW Function

This Excel VBA default function to show the Date and time. You can assign the result of this function to a variable, worksheet cell or a message box

like the one below...

Sub NowDemo()

MsgBox "The Date and Time is " & Now

End Sub

Not

The Not statement is used when you to toggle a certain properties for example...

Sub NotDemo()

ActiveWindow.DisplayFormulaBar = Not ActiveWindow.DisplayFormulaBar
ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines

End Sub

Nothing (back to top)

The Nothing keyword is used to free an object variable. By doing so you free up the memory used to store the object in the first place.

Sub NothingDemo ( )

Set MyRange = Range(“A1:A100”)

Set MyRange = Nothing

End Sub

First you allocated the range A1:A100 as MyRange to your system memory. After that you free the object from the system memory.

Numeric Formatting

You can custom format a numeric expression of your choice by using the FormatNumber, FormatCurrency and FormatPercentage functions.

The procedure below show you how to do this.

Sub FormatNumberDemo ( )

x = 50/100

NumNormal = FormatNumber(x,2)

NumPercent = FormatPercent(x,2)

NumCurrency = FormatCurrency(x,2)

MsgBox NumNormal & vbCrLf & NumPercent & vbCrLf & NumCurrency

End Sub

Your Ad Here

O

OffSet (back to top)

The OffSet method enable you to define a range that is a specific offset from another range with the offset being the distance, in row and column.

Sub OffSetDemo ()

Range("C2").Select

ActiveCell.OffSet(1,1).Value = 100 'This will enter the value 100 into cell D3

ActiveCell.OffSet(-1,-1).Value = 200 'This will enter the value 200 into cell B1

Range(ActiveCell, ActiveCell.OffSet(2,2)).Select 'C2:E4 is selected

End Sub

OnKey Method

The OnKey method enable you to assign a specific key to a certain procedure. The following example demonstrate this...

Sub OnKeyDeom ( )

Application.OnKey "^p", "MyProcedure"

End Sub

When you press Ctrl + p, Excel VBA will execute the subroutine MyProcedure.

OnTime (back to top)

You can create a macro that execute at a specific time by using this OnTime event. With the example below, the code will execute "MyProcedure" after 10 seconds.

Private Sub Worksheet_Activate()

Application.OnTime Now + TimeValue("00:00:10"), "Module1.MyProcedure"

End Sub

Open Method

The Open method is very useful whenever you need to run a specify macro when the workbook is open. The procedure below will run the macro Test

in module 1 whenever the workbook is open.

Private Sub Workbook_Open()

Module1.Test

End Sub

Opening a document (back to top)

To open document with Excel VBA, the following example using the Open Method will do the trick…

Workbooks.Open Filename:="C:\My Documents\Bet.doc"

This statement above opens the file "C:\My Documents\Bet.doc" for editing. To open a Excel file as read only, enter the code below...

Workbooks.Open FileName:="Health.xls", ReadOnly:=True

P

Password (back to top)

The procedure below allows you to password protect a specific worksheet so that other user cannot modify it.

Sub PasswordDemo()

Worksheets("Health").Protect Password:="xlpert", AllowFormattingCells:=True

End Sub

User will need to enter the correct password to edit the Worksheet "Health".

Perform mathematical calculation with Excel VBA

You can perform many types of mathematical calculations with Excel VBA

Sub CalcDemo ( )

Dim x As Integer, y As Integer, z As Single

x = 3

y = 4

z = x + y 'addition - here z = 7

z = x * y 'multiplication - here z = 12

z = y - x 'subtraction - here z = 1

z = y / z 'division

z = y^x 'exponential, here z = 64

End Sub

Placing formula in cell

To insert a formula in a cell, the formula must be expressed in A1-style notation, and must include a leading equal sign. Look at the example below...

Sub FormulaDemo ( )

Range("A1").Formula = "=Sum(A2:A3)"

Range("B2").Formula = "=Avearage(C1:C100)"

End Sub

You can also put the exact same formula in each cell, by using an array, as in:

Range("A1:C1").Formula = _

Array("=Sum(A2:A3)", "=Sum(A2:A3)", "=Sum(A2:A3)")

Protecting Worksheets (back to top)

The procedure below demonstrate how you protect all the worksheets in a workbook. A For/Next statement is used to loop through all the worksheets...

Sub ProtectSheetDemo()

For i = 1 To Application.Sheets.Count 'Determine how many worksheets

Worksheets(i).Select
ActiveSheet.Protect

Next i

End Sub

And to unprotect simply...

Sub UnprotectSheetDemo()

For i = 1 To Application.Sheets.Count

Worksheets(i).Select
ActiveSheet.Unprotect

Next i

End Sub

Protecting A Chart

Excel VBA enable user to protect a chart to prevent errors caused by someone attempting to change a protected chart. You can also password protect them. Let me show you an example.

Sub ProtectChartDemo ( )

Charts(1).Protect Password = "Sweet Home", DrawingObjects:=False

End Sub

This will prevent anybody from modifying Chart 1 until the password is enter correctly. To unprotect insert the following code.

Sub UnProtectChart()
Password = "Sweet Home"
Charts(1).Unprotect Password
End Sub

PrintArea Method (back to top)

You can set a print area for a worksheet with the PrintArea property. Let's look at an example...

Sub PrintAreaDemo ()

ActiveSheet.PageSetup.PrintArea = "$A$1 : $F$100" 'Range("A1:F100") to be printed

ActiveSheet.PrintOut

End Sub

Your Ad Here

Q

Quit

When you close all workbooks, you may notice that Excel is still running. If you want to close Excel altogether, then you can use the Quit method with the Application object.

Sub QuitDemo ()

Application.Quit

End Sub

R

Random Number (back to top)

The procedure below will generate a random with the upper and lower value determine by the user, something like the Excel built-in function RANDBETWEEN( ). The code in this procedure takes this format : Int ((upperbound - lowerbound +1) * Rnd + lowerbound). Where the Upperbound is the largest number random number to be generated and Lowerbound is the lowest.

Sub RandDemo ( )

UpperValue = 10

LowerValue = 0

r = Int((UpperValue - LowerValue + 1) * Rnd + LowerValue)

Range("A1").Value = r

End Sub

Here a random number between 0 to 10 will be generated.

Range (back to top)

You can use the Range object represents a group of one or more contiguous cells in an Excel worksheet. It is extremely useful, as it allows us to manipulate the properties of an individual cell or collection of cells in a worksheet. You will probably find yourself using the Range object in every program you write using VBA for the Excel application.

Consider the following code examples that use properties of the Range object.

Range(“B1”).Value=”Column B”

Range(“B1:G1”).Columns.AutoFit

Range(“B1:C1”, “E1:F1”).Font.Bold = True

Let's combine 2 ranges together by using the Union method. The code below will combine 2 ranges into one and will set the font size to 8 if the cell value is a number...

Sub RangeDemo()
Dim rng1 As Range, rng2 As Range, myRange As Range, cell As Range

Worksheets(1).Activate

Set rng1 = Range("A1:B10")
Set rng2 = Range("D1:D10")
Set myRange = Union(rng1, rng2)

For Each cell In myRange
If IsNumeric(cell) = True Then
cell.Font.Size = 8
End If
Next cell

End Sub

Rectangle (back to top)

The following statement adds a rectangle to the Worksheet named "Personal"...

Sub RectangleDemo ()

WorkSheets("Personal").Rectangles.Add 150, 50, 50, 100

End Sub

Redimension An Array

You can redimension an array using the ReDim Statement. Look at the example below…

Sub RedimDemo ( )

Dim Arr() As Integer

Set MyRange = Selection

x = MyRange.Rows.Count

ReDim Arr(x) As Integer

End Sub

The procedure above will determine the number of rows in the selected area and then redimension it accordingly to the Arr()

Replace (back to top)

Sometimes we need to replace certain words or a number that we have entered on a worksheet. It may be troublesome to look for the word or number one by one. You can actually write an Excel VBA procedure to do this and the macro below will do the trick.

Sub ReplaceDemo ()

Dim sht As Worksheet

For Each Sht In Worksheets

sht.Cells.Replace What:="Home Loan", _

Replacement:="Mortgage", LookAt:=xlPart, MatchCase:=False

Next

End Sub

The procedure will loop through all the worksheets in the specify workbook, look for the word "Home Loan" and replace it with the word "Mortgage'

Resizing the Height of Rows (back to top)

You can specify the height of row/s with the Height property. Below is a procedure to set the height of a few rows.

Sub RowHeightDemo ( )

RowHeight = 15

For n = 1 to 10

ActiveSheet.Rows(n).Height = RowHeight

Next n

End Sub

Here the height of row 1 to 10 is set to 15 points.

Resume execution if an Error is encountered

You can ask Excel VBA to continue running even if an error is encounter. Just insert the code at the top of your subroutine like below.

Sub ResumeOnError ( )

Dim x As Integer

On Error Resume Next

x = "abc"

End Sub

Here x is assigned as an integer value. As "abc" is a string valuable, this subroutine will encounter an error. However since we have enter the syntax,

"On Error Resume Next" at the top, the subroutine will continue running.

Retrieve Current Date And Time (back to top)

You can assign the results of the Data or Time function to another variable, a worksheet cell, or another function. The following example assign it to a

message box.

Sub NowDemo ( )

MsgBox(“Current Data and Time:” & Now())

End Sub

Or to a cell just enter this code…

Range(“C1”) = Now( )

Rows property

With this property, you can select the entire row as the Range Object. Just enter the code below.

RangeSelected = Rows(5)

Here you have selected the entire row 5. You can also use the Rows property to determine a specific row within a Range. For example, look at the macro

below.

Sub RowDemo ( )

Set MyRange = Range(“B1:E100”).

MyRange.Rows(2).Select

End Sub

Here you have selected Range(“B2:E2”) as it is the second row within B1:E100.
*********************
SaveAs

Use the Save or SaveAs statement when you want to save the currently selected Excel workbook. This is how you do it programatically...

Sub SaveAsDemo ()

Workbooks(1).SaveAs "Sports"

End Sub

Saving a Document as a Web Page
With Excel VBA, you can save a workbook, worksheet, chart, range, print area to a Web page. The procedure below use the SaveAs method to do so. It saves the active workbook as C:\My Documents\mortgage.htm.

Sub SaveAsWebpage ( )

ActiveWorkbook.SaveAs _

Filename:="C:\My Documents\mortgage.htm", _

FileFormat:=xlHTML

End Sub

Searching wildcard matches

To find cells that match more complicated search criteria, such as those involving wildcard matches, you can use a more manual approach, such as cycling through the cells in the range with a For Each loop and using the Like operator. For instance, the

following code searches for all cells in the range A1:C100 whose contents begin with an "D" and sets the font for these cells to bold (note the use of the evaluation operator to denote the range A1:C100):

Sub WildcardSearchDemo ( )

Dim cell As Range

For Each cell In Range("A1:C100")

If c.Value Like "D*" Then

c.Font.Bold = True

End If

Next cell

End Sub

Send Mail

When you want to email to someone the workbook, just enter the code below in the current workbook VBE module.

SendMailDemo ()

ActiveWorkbook.SendMail Recipients:="admin@xlpert.com"

End Sub

Select (back to top)

You can use the select method with a Worksheet, Range object to highlight your selection. Look at the example below...

Sub SelectDemo ()

Dim MyRange As Range

x = 10

y = 2

Worksheet(1).Select

Set MyRange = Range("A1:C100")

MyRange.Cells(x,y).Select 'here you've selected Cell B10

End Sub

Select Case (back to top)

You can create code to execute a specific block of code based upon the value of a statement using the Select Case syntax. Look at the example below...

Sub GradeDemo

Points = InputBox("How many points?")

Select Case Points

Case 1 to 40

MsgBox ("Failed")

Case 41 to 70

MsgBox ("Passed")

Case 71 to 90

MsgBox ("Good")

Case Else

MsgBox ("Very Good!")

End Select

End Sub

The above procedure will prompt user for the points score and will display a message box on the grade achieved.

Selecting non - contiguous multiple rows

We use the Union method when we want to select a few rows that are non-contiguous. The example below will show you how.

Sub SelectRowsDemo ( )

Dim rng As Range

Set rng = Union(Rows(1), Rows(3), Rows(7))

rng.Select

Selection.Font.Bold = True

End Sub

The above will select row 1.3 and 7 and set the font to bold.

ScreenUpdating

The Application.ScreenUpdating statement is very useful in speeding up your macro. All you need to do is to add this statement at the beginning of your macro...see below

Sub ScreenDemo ( )

Application.ScreenUpdating = False

'Block of codes to be executed

Application.ScreenUpdating = True

End Sub

At the end of your macro, you reverse the effect of the statement by setting it to True again.

SelectionChange Event (back to top)

If you want to add a value into a specified cell when the cell is click, the macro below will do the trick.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("C10").Address Then _
Range("C10").Value = 100
End Sub

When you click on cell C10, the value 100 will be entered on it automatically.

Set Statement (back to top)

This statement assigns the range of cells to the specified Range object. The macro below show you how…

Sub SetDemo ( )

Dim MyRange As Range

Set MyRange = Range(“B1:C100”)

MyRange.Cells(1,2).Value = 100

End Sub

This will enter the value 100 into cell C1.

Setting replacement text properties

To do this enter the following code...

Sub TextDemo ( )

With Application.ReplaceFormat.Font

.Name = "Times New Roman"

.FontStyle = "Bold"

.Size = 10

End Sub

Here your Excel application text format is change accordingly to the above

Your Ad Here

Shapes

The following example adds an oval object to myWs, gives it the name Red Circle, and then sets its foreground color to red and line style.

Sub ShapeDemo ( )

Set myWs = Worksheets(1)

With myWs.Shapes.AddShape(msoShapeOval, _

100, 100, 50, 50)

.Name = "Red Circle"

.Fill.ForeColor.RGB = RGB(255, 0, 0)

.Line.DashStyle = msoLineDashDot

End With

End Sub

As Excel assigned each shape a default name when you add it to the Shapes collection, the macro above give the shape a more meaningful name by using the Name property.

SpecialCells Method (back to top)

A very useful statement when you want to select a cell that contain formula, numerical, non-empty etc. This is how you implement the statement...

Sub SpecialCellsDemo ()

On Error Resume Next

Selection.SpecialCells(xlFormulas, xlNumbers).Select

Selection.Font.Bold = True

End Sub

The above macro will change the font to bold if the cell contain formula.

Sort Worksheets By Name (back to top)

You can use Excel VBA the order of the worksheets in a workbook based upon the worksheet name. The macro below show you how...

Sub SortSheetsDemo ( )

Dim numSht As Integer

Dim shtName As String

numSht = Sheets.Count

For x = 1 to numSht

shtName = Sheets(x).Name

For y = x to numSht

If Sheets(y).Name < shtName Then

shtName = Sheets(y).Name

End If

Next

Sheets(shtName).Move Before:= Sheets(x)

Next

End Sub

The above macro will attempt to move the sheet with the smallest name to the front (left). For example, before sorting you have 3 sheets name Hire Purchase, Mortgage and Bet. After sorting the sheets become Bet, Mortgage and Hire Purchase.

Space function

Below is how you add space to a string.

Sub AddSpacesDemo()

Dim MyString As String

MyString = "Hello" & Space(8) & "World"

MsgBox MyString

End Sub

T

ThisWorkbook

This statement refer to the current active workbook. For example, you want to close the active workbook without saving any changes you can enter this code...

ThisWorkbookDemo ()

ThisWorkbook.Close SaveChanges:=False

End Sub

Timer Function (back to top)

This is also an Excel built-in function. The example below will demonstrate how to implement the timer function to find out how long a program has run.

Sub TimerDemo ()

Dim start as Date, finish As Date

start = Timer

For x = 1 to 1000

For y = 1 To 1000

'execute some code here

Next y

Next x

finish = Timer

MsgBox Format(finish - start, "000")

End Sub

Top of the screen (back to top)
The macro below enable the activecell to be at the top of the screen and to the left on the screen.

Sub TopLeftDemo()
ActiveCell.Select

With ActiveWindow
.ScrollColumn = ActiveCell.Column
.ScrollRow = ActiveCell.Row
End With

End Sub

TypeName (back to top)

The TypeName is a built-in Excel function. It return a string that describes its argument.

Sub TypeNameDemo ()

Set MyObject = Selection 'a range, chart, rectangle is selected

MsgBox (TypeName(MyOject)) 'will return the object type

MyString = "Travel"

MsgBox (TypeName(MyString))

End Sub

Let see how you can use this function to more practical purpose..

Sub TypeNameDemo2()

If TypeName(Selection) = "Rectangle" Then
ActiveSheet.Rectangles.Delete 'if selected object is a rectangle, then it will be deleted
ElseIf TypeName(Selection) = "Range" Then
Selection.Font.Bold = True
End If

End Sub

U

Unload (back to top)

When you want to unload a userform

Sub UnloadDemo ()

Unload UserFrom1

End Sub
Buy Jewellery @ Rs.1
India's No.1 Jewellery Auctions Site..Bid Now :)

Your Ad Here

Union

Use the Union method when you want to create a multiple area Range, where the block of cells are non - contiguous and which may not be connected. This example will combine 2 block of cells together and loop through each cell. If the cell contain formula then it will set its font color to red.

Sub UnionDemo ()

Dim MyUnion As Range

Set MyUnion = Union(Range("A1:A10), Range("D4:E5")

For Each cell in MyUnion

If cell.HasFormula = True Then

cell.Font.ColorIndex = 3

End If

Next cell

End Sub

URL (back to top)

To select a link and embed it on the worksheet cell, the procedure below will do the trick...

LocateURLDemo ()

Range("A1").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"http://www.xlpert.com", TextToDisplay:="xlpert.com"

End Sub

When you click on cell A1, you will be able to connect to the website, "http://www.xlpert.com"

Unload

The Unload statement is use to remove the user form from memory. There are two type of statement where you can unload an active userform. For example if you’ve an active UserForm1. To unload enter the code below…

Unload Me

or

Unload UserForm1

But if you need to retain programmatic control, use the Hide() method instead. You still can access value in the hide userform using this Hide method. For example, you need to refer to a value on a textbox embed on the hide userform. This is how you use the Hide method...

UserForm1.Hide

V

Value (back to top)

You can assign value to variables after declaring them using the Dim statement. The correct data type will need to be assigned to the variables declared else there will be a type mismatch error. See the example below that cause an type mismatch error

Sub ValueDemo ()

Dim Int as Integer

x = "abc"

Int = x

End Sub

Sometimes you need to check a value before assign them to the correct variables. Here you only want to sum all the numeric value in a selected Range. If the value in a cell is a string it will jump to the next cell without adding the non-numeric cell.

Sub ValueDemo2 ()

Dim Total As Single

Range("B1:B100").Select

For i = 1 To Selection.Cells.Count

If IsNumeric(Range("B" & i)) Then

Total = Total + Range("B" & i).Value

End If

Next i

Range("B101").Value = Total

End Sub

Visible

The "Visible" property can take 3 different values. The first two are True or False meaning the a certain sheet is or is nor visible that it is either hidden or not.

Worksheets("Good ").Visible= True
Worksheets("Good 1").Visible= False

Remember that formulas in cells are calculated even if the sheet is hidden but before you can do anything programmatically on the sheet you must unhide it:

Worksheets("Good ").Visible = True
Worksheets("Good "). Select

Range(“A1”).Value = 10

The third value that the property "Visible" can take is very interesting. A sheet can be very hidden " Worksheets("Good ").Visible = xlVeryHidden". In this state not only the sheet is hidden but you can't see its name when in Excel you go to "Format/Sheets/Unhide". The value xlVeryHidden can only be changed programmatically. That means that only users that have access to the VBA code can unhide this sheet. If your code is protected by a password only users with the password can access the code and modify the "xlVeryHidden" value. You can use this value of the property "Visible" to hide confidential information like credit card details and personal info or to hide parameters that you don't want modified by the user.

Worksheets("Good ").Visible = True
Worksheets("Good "). Select

Range(“A1”).Value = 10

And hide it again…

Worksheets("Good ").Visible = xlVeryHidden

Remember also that formulas on other sheets referring to cells of a hidden or very hidden sheet work even if the sheet is hidden or very hidden.

W

Wait method

Pauses a running macro until a specified time. This is very useful when you want to display a splash to welcome the user. Create a userform and rename to "Welcome". Enter this statement on the Workbook Open event. The Welcome userform will be display for 5 seconds and unload after that.

Sub Workbook_Open ()

Welcome.Show

Application.Wait Now + (TimeValue("00:00:05")

Unload Welcome

End Sub

While Wend Statement (back to top)

Excel VBA has a few syntax that we can use when we want to continue to execute a procedure as long as certain condition is evaluates to true. The While...Wend statement is appropriate to be used when you want to loop through a block of code until a condition is satisfied. Let's look at an example below...

Sub WhileWendDemo ()

start = Timer

finish = Timer

elapsed = finish - start

While elapsed < 10 'condition to be evaluate

ActiveSheet.Range("A1").Value = elasped

finish = Timer

elapsed = finish - start

Wend 'condition to be satisfied

Msg elapsed & " second has passed."

End Sub

The procedure above use the Timer function and the While...Wend loop to execute. It will stop after 10 seconds has passed.

Width Property (back to top)

You can specify the width of columns with the width property. Below is a procedure to set the width of a few columns.

Sub ColWidthDemo ( )

Colwidth = 20

For n = 1 to 10

ActiveSheet.Column(n).Width = 15

Next n

End Sub

Here the width of Column A to J is set to 20 points.

With statement

The With statement enables you to specified statements that refer to the same object.

Sub WithDemo ()

Set MyRange = Range("C1:C100")

With MyRange

.Font.Bold = True

.Font.ColorIndex = 6

.Font.Size = 12

End With

End Sub

X

XoR (back to top)

Perform a logical exclusions on two expressions. If both expressions are True or both are False then the result is False. If one of the expressions is True or False then it will return True. The code below will return False as both expression are correct. The second example will return True as one of the expression is True.

Sub xorDemo ()

x = 4

y = 5

If x = 4 Xor y = 5 Then Exit Sub 'result is False as both expression are correct

End Sub

Sub xorDemo2 ()

x = 4

y = 5

If x = 4 Xor y = 6 Then 'result is True as one of the expression is True

MsgBox "This is True

End If

End Sub

The above macro will return True as one is True and the other is False.

To put it simply : exp1 = True; exp2 = True then False

exp1 = False; exp2 = False then False

exp1 = True; exp2 = False then True

Y

Year Function (back to top)

This function is use to obtain the year from a specified date.

Sub YearDemo()

MyDate = #10/15/2007#
MyYear = Year(MyDate) 'result is 2007

End Sub

Yes / No Message box

Sometime you need to get user response on whether to continue on a particular action. The Excel VBA code below will show a Yes / No message box to prompt the user.

Sub YesNoDemo ( )

Dim Ans As String

Dim Question As String

'Place your text here

Question = "Do you want to continue?"

'Display MessageBox

Answer = MsgBox(Question, vbQuestion + vbYesNo, "Continue?")

If Ans = vbNo Then

'No Action

Else

'Execute code here

End If

End Sub

Your Ad Here

Z

ZOrder Method (back to top)

You can moves the specified shape in front of or behind other shapes in the Excel shapes collection. The example below adds a rectangle to myWS and then places the rectangle to the back in the z-order if there is at least one other shape on the document.

Sub ZOrderDemo()
Set myWS = Worksheets(2)
Set myRec = myWS.Shapes.AddShape(msoShapeRectangle, 100, 100, 150, 200)

Do Until myRec.ZOrderPosition < 2
myRec.ZOrder msoSendBackward
Loop

End Sub

my computer lessons

Wednesday, February 23, 2011

Loops and Arrays in VBA

Loops and Arrays in VBA

Loops and Arrays in VBA

Optimizing VBA

Tuesday, February 22, 2011

Sunday, February 20, 2011

About Me

Followers