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

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

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

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

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

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

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