VBA Level 3

By Ben Miu

What do we know?

• Creating variables• Loops• Arrays• If-Then Else• Range Objects• Dictionary Objects• Subs/Functions• UserForm

ADO VBA

• ADO stands for ActiveX Data Objects. It allows VBA to communicate with back end databases

• A database back end is useful for storing information if there are more fields that Excel can handle

• It serves as a centralized location for all your tables

• Code is provided on next page

ADO VBA Dim conn As New ADODB.Connection Dim cmd As New ADODB.Command Dim rs As ADODB.Recordset

conn.Open "Driver={Microsoft Access Driver (*.mdb, *.accdb)};};Dbq="DailyRiskFolder & "Daily Risk.accdb;" Set cmd.ActiveConnection = conn

cmd.CommandText = "insert into Pricing (PriceDate, PriceIndex, PricePrice) values(#" & strDate & "#,'" & a.Offset(0, 1).value & "','" & a.Offset(0, 2).value & "')"cmd.CommandType = adCmdTextcmd.execute

cmd.CommandText = "select PricePrice from Pricing where PriceIndex = '" & a.Offset(0, 1).value & "' and PriceDate = #" & finalDate & "#"cmd.CommandType = adCmdTextSet rs = cmd.execute()

FreeFile

• Free file is a method which allows CSV files or TXT files to be read without having to use Excel to parse the data

• Commonly used with the Split function which creates a array for the data values within the CSV

• VB.NET equivalent is streamreader/streamwriter

• Useful for input files which are not XLS

FreeFile intinFile = FreeFile Open InputFile For Input As #intinFile

Do Until EOF(intinFile) Line Input #intinFile, sLine Loop Close #intinFile

intOutFile = FreeFile Open InputFile For Output As intOutFile

For Each eline In strLine If eline <> "" Or eline <> vbCr Then Print #intOutFile, elineNext eline

Close #intOutFile

Classes in VBA

• Class objects are a move towards true object orientation where you can re-use classes in other projects

• New era of programming whereby your code is portable

• Expert level VBA as understanding classes can be very tricky if you don’t get the basics down

• A sample class on next slide

Instances / Constructor / Destructor

• Classes allow you to create separate worlds based on base code where they don’t interact to each other

• Possibility of code conflicts is eliminated with classes and scope control is possible

• No real benefits of classes in simple VBA applications. Starts to make sense as you code more and more and don’t want to repeat yourself

Daily Risk Project

• Hired to bring automation to the risk group• Daily Risk started as separate processes like

partition creation, hierarchy generation and eventually was combined with reporting

• Daily Risk One Button solution is the risk group’s answer to all manual processes. It’s estimated that the Daily Risk process can do at least 3-4 people’s worth of work and cut down on hundreds of man hours per month

Evolution of Risk Needs

• With more object oriented coding, there will be more robust reporting capabilities faster

• For example, if someone wants a HEEN risk report or FSER risk report, and they want customization, object oriented programming base code will maintain the regular template and be able to handle new specific changes

• CONST (Constants allow for all change to take place on one module)

S&T

• S&T is responsible for the development of applications to be deployed enterprise wide or to a specific group

• Process involves requirements document, business analysis, project management, quality assurance, training and use case analysis

• Ted Tea does the VB.NET side whereas Trevor Tilley does the C# side

Two approaches to coding

• Business user understands his own processes, codes himself

• Turn around time is very fast as no requirements documentation is required

• Downside to business user’s coding is if the person ever leaves then someone else must maintain the code

• Could represent a operational risk. Therefore, Trevor Tilley is not a fan of VBA coders running around

When you should automate?

• The idea is that you should automate if the time it takes to code is faster than the time it is to do the process manually and/or the process is a daily process which requires many man hours

• Cutting and pasting one spreadsheet to another is not a justification to write code unless that cutting and pasting is part of a bigger picture

Breaking down to basic steps

• Much of coding is negotiating with other departments to get the data in a format that you can read with macros. Makes no sense to be reading a PDF file as a PDF file comes from a XLS or DOC originally

• All systems essentially have a database backend and SQL scripts can automate output from those databases

• Problem is, the other group may not have the resources to give you what you want

Moving from the small application

• A small cut and paste procedure can be much much more and start to become larger and eventually replace entire processes

• Bonnie part of VFA prepares the performance report which is ultimately driven by daily Sylvan output files

• Don’t just automate the cut and paste, automate the entire process to just one button

Bigger picture

• Small processes to larger processes encompassing more people in your group requires you to be the one that talks to others in your group and automate their work

• Many times, for example, Candy might prepare a file for Felix to deal with, then Felix passes the end result to Wenli. 3 people passing a document around modified 3 different times

Process Map of Reporting

Candy gets PDF from client and types it

into system

Felix inserts benchmarks

Wenli creates reports

How to think about a process

• When presented with a problem, the first thing you should think about is what types of VBA are you going to use. Are you going to use a dictionary, a array, a range? Are you going to use a back end database?

• Do you see this process being something that needs to be done on a regular basis?

Quick garbage code vs. Robust code

Garbage Code vs. Robust Code

• Using a For loop and you counting the number of rows is considered garbage code because it isn’t going to be robust

• Even using a While-Wend loop isn’t robust enough because the users can put spaces in between cells

• Let’s use Excel’s UsedRange approach to assist

How I first learnt VBA

• A D- student in VB class in high school• Later when working at FK Imports, (Giftware

industry) started to use VB code to assist in Crystal Reporting

• Started to become an automator at BMO Middle Office

• TD operations were simplified to one button clicks.

How to learn VBA

• Macro recorder is key to getting the syntax right. Don’t try to memorize the code

• Change the macro recorder’s output with what you have learned

• Don’t always choose the manual way over the automated way

• Books and video’s don’t help a lot because many times you will have to sift through it and it doesn’t help you

• Only way is to think ‘automation’ at every step of the way

Case Studies

Thank You