Basics of Advanced Excel and VBA for Civil Engineering

 Advanced Excel and VBA for Civil Engineering

Accessing the Visual Basic editor

1) Office 2003 --> Tools --> Macro --> Visual Basic Editor

2) Office 2007 --> Office Button --> Excel Options --> Popular --> Show Developer Tab --> Press Ok --> Goto developer Tab --> Visual Basic Editor

3) Office 2010-13 --> Right Click on any ribbon tab --> Click Customise Ribbon --> Devloper tab appears --> Visual basic editor

To back to excel click 1st button ribbon which looks like excel or Keyboard shortcut is Alt + F11

Two windows in VB Editor

1) Project Explorer

2) Properties Window

Creating Module

To write VBA code we write it in module, Right click in Project window --> insert --> Module

To switch between different modules Double click on modules in project explorer windows

Renaming Modules

To rename it we have to change it in properties window but first we have to select in project explorer window.

No space is allowed while naming modules also many special characters are also not allowed.

Setting up of font which are going to use in VBA code, 

Tools --> Font, size, colours

Syntax error text --> Red

Comment text --> Green

Note:- Above colours of notifications can be changed as per user requirement

Basics of Writing VBA Code

1) Beginning a subroutine

2) Laying out code neatly

    Basic VBA codes follow following sentences

        Thing.Action

        Object.Method 

3) Writing Comments

        To add comments(') sign is used or REM can be used which means remark.

Project:- To Create a Brand New worksheet

Alt + F11  -> to go to VB editor

Insert new module as explained above (In project window right click)

Rename module as explained above (Properties Window)

'Beginning of subroutine

Sub CreateNewSheet ()

    

    Worksheets.Add  '<-- here worksheet is object and Add is method

End Sub

Project:- Choose where to insert New worksheet

Sub CreateNewSheet ()

    

    Worksheets. Add. worksheets("Sheet 3")  '<--This will add sheet before "sheet 3"

    Worksheets. Add after:= worksheets("Sheet 3")  '<--This will add sheet After "sheet 3"

     Worksheets. Add befor:= sheets(1)  '<--This will add sheet before "sheet 1" i.e. at Start

     Worksheets. Add After:= sheets(sheets.count)  '<--This will add sheet after last sheet i.e. at End

End Sub

Project:- Insert Multiple New worksheets

Sub CreateNewSheets ()

    

     Worksheets. Add After:= sheets(sheets.count), Count:=3  '<--This will add 3 sheets after last sheet 

End Sub

Project:- Insert New Chartsheets

Sub CreateNewChartSheets ()

    

     Worksheets. Add After:= charts("Chart1")  '<--This will add new chartsheet

    Sheets.Add Type:= Xlsheettype.xlchart

End Sub

Running Macro

1) Press green triangle on ribbon

2) Run on ribbon

Saving Files

Clicking Alt + F11 saves the files or you can use Ctrl + S or Save on ribbon

Reopening files

Reopening files containing VBA code in -

Office 2010-13 --> Security Warning --> Enable content

Office 2007 --> Security warning --> Options -> Enable

Errors and Debugging in VBA

Type of Errors

    1) Syntax Errors

    2) Compile Errors

    3) Runtime Errors

Debugging a subroutine

    1) Stepping through code

    2) Setting breakpoints

    3) The debug toolbar

Project:- Selecting Single cell using VBA

Sub SelectCell()

    Range("A2").select  '<-- By using Range

    ActiveCell.value = 10  

    Cells(3,1).select  '<-- By using Cells,  3 '<-- Row Number and 1 <-- column number

    ActiveCell.value =" Yogi"

End Sub

Project:- Selecting Multiple cells using VBA

Sub SelectCells()

    Range("A2:A10").select  

    Range("B2","C2").select

    Range(Cells(5,4), cells( 6,5)).select

End Sub

Project:- Selecting Entire region using VBA

Sub SelectRegion()

    Worksheets("Sheet1").activate

    Range("A2").currentregion.copy

    Worksheets("Sheet2").activate

    Range("A2").PasteSpecial xlpastecolumnwidths    

End Sub

Working With Variables

1) Using Non-Declared Variables

2) Explicitly declared variables

3) Variable data type

4) Errors when using variables

5) Changing the scope of Variables

Project:- Creating Non-declared variable

Sub AddNewTopicInIndex()

    NewFileName=" Yogi"

    Worksheets("Index").Activate

    Range("A2").End(xldown).offset(1,0).select

    

    Activecell.value = NewFileName

    Msgbox NewFileName&" is added to the List"  '<-- This will display messagebox"

End Sub

Project:- Using Object Variables

Sub StoreRange()

    Dim filenamecells as Range  '<-- Variable declared

    Set filenamecells = Range("B3:B15")

    filenamecells.font.color = rgbblue

End Sub

Using If Statement

Project:- Using if statements

Sub TestFileLength()

    Dim filename as String  '<-- String Variable declared

    Dim fileLength as Integer  '<-- Integer Variable declared

    Dim fileDescr as String 

    Range("B10").select

    filename = Activecell.value

    fileLength = Activecell.offset(0,2).value

        if fileLength < 100 then   '<-- Starting of If Statement

            fileDescr =" Short "

        Else

            fileDescr =" Long "

            

        End if                                       '<-- Ending of If Statement

msgbox filename&" is "&fileDescr

End Sub

Project:- Using if statements with multiple elseif

Sub TestFileLength()

    Dim filename as String  '<-- String Variable declared

    Dim fileLength as Integer  '<-- Integer Variable declared

    Dim fileDescr as String 

    Range("B10").select

    filename = Activecell.value

    fileLength = Activecell.offset(0,2).value

        If fileLength < 100 Then   '<-- Starting of If Statement

            fileDescr = " Short "

        ElseIf fileLength < 150 Then    '<-- Unlimited conditions can be added using Elseif

        

            fileDescr = " Medium"

        Else

            fileDescr = " Long "

            

        End If                                     '<-- Ending of If Statement

msgbox filename&" is "&fileDescr

End Sub

Using For Statement

Sub write1to1000()

    Dim I

    For I = 1 To 1000

    Cells(I, 2) = I                    ' <........ It will populate rows in column B from 1 to 1000

    Next I

End Sub

result will look like

Using Loops

Do Until and Do While

Basic Do loop statement

Sub SimpleLoop()

        Range("A3").select

        

        do

        Activecell.offset(1,0).select

        Loop

End Sub

Conditionally ending a loop with do until

Sub SimpleLoop()

        Range("A3").select

        

        do until Activecell.value=""      '<----- Added do until condition to stop the loop

        Activecell.offset(1,0).select

        Loop

End Sub

Conditionally ending a loop with do until with Loop Until

Sub SimpleLoop()

        Range("A3").select

        

        do 

        Activecell.offset(1,0).select

        Loop until Activecell.value=""      '<----- Added do until condition to stop the loop

End Sub

Do While

Sub SimpleLoop()

        Range("A3").select

        

        do while Activecell.value < >""     '<----- Added do while condition to stop the loop

        Activecell.offset(1,0).select

        Loop 

End Sub

Existing from a loop prematurely

Sub SimpleLoop()

        Range("A3").select

        

        do 

            if Activecell.value=""   then Exit do   '<----- Existing from a loop prematurely

            Activecell.offset(1,0).select

        Loop 

End Sub

In Progress ........................