1
infotica
vbafaculty@gmail.com
Built-in Functions Contd.
IsDate
?ISDATE(#23-JUN-2014#)
True
?ISDATE(#29-FEB-2012#)
True
?ISDATE(#30-FEB-2012#)
Now
Time
Time$
Hour(time)
Minute(time)
Second(time)
StrReverse
?ISDATE(#23-JUN-2014#)
True
?ISDATE(#29-FEB-2012#)
True
infotica
vbafaculty@gmail.com
?ISDATE(#30-FEB-2012#)
?NOW
7/26/2013 11:11:06 AM
?TIME
11:11:22 AM
?TIME$ '24 Hr format
11:11:30
?hour(time)
11
?minute(time)
12
?second(time)
19
?StrReverse("abcc dfd ^^dfdf 123")
321 fdfd^^ dfd ccba
'
?StrReverse("acbdfdf1234")
infotica
vbafaculty@gmail.com
?val("4321fdfdbca")
4321
?StrReverse(val(StrReverse("acbdfdf1234")))
1234
Round
'ROUND(NUMBER,[NUMBER OF DIGITS AFTER DECIMAL])
?ROUND(234.567)
235
?ROUND(234.567,2)
234.57
Use Relative References:
Macros are recorded with reference to the initial selected cell.
This option can be switched on/off while recording.
Working with Personal Macro Workbook
Where is the path?
*Varies based on versions
The best way to find the path of Personal Macro Workbook :
Ctrl+G
infotica
vbafaculty@gmail.com
?Application.startuppath
Note : PMW is stored under XLSTART folder
PMW is used to store the commonly used macros
PMW is a hidden workbook
PMW gets loaded into excel on each session
PMW can be viewed in Project Explorer
PMW contains one sheet by default, and sheets can be added
To view PMW from Excel :
View -> Unhide -> Personal -> OK
Note : PMW, just like any workbook, can store an unlimited no. of
macros
Note : PMW can be deleted
Note : Workbooks placed in XLSTART folder are loaded into excel on
each session
Record a macro and save in the PMW
Click RECORD MACRO button on the statusbar
Select PERSONAL MACRO WORKBOOK from Store-Macro-In
dropdown
Click OK
Do some actions in Excel
Click STOP RECORD MACRO button on the statusbar
Select another sheet/workbook
Press Alt+F8
infotica
vbafaculty@gmail.com
Select the macro just created
Note : You can observe Personal Macro Workbook!Macro1
Click RUN
*Changing/Removing SHORTCUT/DESCRIPTION
Alt+F8
Click OPTIONS
Change/Remove
Click OK
Option Explicit
A module level statement that makes variable declaration
mandatory in all programs in the current module
Write two programs with wrong/undeclared variable names and run
Option Explicit
Sub pro3()
Dim a As Integer, b As Integer
a = 10: b = 20
'Variables can be declared anywhere in the program
Dim c As Integer
c=a+b
MsgBox c
End Sub
Sub pro4()
Dim x As Integer
infotica
vbafaculty@gmail.com
y = 10 'error
End Sub
'set option explicit as default :
'Tools -options - Editor - Require variable declaration
Types of Errors:
1. Compile Errors
Compiler : An internal feature
that converts source-code(your program)
into object-code(binary language)
Ex : Syntatctical errors
2. Runtime Errors
Syntactically correct:
Resource missing
object name etc.
3. Logical Errors
Programming approach/logic
Note : Debug menu options,
watchwindow,
infotica
vbafaculty@gmail.com
localswindow &
debug.print are used to debug the
programs
Excel Object Model :
Class : A logical set of rules and regulations
Object : An instance of a class
Lucy(a dog)
Summary(a sheet)
Descriptions
Sex : Female
Age : 6 months
Height,weight,
Rows
Columns
Name
Tab-color
Actions
Bark,jump,wag,hunt,smell
Move,copy,delete
Classes : workbook, worksheet, range etc
infotica
vbafaculty@gmail.com
These are the internal items that define the structural
parts of excel.
These classes are used to create objects.
Objects:
An object is an instance of a class.
Eg: sheet1, sheet2, sheet3 etc. are the objects of
worksheet class.
Methods : Actions against an object
Eg: cut,copy,move (sheet1) etc.
These actions can be used after a dot.
Properties : Set/Get Items(values/names) against an object
Eg: Name, Rows, Columns, Tab.Color (sheet1) etc.
These properties can be used after a dot.
Constants : Keywords that can be used in code
directly. Generally, each constant has a numeric
value internally (ctrl+I : Quick Info)
Eg: vbyesno(4), msofiledialogfolderpicker(4)
Enumerations: Groups of related constants
Eg: VbMsgBoxResult
infotica
vbafaculty@gmail.com
(contains : vbyes, vbno, vbcancel etc.)
Keywords used related to objects and important properties in
VBA:
Application : Current excel session
Workbooks: The collection of all open workbooks in a session
Workbooks(index) : Particular workbook identified by number
Workbooks(name): Particular workbook identified by name
Activeworkbook: The current workbook in a session
Sheets/Worksheets: The collection of all sheets in a workbook
sheets(index) : Particular sheet identified by number
sheets(name): Particular sheet identified by name
Activesheet: The current sheet (in the activeworkbook) in a session
Range : One/more cells that may be adjacent/non-adjacent in
one/more sheets (3D)
ActiveCell: The current cell in the activesheet
ActiveWindow : The window currently active in the current session.
ListObjects(Tables)
Pivottables
FormatConditions
ChartObjects
PageSetup
Comments etc.
Cells : all cells in a sheet
10
infotica
vbafaculty@gmail.com
Cells(rowIndex,columnIndex) : particular cell
Working with Range object:
Range syntax(1)
'Note : Address is enclosed in double quotes
'Eg : range("A10").method/property
Workbooks(Bookname).sheets(sheetName).range(address)
range(address) '*range in the activeworkbook(in the activesheet)
?ActiveWorkbook.Name
Book6
?Activesheet.name
Sheet1
?activecell.Address
$A$1
'syntax : range().Address([rowAbsolute],[ColumnAbsolute])
?activecell.Address(false,false)
A1
?activecell.Address(,false)
A$1
11
infotica
vbafaculty@gmail.com
?activecell.Address(false)
$A1
activewindow.WindowState=xlMaximized 'ctrl+shift+j (list
constants)
'xl...
: excel constants
'vb... : vba constants
'mso... : ms-office constants
'fm... : userforms constants
?activeprinter
PDF-XChange 3.0 on Ne01:
Select
'select
range("a10").Select
range("a1:B5").Select
range("a1:B5,f5:f10").Select
range("a:A").Select
range("a:c").Select
range("a:c,f:f").Select
range("a:c,f:f,g5").Select
12
infotica
vbafaculty@gmail.com
range("1:1").Select
range("1:3").Select
range("1:3,7:7").Select
range("a:c,f:f,2:10,g5").Select
Activate
range("a1:B5").Select
'activate method changes activecell in the current selection
'OR it can be used like SELECT
range("b3").Activate
range("e2").Activate
range("e2:e5").Activate
Value
'value
'to set /get the value of a range
'It is optional
range("A1").Value="Sample Text"
range("b1:b5")="excel vba"
range("b1:b5")=100.567
range("b1:b5")=#12-may-2014#
13
infotica
vbafaculty@gmail.com
range("A1")=""
range("B:B").Columns.AutoFit
Autofit
Font
'colors in excel 2007
'1 to 56 default colors
'custom colors
'16 Millions
range("A1:b5")="Sample Text"
range("A1:b2").Font.Bold=true
range("A1:b2").Font.Italic=true
range("A1:b2").Font.Underline=true
Sub fontEx()
'*with is used to avoid repetitions in object reference
'Code runs faster
'*WITH ... must be ended with END WITH
With Range("A1:b2").Font
.Bold = True
.Italic = True
.Underline = True
.Name = "Impact"
End With
14
infotica
vbafaculty@gmail.com
End Sub
Color/ColorIndex
'DEFAULT COLORS : COLORINDEX
'CUSTOM COLORS : COLOR
RANGE("a1:b2").Font.ColorIndex=4
?RANGE("a1:b2").Font.ColorIndex
4
RANGE("a1:b1").Font.Color=14567899
?RANGE("a1").Font.ColorIndex
39
'rgb() 'min 0 max 255
range("F1").Interior.Color=rgb(199,112,18)
?range("F1").Interior.Color
1208519
Cut/Paste
Sub cutPasteEx1()
'within the same sheet
Range("A1:b2").Cut Range("h5")
End Sub
Sub cutPasteEx2()
'b'ween 2 sheets
15
infotica
vbafaculty@gmail.com
Sheets("sheet1").Range("h5:i6").Cut _
Sheets("sheet3").Range("a1")
End Sub
Sub cutPasteEx3()
'b'ween 2 sheets in 2 wbks
Workbooks("book6").Sheets("sheet3").Range("a1:b2").Cut _
Workbooks("book7").Sheets("sheet1").Range("a1")
End Sub
Copy/Paste
Option Explicit
Sub copyPasteEx1()
'one time
'range.Copy [destination]
Range("A1:a4").Copy Range("f1")
End Sub
Sub copyPasteEx2()
'multiple times
'range.Copy [destination]
Range("A1:a4").Copy
Range("f1").PasteSpecial
Range("g1").PasteSpecial
16
infotica
vbafaculty@gmail.com
Application.CutCopyMode = False
'to clear the data from os clipboard
'F8 : step into mode / break-mode
End Sub
PasteSpcial
17
infotica
vbafaculty@gmail.com
Branching/Decision Making
IIF
IF complete syntax
IF single line
IF multiline
IF multiline with Else
If multiline with ElseIF Else
Nested IF
