Classification of Functions

We can Classify functions in 3 ways

Built-In Functions - - - Provided by VBA
Worksheet Functions - - - Provided by Excel
Custom Functions - - - Created by User

Where we can find worksheet functions in vba?

Write the code APPLICATION.WORKSHEETFUNCTION.
Instead of
WORKSHEETFUNCTION.

Rational behind this is Application.worksheetfunction retrieves most of the available functions

COUNTIF Function - Single Condition

We can count the data based on a single condition
We can count in two ways:
(i) with defining Object variable(ii)Without defining Object variable

1)Find the number of cells which consists of MILK as description

Program:

Count the number of cells which consists of values Greater than 16, greater than 18

Countif Function - Multiple Conditions

Example#2: Find the total count of Pens and Erasers

COUNTIF - Consolidated Summary

Sub Countfunctions()
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Sheets("count").Delete
Worksheets.Add after:=Worksheets(Sheets.Count)
ActiveSheet.Name = "Count"
Dim sh As Worksheet
Set sh = Sheets("count")
Item = Sheets("Sheet2").Range("J" & Rows.Count).End(xlUp).Row
For i = 2 To 11
sh.Range("A" & i).Value = Sheets(i).Name
Next
For c = 1 To Item
sh.Cells(1, c + 1).Value = Sheets("Sheet2").Range("j" & c + 1).Value
Item = Item + 1
Next
cnt = 2
Dim ro As Integer
ro = 2
Do Until Range("A" & cnt) = ""
Sheets(cnt).Activate
Dim max As Range
Set max = Sheets(cnt).Range("B2:B1050")
For r = 2 To 10
Condition = sh.Cells(1, r).Value
sh.Cells(ro, r).Value = Application.WorksheetFunction.CountIf(max, Condition)
Next
cnt = cnt + 1
ro = ro + 1
Loop
Application.DisplayAlerts = True
End Sub

VLookup Function

Through this function we can retrieve lookup value

VLookup Function

Private Sub CommandButton1_Click()
q = ComboBox2.Value
Data = Sheets("Sheet2").Range("B1:G10")
Range("J4").Value = Application.WorksheetFunction.VLookup(q, Data, 2, 0)
Range("J5").Value = Application.WorksheetFunction.VLookup(q, Data, 3, 0)
Range("J6").Value = Application.WorksheetFunction.VLookup(q, Data, 4, 0)
Range("J7").Value = Application.WorksheetFunction.VLookup(q, Data, 5, 0)
Range("J8").Value = Application.WorksheetFunction.VLookup(q, Data, 6, 0)
End Sub

Mod Function

Segregation of Odd and Even Numbers

Copy the Code:

Private Sub CommandButton1_Click()
Dim max As Long
max = Range(Range("A1"), Range("A1").End(xlDown)).Rows.Count
Dim i As Integer
For i = 1 To max
If Cells(i, 1) Mod 2 = 0 Then
Cells(i, 2) = "Even Number"
Else
Cells(i, 2) = "Odd Number"
End If
Next
End Sub

Print Unique name\item in alternate rows

Private Sub CommandButton1_Click()
Dim max As Integer
max = 50
Dim i As Integer
For i = 1 To max
If Cells(i, 1).Row Mod 2 = 0 Then
'Excel Formula:=IF(MOD(ROW(),2) = 0,"Apple","Banana")
Cells(i, 1) = "Apple"
Else
Cells(i, 1) = "Banana"
End If
Cells(i, 2) = Cells(i, 1).Row
Next
End Sub

MOD Function - Color alternate rows

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Range("B4:F15")
rng.Select
Dim i As Integer
For i = 1 To Selection.Rows.Count
If i Mod 2 = 0 Then
Selection.Rows(i).Interior.ColorIndex = 7
End If
Next
End Sub

SUM Function

This is Excel sheet function
We can execute this function through two methods
(i) With defining Object
(ii)Withoug Defining Object

1) Without defining Object

2) With defining Object

SUM Function - Adding Formula

Private Sub CommandButton1_Click()
Range("A1:B3").Columns(3).Formula = "=A1+B1"
End Sub

Worksheet function - SUM

It enables the user to add entire range

Private Sub CommandButton1_Click()
Dim i As Integer
i = Range("A2").End(xlDown).Row
Range("F4").Value = "Sum Total"
Range("G4").Value = Application.WorksheetFunction.Sum(Range("A1:A" & i))
End Sub

SumIF

Private Sub CommandButton1_Click()
Range("F4").Value = "SumIf Total"
Range("G4").Value = Application.WorksheetFunction.SumIf(Range("B1:B150"), Range("g3"), Range("C1:C150"))
End Sub

SumIFs

Private Sub CommandButton1_Click()
Range("F4").Value = "SumIf Total"
Range("G4").Value = _
Application.WorksheetFunction.SumIfs(Range("C2:C150"), Range("B2:B150"), Range("G3"), Range("D2:D150"), Range("H3"))
End Sub

Sum Function

Private Sub CommandButton1_Click()
Dim total As Integer
For i = 2 To 150
total = total + Range("E" & i).Value
Range("J3").Value = total
Application.StatusBar = i
Next
Application.StatusBar = ""
End Sub

Sum Five Rows

Sub Sum_Every_5_Rows()
Dim i As Integer
For i = 2 To 25 Step 5
cumsum = Sum + Sum
Range(Cells(i, 5), Cells(i + 4, 5)).Select
Sum = 0
For q = i To i + 4
Sum = Sum + Cells(q, 5)
'MsgBox Sum
Cells(q, 6).Value = Sum
Next
Next
End Sub

Sum Top N numbers

Private Sub CommandButton1_Click()
Dim i As Integer
Max = Range("E2").End(xlDown).Row
Data = Range("E2:E" & Max)
For i = 1 To 6
s = Application.WorksheetFunction.Large(Data, i)
MsgBox s
Sum = Sum + s
MsgBox Sum
Next
End Sub

Average Function

Assign AVERAGE FUNCTION to particular cell

Private Sub CommandButton1_Click()
Range("B1").Formula = "=Average(A1:A20)"
End Sub

Average Function

Sub Find_Average()
Dim Datarange As Range
Set Datarange = Range("E2:E5")
Cells(2, 6) = Application.WorksheetFunction.Average(Datarange)
MsgBox Application.WorksheetFunction.Average(Datarange)
End Sub