PL00, MS-Excel

Back to the previous page | page management
List of posts to read before reading this article

• post1
• post2
• post3

---

Contents

• Data accumulation
  • Cell and Input
    • Screen layout
    • Input, Update, Delete
  • Table and Edit
  • Sheet
  • Useful shortcut
• Data production
• Data analysis
• VBA basic
  • basic Tutorials
    • MsgBox
    • Sub Procedure
    • Function Procedure
    • Calling Sub Procedure
    • Calling Function Procedure
    • Passing Argument by Reference or by Value
    • Workbook and Worksheet Object
    • Range Object and Cells Property
  • Creating and Managing Array
    • Declaring an Array With Dim Statement
    • Resize an Array With Redim Statement
    • Manage Dynamic Array
    • Create Multi-Dimensional Array
    • Find The Size of an Array
  • Decision Structures - IF and Select Case
    • IF … Then Statement
    • IF … Then … Else
    • IF … Then … ElseIf
    • Select Case
  • Loop Structures
    • For … Next
    • For … Next Loop With Step
    • Do While … Loop
    • Do Until … Loop
    • Do … Loop While
    • Do … Loop Until
  • Sorting Numbers In an Array
  • Statistics
    • Random Number and Randomize Statement
    • Standard Deviation and Mean
    • Skewness and Kurtosis
    • Percentile and Confidence Interval
    • Profitablity
    • Creating a Histogram
    • Finding Median
    • Generate Random Numbers From Uniform Distribution
    • Sum Numbers
    • Compute Factorial
    • Binomial Coefficient
    • Cumulative Standard Normal Distribution
• VBA project
• Auto-hotkey basic
• Auto-hotkey project

---

Data accumulation

Cell and Input

Screen layout

Input, Update, Delete

Action	shortcut
Input	any keys for typing
Update	F2
Delete	Del

---

Table and Edit

with “Quotation”

---

Sheet

---

Useful shortcut

---

Data production

---

Data analysis

---

VBA basic

basic Tutorials

MsgBox

Sub procedure()
    MsgBox "Hello World!"
End Sub

Sub Procedure

Sub procedure()
    Range("C1") = Now()
End Sub

Private

Private Sub procedure()
    Range("C1") = Now()
End Sub

Public

Public Sub procedure()
    Range("C1") = Now()
End Sub

Function Procedure

Function procedure(x, y)
     procedure = x + y
End Function

Calling Sub Procedure

Sub procedure1(a)
    MsgBox a
End Sub
---
Sub procedure2()
    Call procedure1("ABC")
End Sub

Sub procedure1(a)
    MsgBox a
End Sub
---
Sub procedure2()
    procedure1 "ABC"
End Sub

Calling Function Procedure

Sub procedure1()
     MsgBox procedure2(3, 5)
End Sub

Function procedure2(x, y)
     procedure2 = x + y
End Function

Passing Argument by Reference or by Value

Workbook and Worksheet Object

Sub procedure()
    Dim Sheet As Worksheet
   
    For Each Sheet In Worksheets
        MsgBox Sheet.Name
    Next Sheet
End Sub

Range Object and Cells Property

Worksheets & Range

Sub procedure()
    Worksheets("Sheet1").Range("A1:B5") = "AB"
End Sub

Worksheets & Range

Sub procedure()
    Worksheets("Sheet1").Range("A1, A3, A5") = "AAA"
End Sub

Range & Cells

Sub procedure()
    Worksheets("Sheet1").Range("A1") = "AAA"
    Worksheets("Sheet1").Cells(2, 1) = "BBB"
End Sub

Cells

Sub procedure()
   For i = 1 To 5
        For j = 1 To 5
            Cells(i, j) = "Row " & i & "   Col " & j
        Next j
   Next i
End Sub

offset

Sub procedure()
    ActiveCell.Offset(1, 0) = 1
End Sub

---

Creating and Managing Array

Declaring an Array With Dim Statement

Sub procedure()
    Dim Arr(5)
    
    Arr(1) = "Jan"
    Arr(2) = "Feb"
    Arr(3) = "Mar"
    Arr(4) = "Apr"
    Arr(5) = "May"
    
    MsgBox Arr(1) & "-" & Arr(2) & "-" & Arr(3) & "-" & Arr(4) & "-" & Arr(5)
End Sub

Resize an Array With Redim Statement

Sub procedure()
    'Dim Arr(5)
    ReDim Arr(6)
    
    Arr(1) = "Jan"
    Arr(2) = "Feb"
    Arr(3) = "Mar"
    Arr(4) = "Apr"
    Arr(5) = "May"
    Arr(6) = "Jun"
    
    MsgBox Arr(1) & "-" & Arr(2) & "-" & Arr(3) & "-" & Arr(4) & "-" & Arr(5) & "-" & Arr(6)
End Sub

Manage Dynamic Array

Sub procedure()
    ReDim Arr(5)
    Arr(1) = "Jan"
    Arr(2) = "Feb"
    Arr(3) = "Mar"
    Arr(4) = "Apr"
    Arr(5) = "May"
    
    ReDim Arr(6)
    Arr(6) = "Jun"
    
    MsgBox Arr(1) & "-" & Arr(2) & "-" & Arr(3) & "-" & Arr(4) & "-" & Arr(5) & "-" & Arr(6)
End Sub

Sub procedure()
    ReDim Arr(5)
    Arr(1) = "Jan"
    Arr(2) = "Feb"
    Arr(3) = "Mar"
    Arr(4) = "Apr"
    Arr(5) = "May"
    
    ReDim Preserve Arr(6)
    Arr(6) = "Jun"
    
    MsgBox Arr(1) & "-" & Arr(2) & "-" & Arr(3) & "-" & Arr(4) & "-" & Arr(5) & "-" & Arr(6)
End Sub

Create Multi-Dimensional Array

Sub procedure()
    Dim Arr(2, 2)

    Arr(1, 1) = 1000
    Arr(1, 2) = 1200
    Arr(2, 1) = 1500
    Arr(2, 2) = 2000

    MsgBox Arr(1, 1) & ", " & Arr(1, 2) & vbCrLf _
           & Arr(2, 1) & ", " & Arr(2, 2)
End Sub

Find The Size of an Array

Sub procedure()
    Dim Tensor(1 To 100, 0 To 3, -3 To 4)
    
    Upper1 = UBound(Tensor, 1)
    Upper2 = UBound(Tensor, 2)
    Upper3 = UBound(Tensor, 3)
    
    Lower1 = LBound(Tensor, 1)
    Lower2 = LBound(Tensor, 2)
    Lower3 = LBound(Tensor, 3)
    
    MsgBox Lower1 & ", " & Upper1 & vbCrLf _
           & Lower2 & ", " & Upper2 & vbCrLf _
           & Lower3 & ", " & Upper3
End Sub

---

Decision Structures - IF and Select Case

IF … Then Statement

Sub procedure()
    Age = 20
    If Age >= 18 Then
        Status = "Adult"
        Vote = "Yes"
    End If
    MsgBox (Status & vbCrLf & Vote)
End Sub

IF … Then … Else

Sub procedure()
    Age = 22
    If Age >= 22 Then
        Drink = "Yes"
    Else
        Drink = "No"
    End If
    MsgBox Drink
End Sub

IF … Then … ElseIf

Sub procedure()
    Age = 20
    If Age >= 18 And Age < 22 Then
        MsgBox "You can vote"
    ElseIf Age >= 22 And Age < 62 Then
        MsgBox "You can drink and vote"
    ElseIf Age >= 62 Then
        MsgBox "You are eligible to apply for Social Security Benefit"
    Else
        MsgBox "You cannot drink or vote"
    End If
End Sub

Select Case

Sub procedure()
    Grade = 70
    Select Case Grade
        Case Is >= 90
            LetterGrade = "A"
        Case Is >= 80
            LetterGrade = "B"
        Case Is >= 70
            LetterGrade = "C"
        Case Is >= 60
            LetterGrade = "D"
        Case Else
            LetterGrade = "Sorry"
    End Select
    MsgBox LetterGrade
End Sub

---

Loop Structures

For … Next

Sub procedure()
    For i = 1 To 10
        Cells(i, 1) = i
    Next i
End Sub

For … Next Loop With Step

Sub procedure()
    For i = 1 To 10 Step 2
        Cells(i, 1) = i
    Next i
End Sub

Do While … Loop

Sub procedure()
   i = 1
    Do While i <= 10
        Cells(i, 1) = i
        i = i + 1
    Loop
End Sub

Do Until … Loop

Sub procedure()
    i = 1
    Do Until i = 11
        Cells(i, 1) = i
        i = i + 1
    Loop
End Sub

Do … Loop While

Sub procedure()
   i = 1
    Do
        Cells(i, 1) = i
        i = i + 1
    Loop While i < 11
End Sub

Do … Loop Until

Sub procedure()
   i = 1
    Do
        Cells(i, 1) = i
        i = i + 1
    Loop Until i = 11
End Sub

---

Sorting Numbers In an Array

Sub procedure()
    Dim arr(5) As Integer
    Dim str As String
   
    arr(1) = 8
    arr(2) = 4
    arr(3) = 3
    arr(4) = 7
    arr(5) = 2
    str = ""
   
    For i = 1 To 5
        str = str & arr(i) & vbCrLf
    Next i
   
    MsgBox "Before Sorting" & vbCrLf & str
       
    Call Sort(arr)
   
    str = ""
    For i = 1 To 5
        str = str & arr(i) & vbCrLf
    Next i
    MsgBox "After Sorting" & vbCrLf & str
End Sub
---
Sub Sort(arr() As Integer)
    Dim Temp As Double
    Dim i As Long
    Dim j As Long
   
    For j = 2 To UBound(arr)
        Temp = arr(j)
        For i = j - 1 To 1 Step -1
            If (arr(i) <= Temp) Then GoTo 10
            arr(i + 1) = arr(i)
        Next i
        i = 0
10      arr(i + 1) = Temp
    Next j
   
End Sub

---

Statistics

Random Number and Randomize Statement

Sub procedure()
    Dim str As String
   
    For i = 1 To 5
        str = str & CStr(Rnd) & vbCrLf
    Next i
   
    MsgBox str
End Sub

Sub procedure()
    Dim str As String
   
    Randomize
    For i = 1 To 5
        str = str & CStr(Rnd) & vbCrLf
    Next i
   
    MsgBox str
End Sub

Standard Deviation and Mean

Skewness and Kurtosis

Percentile and Confidence Interval

Profitablity

Creating a Histogram

Finding Median

Generate Random Numbers From Uniform Distribution

Sum Numbers

Compute Factorial

Binomial Coefficient

Cumulative Standard Normal Distribution

---

VBA project

---

Auto-hotkey basic

---

Auto-hotkey project

---

List of posts followed by this article

• post2
• post3

---

Reference

• excel namespace
  • worksheetfunction interface
• shortcuts
• dax reference
  • functions
• vb language reference
• vba language reference
  • overview
  • fuctions
  • excel object model
• vba language tutorials
  • tutorials1
  • tutorials2
  • tutorials3

---

OUTPUT

---

---