VBA- User Defined Functions

CDSID: lvishwan

1

7/12/2019

LEVEL -3

Duration:

Presentation: 2Hr

With demo: 4Hrs

With Practice: 16 Hrs

CDSID: lvishwan

2

7/12/2019

What is VBA?

VBA is a specialized version of Microsoft’s well-known Visual Basic language.

VBA is an Object-Oriented language within Microsoft Office suite.

​

Where to use?

​

• Excel
• Word
• Access
• Power Point.

Why to use?

​

By using VBA we can Automate the repeated process and simplify multiple step process into simple click

​

​

VBA

CDSID: lvishwan

3

7/12/2019

General Programming Concept

​

• Arithmetic commands
• Array and dictionary
• Logical commands
• Loop
• Functions and modules

CDSID: lvishwan

4

7/12/2019

1. Enable Developer Tab
2. Record Macros
3. VBA-Editor
4. Program Structure
5. Basic commands
6. Excel specific commands
7. Example programs
8. Code Optimization
9. Events

​

​

Level-4

Contents

CDSID: lvishwan

5

7/12/2019

Enable Developer TAB

​

by default Developer tab will be Hidden. we need to enable it to easy access

​

CDSID: lvishwan

6

7/12/2019

Record Macros

Record Macro is useful for beginner to get the command for the specific operation

It will helpful to write our own commands and to Automate simple repetitive task

CDSID: lvishwan

7

7/12/2019

VBA Editor

Excel Have own VBA editor in it. Such as Global editors, it have all features to form a code

CDSID: lvishwan

8

7/12/2019

Program Structure

All the programs should starts with <privacy><program type> <name>()

Ends with “End” <program type>

​

Privacy (optional, default Public):

• Public
• Private

​

Program types:

• Sub : to perform some activity with out written result value
• Function : to get written value ( we can use like formula in cells)

​

Example:

Private Sub prog1():

<some program>

End sub

​

CDSID: lvishwan

9

7/12/2019

Basic commands

​

• x=Inputbox(“enter some value”)
• Msgbox “some value”
• Debug.print “some value”
• Range(“A1”).value= “some value”
• Cells(1,2).value=“some value”
• ‘ – (single quote for idle row or comment)

​

CDSID: lvishwan

10

7/12/2019

In addition to General Functions VBA have some more basic functions need to know

​

1.Arithmetic ( Eg:add, sub, average,% etc)

​

2. Comparison (Eg: equal, not equal, greater)

​

3.String (Eg: uppercase, split,length)

​

4. Logical (Eg: AND,OR)

​

5.Conditional (Eg: If, If-else)

​

6.Date (Eg: week number, month)

​

7.Loop

​

8.Array

​

9.Dictinary

CDSID: lvishwan

11

7/12/2019

In addition to General Functions VBA have some more basic functions need to know

​

1.Arithmetic ( Eg:add, sub, average,% etc)

​

2. Comparison (Eg: equal, not equal, greater)

​

3.String (Eg: uppercase, split,length)

​

4. Logical (Eg: AND,OR)

​

5.Conditional (Eg: If, If-else)

​

6.Date (Eg: week number, month)

​

7.Loop

​

8.Array

​

9.Dictinary

= Equal

<> Not equal

> Greater

< Lesser

>= Greater/equal

<=Lesser/Equal

CDSID: lvishwan

12

7/12/2019

In addition to General Functions VBA have some more basic functions need to know

​

1.Arithmetic ( Eg:add, sub, average,% etc)

​

2. Comparison (Eg: equal, not equal, greater)

​

3.String (Eg: uppercase, split,length)

​

4. Logical (Eg: AND,OR)

​

5.Conditional (Eg: If, If-else)

​

6.Date (Eg: week number, month)

​

7.Loop

​

8.Array

​

9.Dictinary

UCase( word )

Lcase (word)

Len(word)

Split(word, chr)

word1+word2

word1&word2

Left( word, n)

Right( word, n)

InStr(word,chr)

​

CDSID: lvishwan

13

7/12/2019

In addition to General Functions VBA have some more basic functions need to know

​

1.Arithmetic ( Eg:add, sub, average,% etc)

​

2. Comparison (Eg: equal, not equal, greater)

​

3.String (Eg: uppercase, split,length)

​

4. Logical (Eg: AND,OR)

​

5.Conditional (Eg: If, If-else)

​

6.Date (Eg: week number, month)

​

7.Loop

​

8.Array

​

9.Dictionary/collection

CDSID: lvishwan

14

7/12/2019

Array

An array is a group of variables.

​

In Excel VBA, you can refer to a specific variable of an array by using

The array name and the index number. ArrayName(0)

CDSID: lvishwan

15

7/12/2019

Dictionary

The VBA Collection is a simple native data structure available in VBA to store (collect as you wish) objects. VBA Collections are more flexible than VBA Arrays as they are not limited in their size at any point in time and don’t require manual re-sizing. Collections are also useful when you don’t want to leverage there more complex (but quite similar) Data Structures like the VBA ArrayList

Other VBA Data Structures

VBA Array VBA Collection VBA Dictionary VBA ArrayList VBA Queue VBA Stack

Dim myCol As Collection

'Create New Collection

Set myCol = New Collection

'Add items to Collection

myCol.Add 10 'Items: 10

myCol.Add 20 'Items: 10, 20

myCol.Add 30, Before:= 1 'Items: 30, 10, 20

myCol.Add 10, "Key10"

Debug.Print myCol("Key10") 'Returns 10

myCol.Remove (2) 'Items: 10, 30

Debug.Print myCol.Count '3

Debug.Print myCol(1) '20

Loops

In general, statements are executed sequentially: The first statement in a function is executed first, followed by the second, and so on. There may be a situation when you need to execute a block of code several number of times. Programming languages provide various control structures that allow for more complicated execution paths. A loop statement allows us to execute a statement or group of statements multiple times.

The following diagram illustrates a loop statement:

​

For each x in array(“first”,”second”,”third”)

Debug.print(x)

Next

​

For i=2 to 10 step 2

Debug.print(i)

next

On error resume next

On error goto

Goto <label>

Goto 0

Exit sub

Exit For

Exit loop

Exceptional handling

​

If VBA can’t execute a statement (command) then a run-time error occurs. By default Excel deals with these, so when a run-time error occurs, you'll see a default error message

​

​

But you can change this and instruct Excel to allow your code to deal with run-time errors.

CDSID: lvishwan

18

7/12/2019

Excel Specific Commands

​

Find Last row

Find Last column

Find specific value

​

Do while loop for find command

FirstAddress = rng.Address

Do

Set rng = wsm.Columns("C:C").FindNext(rng)

Loop While Not rng Is Nothing And rng.Address <>FirstAddress

CDSID: lvishwan

19

7/12/2019

Find Last Used Row:

CDSID: lvishwan

20

7/12/2019

Find Last Used Column:

CDSID: lvishwan

21

7/12/2019

CDSID: lvishwan

22

7/12/2019

Browse File:

To browse a file:

Set filee = Application.FileDialog(msoFileDialogFilePicker)

If filee.Show <> -1 Then Exit Sub

filepath = filee.SelectedItems(1)

​

To browse a folder:

Set fldr = Application.FileDialog(msoFileDialogFolderPicker)

If fldr.Show <> -1 Then Exit Sub

folderpath = fldr.SelectedItems(1)

CDSID: lvishwan

23

7/12/2019

CDSID: lvishwan

24

7/12/2019

PERSONAL WORKBOOK

​

A Personal Macro Workbook is a hidden workbook in your system that opens whenever you open the Excel application.

​

It’s a place where you can store macro codes and then access these macros from any workbook. It’s a great place to store those macros that you want to use often.

​

CDSID: lvishwan

25

7/12/2019

Code optimization

CDSID: lvishwan

26

7/12/2019

• Worksheet event are triggered when there is a change in worksheet.
• User can select each one of worksheets and choose “Worksheet” from the drop down to get supported events.

​

​

​

​

​

​

Popular worksheet events:

​

​

​

​

Worksheet Events: