Excel VBA

Sub HelloWorld()

    MsgBox "Hello VBA World!"
    Range("A1").Value = "Message shown"

End Sub

Expanding selection–

//The following code will move the selection A1:C3 to 1 column and 1 row.

Range("A1:C3").Offset(1, 1).Select

//Resize will keep the active cell and expand the selection as defined.

Range("A1").Resize(2, 2).Select

This code will navigate to another cell based on currently active cell–

//it will put 10 in the immediate right cell

ActiveCell(1, 2).Value = 10

If-ElseIf-Else conditional logic syntax–

If MyNumber = 10 Then

    MsgBox "Number = 10"

ElseIf MyNumber = 11 Then

    MsgBox "Number = 11"

Else

    MsgBox "Not 10 or 11"

End If

Changing color–

ActiveCell(1, 2).Interior.Color = RGB(0, 255, 0)

Vertical Allignment:

ActiveCell.VerticalAlignment = xlBottom

ActiveCell.VerticalAlignment = xlCenter

ActiveCell.VerticalAlignment = xlTop

Horizontal Allignment:

ActiveCell.HorizontalAlignment = xlRight

ActiveCell.HorizontalAlignment = xlCenter

ActiveCell.HorizontalAlignment = xlLeft

Working with multiple properties–

With ActiveCell.Font

    .Bold = True
    .Color = vbBlue
    .Name = "Arial"
    .Size = 22
    .Italic = True

End With

String Methods–

LCase("samPLE TExt")    //converts to lower case
UCase("samPLE TExt")    //converts to upper case
Len("samPLE TExt")      //gives length of the string
Trim(" David Gilmour ")  //trims the front and back spaces

finding and replacing–

OriginalText = "warld"
CorrectedText = Replace(OriginalText, "a", "o")

Email = "abc@gmail.com"
Location = InStr(Email, "@")  //index of @ i.e. 4. If not found it'll return 0


Left(Email, 3)    // will return 3 left char
Right(Email, 3)    // will return 3 last char

Replacing a part of a String–

Email = "abc@yahoo.com"
partStr = Mid(Email, 4, 6)

MsgBox partStr     // This will select 6 char starting from 4th position.

For loop–

//Following code will display 1 to 10 in message box

For num = 1 To 10
    MsgBox num
Next num

For num = 1 To 10 Step 3    // Step 3 means the loop variable num will get a increment of 3 each time
 MsgBox num
Next num

For Each loop

PD-56-856
PD-56-856
PD-56-856
PD-56-856      // A2 to A5 cell data. Now the code will remove all - from the cells

Dim Mycell As Variant    // Mycell will contain the cell, so it shouldn't be Integer

For Each MyCell In Range("A2:A5")
    MyCell.Value = Replace(MyCell.Value, "-", "")
Next MyCell

For referring a cell address inside a loop we can select cells by following notation–

//both selects cell A1
Cells(1, 1)
Cells(1, "A")

Array–

Dim arr(4) As Integer

// Unlike other convention, this array can hold 5 integers. arr(0) to arr(4)
// so if we want specific number of element then we can write one step less or define explicitly

Dim arr(0 To 3) As Integer

// LBound(array) returns the lowest index and UBound(array) returns highest index of array

Dim MyArr(6 To 12) As Integer
Range("A2") = LBound(MyArr)    // 6
Range("A2") = UBound(MyArr)    // 12

split and join functions

Split(txt, "-") //return an array which will be created by  "-"
Join(arr, " ")   //return an array joined by " "

if we declare a Sub as private, it wont show up in the assign macro dialogue.

Private Sub SecondCode(Bold As Boolean, Name As String)
    Exit Sub
End Sub

Showing OK/Cancel in a msgbox

Dim Response As Variant
Response = MsgBox("Number Required", vbOKCancel + vbInformation, "Title of msgbox")

Dim Response As Variant
Response = MsgBox("Number Required", vbOKCancel + vbCritical, "Title of msgbox")
    
If Response = 1 Then
    MsgBox ("You pressed OK")
ElseIf Response = 2 Then
    MsgBox ("You pressed CANCEL")
End If

!! Important: Subs dont return any value but Functions do return value.

Functin myFunc as Integer
    myFunc = 3           // 3 will be return value here
End Function

Accessing all worksheet functions:

SumTotal = WorksheetFunction.Sum(Range("B1:B5"))

AutoFit

Range("C1").Columns.AutoFit

various useful VBA codes

Sub HideShape()
    ActiveSheet.Shapes("Picture 1").Visible = False
End Sub

Sub UnhideShape()
    ActiveSheet.Shapes("Picture 1").Visible = True
End Sub


' copy cell including shapes

Sub CopyCell()

    Range("A1").Select
    Selection.copy
    Range("B1").Select
    ActiveSheet.Paste
    
End Sub

' copy cell including shapes

Sub CopyCell2()

    Range("A2").Select
    Selection.copy
    Range("B1").Select
    ActiveSheet.Paste
    
End Sub


' this deletes all shapes in the range of B1:B2

Sub x()
Dim s As Shape
For Each s In ActiveSheet.Shapes
    If Not Intersect(Range("B1:B2"), s.TopLeftCell) Is Nothing Then
        s.Delete
    End If
Next s
end sub