AkinToMuafapa

Coding using VBA (Excel)

Advertisements

1. Background

In a long hiatus from this blog since early 2016, this would mark a brief return in 2017 to conduct some self-learning. The blog was temporarily halted as upon reflection from previous articles, it would be best to stop until further research and analysis is done.

Instead, I have taken the liberty to reuse the blog to learn other digital skill-sets that may come in handy in the future – with the first being digital coding. Technology is rapidly advancing and will be a key-focus as automation take holds, thus it pays to be digitally literate.

Starting posts on coding that pushed me here were: Reddit’s Beginner’s Intro to Coding:, ‘What I want to code?’ , ‘Please don’t learn how to code’ and the main-tutorials were, Excel-VBA Coding. Other useful links include: Code Academy and Programming Resources.

Final words: The remnants of the blog would be dedicated to starting up on the VBA and basic introductory codes to get started.

2. Setting up Microsoft Visual Basic (Application and Editor)
Notes from: (7/1/2017),

Pressing ‘Alt+F11‘ will open the VBA software for excel coding.

Project Windows – [View] > Project Explorer
Properties Window [View] > Properties Window
Coding Window > Default (on Sheets)

Worksheets under the properties window correspond to the sheets tab in the excel document. The properties window indicates the features of a worksheet including: Visibility  (tab may be visible/invisible when shared). Project Windows showcase each worksheet ready for coding, and a set of code is referred to as a Macro (automated codes).

2.1 Setting up the Developer Bar

The Developer Bar is essential to hot-key macros, record any operations for automation and check for errors – but is not a default tab. To set it up, go under [Add or Remove Programs] > [Microsoft Office Professional Plus 2013] > (Right-Click) Change > Add/Remove Features > Office Shared Features > Click Visual Basic for Applications. Head back to Excel > File > Options > Customise Ribbon > Tick ‘Developer’ [left].

3. [Macros] Keying Basic Codes

To run a Macro, you may do the following:
‘Press F5’ in the VBA software (Run > Run/Sub UserForm)
‘Press F8’ in the VBA software – debug step-by-step (Debug > Step Into)
‘Right click on any cell/text-box/image > Assign Macro’ > Select Sheet’ in Excel
‘Developer > Macros > Options > Shortcut Key’ – Excel hotkeying

3.1 [Macro] Security

To create password access to a macro files, ‘Alt+F11’ > Tools > VBA Project Properties > Projection > Lock Project for viewing > Assign password (do not forget). To access the specific worksheet again, a password requirement is needed. (The security may be breached easily with the right software in place).

To lower security for access to Macros in other PC: Under Developer Tab > ‘Macro Security’ > ‘Disable all macros with notification’. When receiving an excel file with macros ‘xlsm’, be cautious as the specific codes may be malware or disablers. Macros can’t be saved under regular excel files ‘xlsx’, hence saving in them will remove all macro codes.

3.2 [Macro] Basic Coding

Firstly, do not type in capital letters unless naming as excel can spot for errors by underlining in red or via not capitalizing certain words. Sub Profirst() and End Sub, starts the code and end it respectively – all code lies inbetween. ‘Profirst‘ indicate sheet 1, so ‘Prosecond‘, ‘Prothird‘ will be respective for subsequent sheets.

Using space bar followed by ‘_’, will text break a line of code for better viewing.

Range Statements

1. Range(“A1”).Value =                     (Numerical or Text – Text being in “Orange”)
2. Range(“A1”).Formula = “”         (Mathematical: +, -, *, /, *)
3. Range(“A1”).Select                       (Selects specific cell)
4. Range(“A1”).Copy                         (Copies specific cell)
5. Range(“A1”).PasteSpecial           (Pastes specific cell)

1. Range(“A1:E1”).Select                  (Selects collectively from cells A1 to E1)
2. Range(“A1, B1, E1”).Select         (Selects individually of cells, A1, B1 and E1)

1. Sheets(“Sheet2”).Select             (Will reselect a sheet to Sheet2)
2.Sheets(“Sheet1”).Name = “”     (Will name the Sheet within “”)

[This may be combined with the codes specific to range, for additional utilities]
e.g. Range(“A3”).Formula = “=A1+A2”, will sum the A1+A2 values into A3 output
e.g. Range(“A1:E1”).Copy       (Copies the entire range of data from A1 to E1)
e.g. Range(“C1″).Value = Application.WorksheetFormula.Sum(Range”A1:D5”)
Using Formula (sum, stdev, average, max, min) values are available for use.

Selection Statements:

Selection.Offset(“1,0”).Select       ( indicates to move down a row by 1 )
Selection.Offset(“-1,0”).Select    ( indicates to move up a row by 1 )
Selection.Offset(“0,1”).Select       ( indicates to move right a column by 1 )
Selection.Offset(“0,-1”).Select     ( indicates to move left a column by 1 )
ActiveCell.Offset(“1,0”).Select     ( selected cell move down a row by 1 )

Loop Statements

Requires 4 basic statements: 1) Selection, 2) Do Until, 3) Loop Operations, 4) Loop

Range(“A1:A4″).Select
Do Until Selection. Row =
Selection Value = ” ”
Selection Offset (“1, 0”).Select
Loop

Range selects the cell data, that may be followed up by the ‘Selection‘ operation. Do Until gives an instruction for when the loop will terminate while the code between Do Until and Loop will indicate the operations that is to be done. In the above.. Key ” “, and move down row by 1. Indicate ” ” parenthesis only for text functions.

Message Statements

MsgBox operation is a key way to inform users operating the macro of when the procedure may be done or where may the results be specifically found.
MsgBox “The Macro is finshed”
MsgBox “The result is” & Range(“A1”).Value
& is crucial to link “” and Range(“A1”).Value

Application Statements

Application.Quit will terminate the program after it runs
ActiveWorkbook.Saved = True will auto-save when used prior to Application.Quit

Application.ScreenUpdating = True shows macro changes to the spreadsheet as it runs
(Must be keyed in at the end of the codes and before the End Sub).
Application.ScreenUpdating = False will do the opposite and not showcase the changes

(Do Feedback if there are any errors or rooms for improvement as this post will be a work-in-progress).

‘If’ Statements
Exceptionally useful when creating an event scenario with some level of autonomy.

If [Specific trigger is True] Then
[Condition 1 executes]
Else
[Condition 2 executes]
End If

*The statement Else is important, as it says if Condition 1 is not true then Condition 2 must be true. This can further chain newer ‘If’ statements with ‘Else’ to improve a macro.

Input statements:If LCase(Selection.Value) = “yes” Then‘ will fix any ‘yes’ text to be lowercase irregardless if its Yes/YES. Ucase will be fixed to uppercase.

Dir Statements
Allows for a macro to identify a file in a specific location
Dir (pathname, attributes)
*Note: Mynah refers to my specific Username on the interface

File = Dir(“C:\Users\Mynah\Desktop\Test1.docx”)
MsgBox File

(Reasoning: Assigning the Dir function to File (need not be specific) allows for detection to be seen in the MsgBox. ‘C:\Users\Mynah\Desktop\Test1.docx’ is the pathname.

1. File = Dir(“C:\Users\Mynah\Desktop\T*.docx”)
2. File = Dir(“C:\Users\Mynah\Desktop\Test?.docx”)

(1) will look for any docx. file that starts with a letter T while (2) may be used if the last character of the file name is unknown.

Combining If and Dir Statements 
Doing so will permit the user to create a program that checks if a file is present or not
Scenario 1: Run a macro to detect if a FILE name Report.docx is present on Desktop

File = Dir(“C:\Users\Mynah\Desktop\Report.docx”)
If Len(File) > O then
Msgbox “Report File is found on Desktop”
Else
Msgbox “Report File is found on Desktop”
End If

Lenindicates the number of char(characters), Report.docx (has 10 char). So if the file exist, then the Len value must be >0.

Scenario 2: Run a macro to detect if a DIRECTORY is present and if not, create one on the Disk Space (C:\)

MyFolder = “C:\Help”
File = Dir (MyFolder, vbDirectory)

If Len(File) > 0, Then
MsgBox (“File & Exists in the Disk Space”)
Else
MkDir MyFolder
MsgBox (“Help File has been created”)
End If

(Check This PC> OS(C:) to affirm that the Help Folder has been created)
Running the macro once (if ‘Help’ file is not present) will indicate that Help File is created using MkDir. Re-running the macro will indicate that it exists in the Disk Space. vbDirectory is crucial to specify the Directory and relocate the newly created file.

MkDir path name or MkDir “C:\Users\Mynah\Desktop\Folder”
Will create a designated folder in the specific path name keyed

Sorting Statement
Sorting allows for a better categorisation of information that may be done manually.

This may be done alphabetically, numerically, a customised order which are based on a selected column (e.g. names, email, numbers etc.)

Range(“A1″), Sort Key1:=”B2”, Order1:=xlAscending, Header:=xlyes
Range(“A1″), Sort Key1:=”B2”, Order1:=xlDescending, Header:=xlyes

Do not forget that there is a spacing after the comma and ‘Sort’.

Range(“A1”) indicates all data in the spreadsheet
Range(“A1:D5”) selectively indicates data from A1 to D5
Sort begins the function and must contain Key1 and Order1
Key1:= “B1”
specifies column B to be sorted
Order1:= Ascending indicates to be sorted by increment
(numerically/ alphabetically, numerical has priority if sharing column)
Header:=xlyes allows the macro to exclude the first row

Note: ‘:=’ indicates text data while ‘=’ represents numerical data.

Advertisements

Advertisements