Excel VBA Code Snippet

51 Pages·2015·0.53 MB·English

Excel VBA Code Snippet

Author: Amit Shah
Version: Version 1.0
Table of Contents
Code Snippet ................................................................................................................................................. 7
Code to Open Existing Workbook ................................................................................................................. 8
Code to Create Excel Object:- ................................................................................................................... 8
Code to Open Excel and Create Object for the same:- ............................................................................. 8
Code to open txt file in the new workbook:- ............................................................................................ 8
Code to Suppressing Excel Warnings:- ...................................................................................................... 9
Code to make excel visible:- ..................................................................................................................... 9
Code to check whether a Sheet in an open workbook exists:- ................................................................. 9
Code to create object to access specific worksheet:- ............................................................................... 9
Code to make worksheet unprotect during runtime:- ........................................................................... 10
Code to make worksheet protected during runtime:- ............................................................................ 10
Code to Make Specified range / column / cell locked during runtime:- ................................................. 10
Code to get Row Count of Specific worksheet:- ..................................................................................... 10
Code to get Column Count of Specific worksheet:- ................................................................................ 11
Code to select Single cell, Contiguous, Non Contiguous range:- ............................................................ 11
Code to get address of the specified cell:- .............................................................................................. 11
Code to get value of the specified cell:-.................................................................................................. 11
Code to get Formula of the Specified cell:- ............................................................................................. 11
Code to use Offset Range Property:- ...................................................................................................... 12
Code to Removes duplicate values:- ....................................................................................................... 12
Code to Creates a data series in the specified range:- ........................................................................... 12
Code to change font of the specified Range or Cell:- ............................................................................. 13
Code to change font Style of the specified Range or Cell:-..................................................................... 13
Code to change font Size of the specified Range or Cell:- ...................................................................... 13
Code to change font color of the specified Range or Cell:- .................................................................... 13
Code to fill Red color of the specified Range or Cell:- ............................................................................ 13
Code to change worksheets / tab color:- ................................................................................................ 14
Code to move worksheets:- .................................................................................................................... 14
Code to change horizontal alignment of the specified Range or Cell:- .................................................. 14
Code to change vertical alignment of the specified Range or Cell:- ....................................................... 14
Code to wrap text of the specified Range or Cell:- ................................................................................. 14
Code to assign column width for the specified column:- ....................................................................... 15
Code to make columns data autofit:- ..................................................................................................... 15
Code to add validation at the specified column during runtime:- .......................................................... 15
Code to delete validation at the specified column during runtime ........................................................ 16
Code to Performs an autofill on the cells in the specified range:- ......................................................... 16
Code to Represents the collection of hyperlinks for a worksheet or range:- ......................................... 16
Code to Clear Contents of the specified Range or Cell:- ......................................................................... 16
Code to Merge selected range:- ............................................................................................................. 16
Code to UnMerge selected Cell:- ............................................................................................................ 17
Code to change the Formatcell:-............................................................................................................. 17
Code to change the Formatcell:-............................................................................................................. 17
Code to AutoFilter with Single Criteria for the column:- ........................................................................ 17
Code to AutoFilter using RegEx with Single Criteria for the column:- .................................................... 17
Code to AutoFilter Using Cell Color with Single Criterial for the Column:- ............................................ 17
Code to AutoFilter with Multiple Criteria for the column:- .................................................................... 18
Code to sort the values in the worksheet in the ascending & descending order:- ................................ 18
Code to do AdvancedFilter on the specified range:- .............................................................................. 18
Code to let you modify any sequence using characters Property:- ........................................................ 19
Code to get address of the range from Filtered Cells:- ........................................................................... 19
Code to insert Column during runtime:- ................................................................................................. 19
Code to Delete Column during runtime:- ............................................................................................... 19
Code to insert Row during runtime:- ...................................................................................................... 19
Code to Delete row during runtime:- ..................................................................................................... 19
Code to Add Conditional formatting to a specified cell or range:- ......................................................... 20
Code to Delete Conditional formatting to a specified cell or range:- ..................................................... 20
Code to Add Comment to a specified cell or range:- .............................................................................. 20
Code to Delete Comment to a specified cell or range:- .......................................................................... 21
Code to copy the specified range during runtime:- ................................................................................ 21
Code to Paste the specified range during runtime:- ............................................................................... 21
Code to PasteSpecial the specified range during runtime:- ................................................................... 21
Code to delete worksheets:- ................................................................................................................... 21
Code to Save the workbook:- .................................................................................................................. 22
Code to close the workbook:- ................................................................................................................. 22
Code to Quit the workbook:- .................................................................................................................. 22
Formula to get count of total working days in the month:- ................................................................... 22
MkDir Statement:- .................................................................................................................................. 22
RmDir Statement:- .................................................................................................................................. 22
FileCopy Statement:- .............................................................................................................................. 23
FileLen Function:- .................................................................................................................................... 23
FileDateTime Function:- .......................................................................................................................... 23
Kill Statement:- ....................................................................................................................................... 23
VLookup Function:- ................................................................................................................................. 24
HLookup Function:- ................................................................................................................................. 24
PIVOT Table Function:- ........................................................................................................................... 25
CurrentRegion Property:- ........................................................................................................................ 27
Code to Create New Workbook and SaveAs on the Local .......................................................................... 28
Code to add workbook during run-time using excel object:- ................................................................. 28
Code to add worksheet during run-time using excel object:- ................................................................ 28
Code to SaveAs Workbook during run-time using excel object:- ........................................................... 28
Code to delete worksheets:- ................................................................................................................... 28
Code to Save ActiveWorkbooks:- ............................................................................................................ 28
Code to Close the workbook:- ................................................................................................................ 29
Code to Quit the workbook:- .................................................................................................................. 29
Excel VBA Properties, Methods & Events ................................................................................................... 30
WORKBOOK:- .......................................................................................................................................... 30
Properties - .......................................................................................................................................... 30
Methods - ............................................................................................................................................ 30
Events - ................................................................................................................................................ 30
WORKSHEETS:- ........................................................................................................................................ 30
Properties - .......................................................................................................................................... 30
Methods - ............................................................................................................................................ 30
Events - ................................................................................................................................................ 30
RANGE:- ................................................................................................................................................... 31
Properties - .......................................................................................................................................... 31
Methods - ............................................................................................................................................ 31
Excel VBA Programming STRING Functions ................................................................................................ 32
ASC:- ........................................................................................................................................................ 32
CHR:- ....................................................................................................................................................... 32
Instr:- ....................................................................................................................................................... 33
InstrB:- ..................................................................................................................................................... 33
InstrRev:- ................................................................................................................................................. 33
Len:- ........................................................................................................................................................ 34
Left:- ........................................................................................................................................................ 34
Right:- ...................................................................................................................................................... 34
Trim, LTrim & RTrim:- .............................................................................................................................. 34
Lcase:- ..................................................................................................................................................... 35
Ucase:- .................................................................................................................................................... 35
Space:- ..................................................................................................................................................... 35
Mid:- ........................................................................................................................................................ 35
Replace:- ................................................................................................................................................. 35
StrReverse: .............................................................................................................................................. 36
StrComp:- ................................................................................................................................................ 36
String:- ..................................................................................................................................................... 36
Split:- ....................................................................................................................................................... 36
LBound:- .................................................................................................................................................. 37
UBound:- ................................................................................................................................................. 37
Date:- ...................................................................................................................................................... 37
DateAdd:- ................................................................................................................................................ 37
DateDiff:- ................................................................................................................................................. 38
DateSerial:-.............................................................................................................................................. 38
Msgbox:- ................................................................................................................................................. 38
InputBox:-................................................................................................................................................ 38
Join:- ........................................................................................................................................................ 39
Filter:- ...................................................................................................................................................... 39
Excel VBA Programming INTEGER Functions .............................................................................................. 40
SQR:- ....................................................................................................................................................... 40
EXP:- ........................................................................................................................................................ 40
LOG:- ....................................................................................................................................................... 40
RND:- ....................................................................................................................................................... 41
ABS:- ........................................................................................................................................................ 41
SGN:- ....................................................................................................................................................... 41
INT & FIX:- ............................................................................................................................................... 42
HEX:- ........................................................................................................................................................ 42
Excel VBA Programming TYPE CHECK Functions ......................................................................................... 43
isArray:- ................................................................................................................................................... 43
isDate:- .................................................................................................................................................... 43
isEmpty:- ................................................................................................................................................. 43
isNull:- ..................................................................................................................................................... 44
isNumeric:- .............................................................................................................................................. 44
Excel VBA Conditional Statements .............................................................................................................. 45
If...Then...Else Statements:- .................................................................................................................... 45
Select Case Statements:- ........................................................................................................................ 46
Excel VBA Loops .......................................................................................................................................... 47
While...Wend Statement:- ...................................................................................................................... 47
Do...Loop Statements:- ........................................................................................................................... 47
For...Next Statement:- ............................................................................................................................ 48
For Each...Next Statement:- .................................................................................................................... 49
With Statement:- .................................................................................................................................... 49
Error Handling ............................................................................................................................................. 50
On Error GoTo:- ....................................................................................................................................... 50
Excel VBA Short-Cuts ................................................................................................................................... 51
Back to Content
Code Snippet
A macro is a piece of computer code, that is written from excel, using visual basic application
programming language. Use VBA and macros to automate virtually any routine task, and save yourself
hours, day, maybe even weeks. Visual basic application (VBA) gives experienced Excel users a wide range
of options for creating Excel spreadsheets and customizing how they look and execute functions.
This document reveals code snippet & scripting technique, which helps you to create automated reports
that are amazingly powerful and useful. It also helps you instantly visualize information so you can
understand and act on it. It also shows you how to capture data from anywhere and use it anywhere.
How to write a macro
1. Choose Developer | On Developer Tab Click Insert
2. In the ActiveX control group, click Command button
3. Drag a command button on your worksheet
4. Right Click on command button (make sure design mode is selected).
5. Click View Code
6. Visual basic editor appears
7. Place your cursor between private sub commandbutton1_Click() and End Sub
8. Add the code here
9. Close the Visual basic editor
10. Click the command button on the sheet (make sure design mode is deselected).
How to Record a macro
1. Choose Developer | Record Macro
2. Enter Macro Name. No space allowed
3. Optional: Specify a keyboard shortcut, macro location and description
4. Click OK
5. Perform the actions
6. Choose Developer | Stop Recording
NOTE:
In Code snippet I have used Object for Workbook i.e. oExcel1_Workbook1 & Worksheet i.e.
oExcel1_Worksheet1, instead we can use workbook name like Workbooks (“file1.xls”).Activate &
worksheet name like Worksheets (“sheet1”).Delete.
Below are few examples:
Using Object Using Name
oExcel1_Workbook1.Save Workbooks (“file1.xls”).Save
oExcel1_Worksheet1.Cells(1, 1).Value Worksheets (“sheet1”).Cells(1,1).value
oExcel1_Worksheet1.Unprotect "XX" Worksheets (“sheet1”). Unprotect "XX"
oExcel1_Worksheet1. Delete Worksheets (“sheet1”). Delete
Back to Content
Code to Open Existing Workbook

Code to Create Excel Object:-

SYNTAX =>
Set oExcel1 = CreateObject("Excel.Application")

Code to Open Excel and Create Object for the same:-

EXAMPLE =>
Set oExcel1_Workbook1 = oExcel1.Workbooks.Open ("C:\NewFolder1\ NewFolder2\
NewFolder3\file1.xls")

Code to open txt file in the new workbook:-

Loads and parses a text file (.txt) as a new workbook with a single sheet that contains the parsed
text-file data
SYNTAX =>
expression.OpenText(Filename, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter,
Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, TextVisualLayout,
DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local)
EXAMPLE =>
oExcel1.Workbooks.OpenText Filename:="C:\ NewFolder1\ NewFolder2\ NewFolder3\
NewFolder4\file1.txt", DataType:=xlDelimited, Semicolon:=True
oExcel1.Workbooks.OpenText Filename:=" C:\ NewFolder1\ NewFolder2\ NewFolder3\
NewFolder4\file1.txt ", DataType:=xlDelimited, Comma:=True
oExcel1.Workbooks.OpenText Filename:=" C:\ NewFolder1\ NewFolder2\ NewFolder3\
NewFolder4\file1.txt ", DataType:=xlDelimited, Space:=True
Set oExcel1_Worksheet1 = oExcel1.ActiveWorkbook.Worksheets(1)
Code to Suppressing Excel Warnings:-

The default value is True. Set this property to False to suppress prompts and alert messages
while a macro is running; when a message requires a response, Microsoft Excel chooses the default
response
SYNTAX =>
expression. DisplayAlerts = True / False
EXAMPLE =>
Application.DisplayAlerts = False

Code to make excel visible:-

SYNTAX =>
oExcel1.Application.Visible = False

Code to check whether a Sheet in an open workbook exists:-

EXAMPLE =>
On Error Resume Next
Set wsheet = ActiveWorkbook.Sheets("Sheet4")
If wsheet Is Nothing Then
MsgBox "Fail"
Else
MsgBox "Pass"
End If

Code to create object to access specific worksheet:-

EXAMPLE =>
Set oExcel1_Worksheet1 = oExcel1.Workbooks ("C:\ NewFolder1\ NewFolder2\
NewFolder3\file1.xls").Worksheets ("Sheet1")
Code to make worksheet unprotect during runtime:-

Removes protection from a sheet or workbook. This method has no effect if the sheet or
workbook isn't protected
SYNTAX =>
expression.Unprotect (Password)
EXAMPLE =>
oExcel1_Worksheet1.Unprotect "XX"

Code to make worksheet protected during runtime:-

Protects a workbook so that it cannot be modified.
SYNTAX =>
expression. Protect (Password)
EXAMPLE =>
oExcel1_Worksheet1.Protect "XX"

Code to Make Specified range / column / cell locked during runtime:-

EXAMPLE =>
oExcel1_Worksheet1.Range("C:D").Locked = True / False

Code to get Row Count of Specific worksheet:-

EXAMPLE =>
WBRowCount = oExcel1_Worksheet1.Cells(oExcel1_Worksheet1.Rows.Count,
"A").End(xlUp).Row