Excel Function and VBA

Excel Functions and VBA Cheat Sheet by Axcel Security:

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

---

Excel Funtions

Join two or more text strings into one string

=CONCATENATE(B2," ",C2)

Separate Strings (1)

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

Separate Strings (2)

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

Length of the string

=LEN(C5)

Replaces existing text with new text in a string

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

Number of Instances

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

Remove extra spaces, starting spaces and ending spaces 

=TRIM(C8)

Replaces existing text with new text in a text string

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

Replaces existing text with new text in a text string (indicate which occurrence you want to substitute)

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

Replaces existing text with new text in a text string (position of the text to be replaced)

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

Get the number of words

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

Convert all letters in a text string to lowercase

=LOWER(C12)

Convert all letters in a text string to uppercase

=UPPER(C13)

Convert all letters in a text string by capitalize each word

=PROPER(C14)

Removes nonprintable characters from a string

=CLEAN(C15)

Compare Text (case-sensitive)

=EXACT(C16, D16)

Compare Text (case-insensitive)

=C17=D17

Count Text Occurrences

=COUNTIF(C21:C25, "star")
=COUNTIF(C21:C25, "star?")
=COUNTIF(C21:C25, "star*")
=COUNTIF(C21:C25, "*star*")
=COUNTIF(C21:C25, "*")

Count Blank/Nonblank Cells

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

Calculate the sum of the products

=SUMPRODUCT(C28:C29,D28:D29)

Nested If

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

3D-reference - Same cell or range on multiple worksheets

=Sheet1!B2+Sheet2!B2

Offset

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

Left Lookup

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

Two-way Lookup

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

Find the maximum value

=MAX(A:A)

Negative Numbers to Zero

=MAX(0, (D36-C36))

Creating random numbers

=RAND()

Returns a random whole number between two boundaries

=RANDBETWEEN(50,75)

Create random decimal numbers between boundaries

=50+25*RAND()

Rank of a number in a list of numbers

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

Rounds a number down to the nearest integer

=INT(C41)

Chops off decimals

=TRUNC(C42)

=TRUNC(C42, 2)

Nearest Multiple

=MROUND(C44,5)

---

Excel VBA

To alert number of worksheets

MsgBox Worksheets.Count

To creates a new workbook

Workbooks.Add

Range Examples

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

Range Examples (with named range)

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

Cells

Cells(3, 2).Value = [value]

Declare a Range Object

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

Select

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

Rows

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

Columns

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

Copy/Paste

Range("C3:C4").Value = Range("A1:A2").Value

Clear

Range("A1").Value = ""

Count

Dim example As Range
Set example = Range("A1:C4")
MsgBox example.Count
' return 12
MsgBox example.Rows.Count
' return 4

Variables - Integer

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

Variables - String

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

Variables - Double

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

Variables - Boolean

Dim continue As Boolean
continue = True
If continue = True Then MsgBox "TRUE!"

If-Else Statement

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

Single Loop

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

Do While Loop

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

String Manipulation

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

One-dimensional Array

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

Function

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

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

Task 1

Before

After

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"
'To get number of last row
LAST_ROW = Cells(Rows.Count, 1).End(xlUp).Row

'Configuration
'To define column number to store new ID
COL_ID_NEW = 4
'To define row number to store new ID
ROW_ID_NEW = 1
'To define column number to store new value
COL_VL_NEW = 5
'To define default column number to store new value
COL_VL_NEW_DEFAULT = 5
'To define last column to delete
LAST_COL_DEL = 3

'To set title
Range("D1").Value = "ID"
Range("E1").Value = "Value 1"
Range("F1").Value = "Value 2"
Range("G1").Value = "Value 3"

'To read line by line until last cell that has value
For i = 2 To LAST_ROW
 'To get current ID and Value
 CURR_ID = Cells(i, 1).Value
 CURR_VALUE = Cells(i, 2).Value
 
 'If current ID is same with previous ID
 If CURR_ID = TEMP_ID Then
    'To increase column number to store new value
    COL_VL_NEW = COL_VL_NEW + 1
    'To copy the value to cell
    Cells(ROW_ID_NEW, COL_VL_NEW).Value = CURR_VALUE
 
 'If new ID found
 Else
    'To replace previous ID with current ID
    TEMP_ID = CURR_ID
    'To reset column number to store new value
    COL_VL_NEW = COL_VL_NEW_DEFAULT
    'To increase row number to store new ID
    ROW_ID_NEW = ROW_ID_NEW + 1
    'To set 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

'To delete unnecessary columns
For i = 1 To LAST_COL_DEL
    Columns(1).EntireColumn.Delete
Next i

End Sub