Function and VBA in Excel

Excel Functions and VBA Cheat Sheet by Axcel Security:

https://drive.google.com/file/d/0B5wopjzDUlg-UGthSGNoWVZZOFE/view?usp=sharing

Excel Funtions

Combine two or more text strings into one string

1
 =CONCATENATE(B2," ",C2) 

Extract part of a text string (1)

1
=RIGHT(A2,LEN(A2)-FIND(",",A2)-1) 

Extract part of a text string (2)

1
=LEFT(C6,FIND(",",C6)-1) 

Calculate the length of a string

1
=LEN(C5) 

Calculate the length of a string after removing specific characters

1
=LEN(SUBSTITUTE(C6,D6,"")) 

Determine the frequency of a specific character within a string

1
=(LEN(C7)-LEN(SUBSTITUTE(C6,D6,"")))/LEN(D7) 

Eliminate leading, trailing, and excess spaces within a text string

1
=TRIM(C8) 

Replace a specific text string with another string

1
=SUBSTITUTE(C9, "how", "who") 

Replace a specific instance of a text string with another string

1
=SUBSTITUTE(C10, "2010", "2013", 2) 

Replace characters within a text string starting at a specified position

1
=REPLACE(C11,3, 5, "") 

Determine the number of words in a text string

1
=LEN(TRIM(C10))-LEN(SUBSTITUTE(C9, " ", ""))+1 

Convert all characters in a string to lowercase

1
=LOWER(C12) 

Convert all characters in a string to uppercase

1
=UPPER(C13) 

Capitalize the first letter of each word in a string

1
=PROPER(C14) 

Remove non-printable characters from a text string

1
=CLEAN(C15) 

Compare two text strings taking case sensitivity into account

1
=EXACT(C16, D16) 

Compare two text strings ignoring case sensitivity

1
=C17=D17 

Count occurrences of specific text within a range of cells

1
2
3
4
5
 =COUNTIF(C21:C25, "star")
=COUNTIF(C21:C25, "star?")
=COUNTIF(C21:C25, "star*")
=COUNTIF(C21:C25, "*star*")
=COUNTIF(C21:C25, "*") 

Count the number of empty or non-empty cells within a range

1
2
 =COUNTBLANK(C21:C26)
=COUNTA(C21:C27) 

Calculate the sum of the products of corresponding values in two ranges

1
=SUMPRODUCT(C28:C29,D28:D29) 

Nested If Statement

1
 =IF(C30>90,"GD!",IF(C30>=75,"Good!",IF(C30>=50,"Good!"))) 

3D-reference - Referencing the same cell or range across multiple worksheets

1
=Sheet1!B2+Sheet2!B2 

Offset - Referencing a cell relative to a specific cell

1
 =OFFSET([TargetCell], [Bottom], [Right], [From Cell], [To Cell]) 

Left Lookup - Search for a value in a column and return a corresponding value from another column

1
=INDEX(E3:E5, MATCH(A2, D3:D5)) 

Two-way Lookup - Search for a value based on both row and column headers

1
=INDEX(D3:F5, MATCH(A2, D3:D5,0), MATCH(B1, D2:F2, 0)) 

Find the maximum value within a range

1
=MAX(A:A) 

Replace negative numbers with zero

1
=MAX(0, (D36-C36)) 

Generate a random number between 0 and 1

1
=RAND() 

Generate a random whole number within specified lower and upper limits

1
=RANDBETWEEN(50,75) 

Generate random decimal numbers between two limits

1
=50+25*RAND() 

Determine the position of a number within a list

1
=RANK([Target Cell], [Range of Cells]) 

Round a number down to the nearest integer

1
=INT(C41) 

Remove decimal places from a number

1
=TRUNC(C42) 

1
=TRUNC(C42, 2) 

Round a number to the nearest specified multiple

1
=MROUND(C44,5) 

Excel VBA

Display the number of worksheets in a workbook

1
MsgBox Worksheets.Count 

Create a new workbook

1
Workbooks.Add 

Write a value to a range of cells

1
Range("A1:A2,B3:C4").Value =  [value] 

Write a value to a named range

1
Range("NamedRange").Value = [value] 

Write a value to a specific cell

1
2
Cells(3, 2).Value = [value] 
```**Declare a Range object and assign a range of cells**  

Dim example As Range Set example = Range(“A1:C4”) example.Value = 8

1
**Select a range of cells**  

Dim example As Range Set example = Range(“A1:C4”) example.Select

1
**Select a specific row within a range**  

Dim example As Range Set example = Range(“A1:C4”) example.Rows(3).Select

1
**Select a specific column within a range**  

Dim example As Range Set example = Range(“A1:C4”) example.Columns(2).Select

Range(“C3:C4”).Value = Range(“A1:A2”).Value

1
**Clear the contents of a cell**  

Range(“A1”).Value = ""

1
**Count the number of cells or rows within a range**  

Dim example As Range Set example = Range(“A1:C4”) MsgBox example.Count ’ return 12 MsgBox example.Rows.Count ’ return 4

Dim x As Integer x = 6 Range(“A1”).Value = x

1
**Declare a String variable**  

Dim book As String book = “bible” Range(“A1”).Value = book

1
**Declare a Double variable**  

Dim x As Integer x = 5.5 MsgBox “value is " & x

Dim continue As Boolean continue = True If continue = True Then MsgBox “TRUE!”

1
**If-Else Statement for conditional execution**  

Dim score As Integer, result As String score = Range(“A1”).Value If score >= 60 Then result = “pass” Else result = “fail” End If Range(“B1”).Value = result

1
2
  
**For loop for iterating a specific number of times**  

Dim i As Integer For i = 1 To 6 Cells(i, 1).Value = 100 Next i

1
**Do While loop for iterating based on a condition**  

Dim i As Integer i = 1 Do While i < 6 Cells(i, 1).Value = 20 i = i + 1 Loop

1
**String manipulation functions**  

Dim text As String text = “example text” MsgBox Left(text, 4) ‘return exam MsgBox Right(“example text”, 2) ‘return xt MsgBox Mid(“example text”, 9, 2) ‘return te MsgBox Len(“example text”) ‘return 12 MsgBox Instr(“example text”, “am”) ‘return 3

Dim Films(1 To 5) As String Films(1) = “Lord of the Rings” Films(2) = “Speed” Films(3) = “Star Wars” Films(4) = “The Godfather” Films(5) = “Pulp Fiction” MsgBox Films(4) ‘return The Godfather

1
**Define a function in VBA**  

Function Area(x As Double, y As Double) As Double Area = x * y End Function

Dim z As Double z = Area(3, 5) + 2 MsgBox z

Sub Area(x As Double, y As Double) MsgBox x * y End Sub

Area 3, 5

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

Task 1
------

**Before**

[![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwnO6vVrOw9QwpV-5C-1PrRmYS_uVig_6z60BSfbA6UIVP00e4S_Skcmb1NFTaY8F7oBVF6C5WaIWWmHq0q9OZn3euP_pp8TqllXxFFqRjJVrzD4rYDavVw-71XaPZjOHBu2J9iP15l2bu/s1600/WhatsApp+Image+2020-07-29+at+19.57.27.jpeg)](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwnO6vVrOw9QwpV-5C-1PrRmYS_uVig_6z60BSfbA6UIVP00e4S_Skcmb1NFTaY8F7oBVF6C5WaIWWmHq0q9OZn3euP_pp8TqllXxFFqRjJVrzD4rYDavVw-71XaPZjOHBu2J9iP15l2bu/s1600/WhatsApp+Image+2020-07-29+at+19.57.27.jpeg)

**After**

[![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyFidPYJ4XhN9xLBu6X8JWdl002nfmeZNC84pguqkB6ulsRlgaFwoSsbtlhOOWl5Oe2YPOZ3sfdkRv5gJS3kfI15J7__XRYO-rBgplM_AACH2CJbPemiZ9hqtAWgmNEQv5lqxRdeTVV1ee/s320/WhatsApp+Image+2020-07-29+at+19.57.43.jpeg)](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyFidPYJ4XhN9xLBu6X8JWdl002nfmeZNC84pguqkB6ulsRlgaFwoSsbtlhOOWl5Oe2YPOZ3sfdkRv5gJS3kfI15J7__XRYO-rBgplM_AACH2CJbPemiZ9hqtAWgmNEQv5lqxRdeTVV1ee/s1600/WhatsApp+Image+2020-07-29+at+19.57.43.jpeg)

Sub compute()

Dim i As Integer Dim TEMP_ID As String Dim COL_ID_NEW As Integer Dim COL_VL_NEW As Integer Dim COL_VL_NEW_DEFAULT As Integer Dim ROW_ID_NEW As Integer

TEMP_ID = “dummy” ‘Determine the last row containing data LAST_ROW = Cells(Rows.Count, 1).End(xlUp).Row

‘Configuration ‘Specify the column number for the new ID COL_ID_NEW = 4 ‘Specify the initial row number for the new ID ROW_ID_NEW = 1 ‘Specify the column number for the new value COL_VL_NEW = 5 ‘Specify the default column number for the new value COL_VL_NEW_DEFAULT = 5 ‘Specify the last column to be removed LAST_COL_DEL = 3

‘Set column titles Range(“D1”).Value = “ID” Range(“E1”).Value = “Value 1” Range(“F1”).Value = “Value 2” Range(“G1”).Value = “Value 3”

‘Process each row until the last row with data For i = 2 To LAST_ROW ‘Get the current ID and Value CURR_ID = Cells(i, 1).Value CURR_VALUE = Cells(i, 2).Value

‘If the current ID matches the previous ID If CURR_ID = TEMP_ID Then ‘Move to the next column for the new value COL_VL_NEW = COL_VL_NEW + 1 ‘Copy the value to the corresponding cell Cells(ROW_ID_NEW, COL_VL_NEW).Value = CURR_VALUE

‘If a new ID is encountered Else ‘Update the previous ID with the current ID TEMP_ID = CURR_ID ‘Reset the column number for the new value COL_VL_NEW = COL_VL_NEW_DEFAULT ‘Move to the next row for the new ID ROW_ID_NEW = ROW_ID_NEW + 1 ‘Populate the cells with the current ID and Value Cells(ROW_ID_NEW, COL_ID_NEW).Value = CURR_ID Cells(ROW_ID_NEW, COL_VL_NEW).Value = CURR_VALUE End If Next i

‘Remove unnecessary columns For i = 1 To LAST_COL_DEL Columns(1).EntireColumn.Delete Next i

End Sub