Excel VBA Example: Date Calculations

To add or subtract an interval (a relative date) from a date, we could use DateAdd function. The syntax is:

DateAdd(interval, number, date)

The DateAdd function syntax has these named arguments:

Part	Description
interval	Required. String expression that is the interval of time you want to add.
number	Required. Numeric expression that is the number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past).
date	Required. Variant (Date) or literal representing date to which the interval is added.

Settings

The interval argument has these settings:

Setting	Description
yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weekday
ww	Week
h	Hour
n	Minute
s	Second

The following example add two months to October 31, 2009:

MsgBox DateAdd("m",2,"31-Jan-09"))

0 Comments

Read More »

Determining whether a path exists

To check whether a path exists or not, we can use Excel VBA's GetAttr function. The following function returns True if a specified path exists, and return False otherwise:

Function isPathExist(ByVal pathname As String) As Boolean
On Error Resume Next
isPathExist = GetAttr(pathname) And vbDirectory = vbDirectory
End Function

0 Comments

Read More »

Determining Whether A File Exists Or Not

There are several ways to check whether a file exists. By using Microsoft Excel VBA statements and functions, or by using FileSystemObject (Microsoft Scripting Library). The following function returns True if a particular file exist, and return False if file does'nt exist. This function uses Dir function to check whether a file exists or not.

Function isFileExist(ByVal fname As String) As Boolean
isFileExist = False
If Dir(fname) <> "" Then isFileExist = True
End Function

Here's example how to use the function above:

Sub FunctionTest()
If isFileExist("D:\SomeFile.txt") = False Then
MsgBox "File not exists."
Else
MsgBox "File already exist."
End If
End Sub

The next function do exactly as previous function, but this function uses FileSystemObject to check whether a file exist:

Function isFileExist2(ByVal fname As String) As Boolean
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    isFileExist2 = fs.FileExists(fname)
End Function

FIN.

0 Comments

Read More »

Data Alignment

In Excel VBA macro we can control the alignment of data in a cell, both horizontally and vertically. To change alignment, we can use the following properties of the Range object:

• HorizontalAlignment. Set to xlLeft, xlCenter, xlRight, xlDistributed, or xlJustify.
• VerticalAlignment. Set to xlTop, xlCenter, xlBottom, xlDistributed, or xlJustify.

Following example sets horizontal alignment to justify:

Worksheets("Sheet1").Range("A1:D5").HorizontalAlignment = xlJustify

0 Comments

Read More »

Font Formatting

When we working in Excel, if we want to changes font properties we may use Font tab in the Format Cells dialog box. In Excel VBA, we control the font's appearance by the Font object. The Font object have several properties that correspond to various aspects of the font's appearance. Here are several list of font's properties:

Property	Description
Name	The name of the font.
Bold	True if the font is bold. Read/write Variant.
Italic	True if the font style is italic. Read/write Boolean.
Underline	Returns or sets the type of underline applied to the font. Can be set to xlUnderlineStyleNone, xlUnderlineStyleSingle, xlUnderlineStyleDouble, xlUnderlineStyleSingleAccounting, xlUnderlineStyleDoubleAccounting. Read/write Variant.
Size	Returns or sets the size of the font. Read/write Variant.
Subscript	True if the font is formatted as subscript. False by default. Read/write Variant.
Superscript	True if the font is formatted as superscript; False by default. Read/write Variant.
Strikethrough	True if the font is struck through with a horizontal line. Read/write Boolean.

This example sets font name of range A1:B4 to Tahoma in ActiveSheet in ActiveWorkbook:

ActiveWorkbook.ActiveSheet.Range("A1:B4").Font.Name = "Tahoma"

The Color property uses an RGB value, which identifies a color in terms of its red, green,
and blue components. To set this property, use the RGB function:

RGB(r, g, b)

The next example sets font color of range B4 to Blue in ActiveSheet in ActiveWorkbook:

ActiveWorkbook.ActiveSheet.Range("A1:B4").Font.Color = RGB(0, 0, 255)

We can also use predefined constants to sets font color, they are vbBlack, vbRed, vbGreen, vbYellow, vbBlue, vbMagenta, vbCyan, and vbWhite.

This example do exactly as previous example:

ActiveWorkbook.ActiveSheet.Range("A1:B4").Font.Color = vbBlue

0 Comments

Read More »

Changing Row and Column Size

Sometimes, in Microsoft Excel we need to change width of columns or height of rows in a worksheet to accomodate data they contain. In Excel Visual Basic for Application (VBA macro), to change columns width we can use ColumnWidth property. Following excel macro code sets width of column C in "Sheet1" worksheet to 24:

Sheets("Sheet1").Columns("C").ColumnWidth = 24

To change columns width to fit data in columns, we can use AutoFit method. The following example uses AutoFit method to change the size of C:F in the "Sheet1" worksheet:

Sheets("Sheet1").Columns("C:F").AutoFit

RowHeight property is used to change rows height of a range. For example:

Sheets("Sheet1").Rows(2).RowHeight = 56

0 Comments

Read More »

Number Formatting in Excel VBA Macro

Number formatting controls how numbers on cells are displayed, it has no effect on cells that contain text. In Microsoft Excel, if we want to apply number formatting we can use Format Cells dialog box. To format numbers in VBA macro we can use the NumberFormat property. Following are some number formatting codes to format numbers.

Number Formatting Codes

Character	Meaning	Code example	Format example
#	Significant digit	##.#	10.78 displays as 10.9
0	Nonsignificant 0	#.00	5.4 displays as 5.40
.	Decimal point	##.##	14.55 displays as 14.55
$	Currency symbol	$#.##	56.78 displays as $56.78
%	Percent symbol	#.#%	0.075 displays as 7.5%
,	Thousands separator	#,###	123000 displays as 123,000

Here is the example VBA code to display numbers with no commas or special characters, and no decimal places:

Sub NumFormat()
Range("A1").NumberFormat = "0"
End Sub

We can display positive and negative numbers differently. Following number format code will display negative numbers in red color.

#.##;[Red]#.##

To specify a display color, include the color name enclosed in square brackets at the start the format code. The available color names are:

• Black
• Blue
  
• Cyan
• Green
  
• Magenta
  
• Red
• White
• Yellow

0 Comments

Read More »

Excel VBA Macro: Creating a Chart

In this post, we will create a chart using Microsoft Excel VBA macro.

Embedded Chart

Use an embedded chart when you want the chart displayed as part of a worksheet along with the data and/or other charts. Here is the example to create an embedded chart:

Public Sub EmbeddedChart()
Dim myChartObject As ChartObject
Dim myChart As Chart
Set myChartObject = Worksheets("Sheet1").ChartObjects.Add(100, 150, 300, 225)
Set myChart = co.Chart
myChart.SetSourceData Source:=Worksheets("Sheet1").Range("A2:E6"), PlotBy:=xlRows
End Sub

Chart Sheets

Use a Chart Sheet when you want a chart displayed in different sheet.

Public Sub ChartSheet()
Dim mychart As Chart
Set mychart = ActiveWorkbook.Charts.Add
mychart.SetSourceData Source:=Worksheets("Sheet1").Range("A2:E6"), PlotBy:=xlRows
End Sub

0 Comments

Read More »

Protecting Excel VBA Macro Code

When we working with Excel VBA macro, sometimes we need to protect our VBA macro code. So nobody can change or modify VBA macro that we have create. Here are steps to protect VBA Project:

• Go to Visual Basic Editor by pressing Alt+F11 key.

• In menu toolbar, click Tools -> VBAProject Properties...

• In VBAProject - Properties dialog box, click Protection tab, and then check Lock project for viewing checkbox.
  

• Enter desired password to protect VBA project, then click OK button.
• Save the VBA project.
  

FIN.

0 Comments

Read More »

Selecting A Row Or Column

To select the entire column we can use the EntireColumn property. The following excel VBA macro example demonstrates how to select the column of the active cell.

Sub SelectColumn()
ActiveCell.EntireColumn.Select
End Sub

The next following excel VBA macro example demonstrates how to perform an operation on all cells in the selected row. This following procedure changes all cells font size to 18 in the row that contains the active cell.

Sub ChangeFontSize()
ActiveCell.EntireRow.Font.Size = 18
End Sub

0 Comments

Read More »

Runs An Executable Program In Excel VBA Macro

Sometimes, it's very useful to run another application from Microsoft Excel. To runs another application in Microsoft Excel, we can use VBA's Shell function. The following example, will lunch the Notepad application and if the Shell function failed to launch the application, it generates an error.

Sub ExecNotepad()
On Error Resume Next
AppVal = Shell("C:\WINDOWS\NOTEPAD.EXE", 1)
If Err <> 0 Then
MsgBox "Can't start the application.", vbCritical, "Error"
End If
End Sub

0 Comments

Read More »

The InputBox Function

In Microsoft Excel VBA Macro, to obtain a single input from the user, we can use the InputBox function.
The InputBox function is useful for obtaining a single input from the user. Here is the InputBox function syntax:

InputBox(prompt[,title][,default])

The arguments:

• prompt: Required. Text that is displayed in the input box.

• title: Optional. Text that appears in the input box’s title bar.

• default: Optional. The default value.

The following is an example of how to use the InputBox function:

Sub AskUserName
ActiveSheet.Range("A1").Value = InputBox("Your name?","Input Name")
End Sub

0 Comments

Read More »

The MsgBox Function

In Excel VBA macro, if we want display a message to the user, we can use the MsgBox function. The syntax for the MsgBox function is as follows:


MsgBox(prompt, buttons, title, helpfile, context)

prompt	Required. String expression displayed as the message in the dialog box. The maximum length of prompt is approximately 1024 characters, depending on the width of the characters used. If prompt consists of more than one line, you can separate the lines using a carriage return character (Chr(13)), a linefeed character (Chr(10)), or carriage return – linefeed character combination (Chr(13) & Chr(10)) between each line.
buttons	Optional. Numeric expression that is the sum of values specifying the number and type of buttons to display, the icon style to use, the identity of the default button, and the modality of the message box. If omitted, the default value for buttons is 0.
title	Optional. String expression displayed in the title bar of the dialog box. If you omit title, the application name is placed in the title bar.
helpfile	Optional. String expression that identifies the Help file to use to provide context-sensitive Help for the dialog box. If helpfile is provided, context must also be provided.
context	Optional. Numeric expression that is the Help context number assigned to the appropriate Help topic by the Help author. If context is provided, helpfile must also be provided.

The buttons argument settings are:

Constant	Value	Description
vbOKOnly	0	Display OK button only.
vbOKCancel	1	Display OK and Cancel buttons.
vbAbortRetryIgnore	2	Display Abort, Retry, and Ignore buttons.
vbYesNoCancel	3	Display Yes, No, and Cancel buttons.
vbYesNo	4	Display Yes and No buttons.
vbRetryCancel	5	Display Retry and Cancel buttons.
vbCritical	16	Display Critical Message icon.
vbQuestion	32	Display Warning Query icon.
vbExclamation	48	Display Warning Message icon.
vbInformation	64	Display Information Message icon.
vbDefaultButton1	0	First button is default.
vbDefaultButton2	256	Second button is default.
vbDefaultButton3	512	Third button is default.
vbDefaultButton4	768	Fourth button is default.
vbApplicationModal	0	Application modal; the user must respond to the message box before continuing work in the current application.
vbSystemModal	4096	System modal; all applications are suspended until the user responds to the message box.
vbMsgBoxHelpButton	16384	Adds Help button to the message box
VbMsgBoxSetForeground	65536	Specifies the message box window as the foreground window
vbMsgBoxRight	524288	Text is right aligned
vbMsgBoxRtlReading	1048576	Specifies text should appear as right-to-left reading on Hebrew and Arabic systems

The MsgBox function returns an integer value that identifies the button the user selected
to close the dialog box.

Constant	Value	Description
vbOK	1	OK
vbCancel	2	Cancel
vbAbort	3	Abort
vbRetry	4	Retry
vbIgnore	5	Ignore
vbYes	6	Yes
vbNo	7	No

Here is an example of using MsgBox funtion:

Sub ShowMsgBox()
   Dim response As Integer
    
   response = MsgBox("Do you want to continue?", vbYesNo + vbQuestion)

If response = vbYes Then
Range("A1").Value = "'Yes' button clicked."
Else
      Range("A1").Value = "'No' button clicked."
   End If
End Sub

0 Comments

Read More »

Looping through a range

The following microsoft excel VBA macro example demonstrates how to loop through all the cells in a range. This example uses the For Each...Next statement to search the word "Microsoft Excel VBA" in a range and changes its font style to bold. In this case, the range is from A1 to E5.

Sub ChangeFontStyle()
Dim Cell As Range

For Each Cell In Range("A1:E5")
If LCase(Cell.Value) = "microsoft excel vba" Then
Cell.Font.Bold = True
End If
Next Cell
End Sub

0 Comments

Read More »

Moving a range

In this Excel VBA Macro Examples section, I will show you how to move a range. This following example will move a range A4:E4 to I15:M15 in active sheet.

Sub MoveRange()
ActiveSheet.Range("A4:E4").Cut _
Destination:=ActiveSheet.Range("I15")
End Sub

If Destination argument is omitted, Microsoft Excel cuts the range to the Clipboard.

0 Comments

Read More »

How To Delete Cells That Contain Certain Word

Sometimes, when we working with excel we need to delete cells that contain certain word. In this section, we will create excel vba macro code to delete cells that contain certain word.

Firstly, we have to create a function that we need for our next procedure. This function will count number of certain word in active sheet.

Function countText(ByVal searchText As String)
Dim rS As Range, counter As Long

counter = 0
Set rS = Cells.Find(searchText, LookIn:=xlValues, LookAt:=xlWhole)

If Not rS Is Nothing Then
fAddress = rS.Address
Do
counter = counter + 1
Set rS = Cells.FindNext(rS)
Loop While Not rS Is Nothing And rS.Address <> fAddress
End If

countText = counter
End Function

And then, here is the main part of the procedure that will delete cells that contain certain word.

Sub DeleteCells(ByVal sText As String)
Dim r As Range

While countText(sText) > 0
Set r = Cells.Find(sText, LookIn:=xlValues, LookAt:=xlWhole)

If Not r Is Nothing Then
r.Delete
End If
Wend
End Sub

Here is an example how to use the procedure above. This example will delete cells that contain word "blablabla" in active sheet.

Sub TheExample()
DeleteCells ("blablabla")
End Sub

0 Comments

Read More »

How To Replace Data Using Excel VBA Macro Code

In my case, I often manipulating data, including but not limited to searches all data in a worksheet and makes the specified replacement.

Here's an example how replace data using excel vba macro code. This example will replace all cell value that contain word "micro excel" to "macro excel" in active sheet.

Sub ReplaceAllData()

    Cells.Replace _
        What:="micro excel", Replacement:="macro excel", _
LookAt:=xlWhole, MatchCase:=False

End Sub

If you to make the search case sensitive you can change the MatchCase property to true. And also if you want to replace data that contain part of the searched data you can change the LookAt property to xlPart.

0 Comments

Read More »

Find A Particular Text Within Ranges And Return Its Row Position

In Microsoft Excel, sometimes we need to find the row position of particular text or even formula, to be able to insert certain of data below the corresponding text or even to delete row that contain the corresponding text.

This is an excel vba macro code to return row position that contain specific text.

Function rowPosition(ByVal searchText As Variant, _
    Optional ByVal stLookAt As XlLookAt = xlPart) As Long
    
    Dim rPos As Long, rS As Range
    
    rPos = 0
    
    Set rS = Cells.Find(searchText, LookIn:=xlValues, LookAt:=stLookAt)
    
    If Not rS Is Nothing Then rPos = rS.Row
    
    rowPosition = rPos
    
End Function

The function above have two arguments:

searchText : the text you are searching for
stLookAt : use xlWhole if you want to search the whole text, or use xlPart if otherwise

Here is example how to use the function above. This example deletes entire row that contain word "Test".

Sub RunExample()
    Dim rwPos As Long
       
    'Find row position
    rwPos = rowPosition("Test", xlPart)
    
    'If found then delete entire row
    If rwPos > 0 Then Rows(rwPos).Delete
End Sub

0 Comments

Read More »

Check Whether Workbook Is Already Open Or Not

I often transfer data from current excel workbook to another excel workbook repeatedly. And I prefer creating vba macro to automate the task rather than doing it manually, which is very time consuming. So, before I open the destination excel workbook, I need to check whether the destination excel workbook is already open or not.

The following vba macro code is the function to check whether a workbook is already open or not. This function is quiet simple, it just do looping in Workbooks collection to check all opened workbook, if there is a workbook that have same name with the workbook we are looking for, then the function will return True or False if otherwise.

Function isWorkbookOpen(bookName As String) As Boolean
Dim vbResult As Boolean
Dim wbs As Workbook

vbResult = False
For Each wbs In Workbooks
If UCase(wbs.Name) = UCase(bookName) Then
vbResult = True
Exit For
End If
Next wbs
isWorkbookOpen = vbResult
End Function

This example show you how to use the function above.

Sub openWorkbook()
If isWorkbookOpen("macroexcel.xls") = False Then
Workbooks.Open "d:\macroexcel.xls"
Else
MsgBox "Workbook macroexcel.xls is already open."
End If
End Sub

0 Comments

Read More »

Excel VBA Macro Example: Protect & Unprotect Worksheet/Excel File

Imagine this; you are working with protected excel file, so only authorized user can access the file. Let's say you are the authorized user, and you are going to create macro excel that need an access to the protected file.

This vba macro example, sets password to excel file named "excel.xls". This example assumes excel file named "excel.xls" already open.

Sub ProtectExcel()
'write password
Workbooks("excel.xls").Password = "macroexcel"

'save workbook
Workbooks("excel.xls").Save
End Sub

This example opens the protected excel file named "excel.xls" on the D:\ drive.

Sub OpenProtectedExcel()
'open protected excel file
Workbooks.Open Filename:="D:\excel.xls", _
Password:="macroexcel"
End Sub

This example remove protection from worksheet named "Sheet1" and protect it with new password.

Sub xProtect()
'Assumes excel.xls already open
'unprotect sheet
Workbooks("excel.xls").Sheets("Sheet1").Unprotect "password"

'protect sheet with new password
Workbooks("excel.xls").Sheets("Sheet1").Protect "vbamacro"

'save changes
Workbooks("excel.xls").Save
End Sub

0 Comments

Read More »