AMA Deep Dive on Data Solutions & VBA Programming

A quick note from the editor:

I recently got to join and moderate a live AMA call that Travis Cuzick did with our students in the Zero To Mastery virtual campus.

Students and I asked questions about Data Solutions, VBA, what it is and how it works, how it compares to Python for Excel, some technical questions and much more.

You can check out some of Travis’s answers to the questions from his AMA call below, but first, let me introduce him.

Who is Travis Cuzick, and why should you listen to him?

Travis has been architecting and coding data solutions for well over a decade and for some of the biggest companies on the Fortune 500.

As a self-taught developer and an instructor inside the Zero to Mastery Academy, Travis aims to make the learning process as efficient, intuitive, and fun (yes, fun!) for his students as possible to help them achieve real-world success that extends far beyond the completion of his courses.

Travis applies exceptional SQL programming skills and extensive experience querying and manipulating multi-terabyte enterprise data stores for his work as a Data Solutions Engineer with major U.S. financial institutions.

With a focus on being a strong communicator that is effective at defining complex business requirements and concisely summarizing detailed data analysis to business leaders, Travis can both talk the talk and walk and walk.

This is why Travis is the creator of 3 exceptional courses at ZTM:

That’s over 35 hours of in-depth training so as you can see, Travis clearly knows what he's talking about when it comes to business intelligence and data modeling.

It was a great interview so with the intro out of the way, let’s dive into his AMA answers 😀.

🙋AMA with Travis Cuzick on Data Solutions & VBA Programming🙋

Want to watch the video, ask questions in our Discord, or join in live on our next expert AMA?

These are all exclusive access for members of Zero To Mastery. You can find out more details about every course and the other benefits of being a ZTM member here.

What are Data Solutions?

Data solutions is basically an overarching name for a group of technology that performs some sort of role in the process of transforming raw data and then helps transform it into something actionable and useful.

Typically this is some kind of reporting or analytics software or even predictive analytics.

A very simple example could be:

You’re tracking site visitors via analytics
You notice the bounce rate is high and sales are zero for a particular offer
On further inspection, you see that there's an issue with the page load or add to cart button, etc.

As you can see, there’s never normally a situation where you have data in and then an actionable output right away. There are usually intermediate steps (especially with large data sets), where you need to delve into that information and find the cause and effect.

Usually, you’ll take the data from the production database and then ‘forklift’ it over to a reporting database or ‘data warehouse’. That data is then reshaped and transformed in such a way that it’s more suitable for analysts to query and use in their analysis and the reports that they build.

Again to recap, Data solutions is a reference name for any technology used during that entire process.

What is Big Data?

Big Data is pretty much the same concept as normal data intelligence.

To capture, analyze and understand raw data, so that we can make actionable insights from it.

The difference with ‘big data’ however refers to the volume of the data that’s being processed. With ‘big data’ we’re looking at data sets on the scale of tens of millions of rows of information!

As a simple rule of thumb, if you can import your data set into Excel and it works ok, then it’s probably not big data. If it crashes due to the size (and not because of your pc processor quality), then you’re in the realm of big data.

A major difference with big data is the different data formats that are analyzed, which you might not normally be using. For example, rather than just structured data from a relationship database, you might start looking at images and text files etc.

Another differing factor is the number of intermediary steps and also analytical channels. Usually there are more steps between the acquisition and assessment in big data, but there's also usually more data analysts from different areas pulling from that same source to gather their own specific insights relevant to their fields.

Who are Data Solutions for? Who uses it or needs it?

Data solutions are for anybody who either:

Makes decisions based on data, or
Someone who captures and creates analytics for those decision makers

Even if you’re a department manager for a small team, you should still be capturing and using data to help you make decisions.

Even here at ZTM, we look at every channel from traffic to sales, and each head is responsible for their own analytics capture and delivery to help people make better decisions.

What is a Data Solution Architect?

A data solutions architect is a strategist who helps design full pipeline analytics. Their job is to help companies come up with the best approach for them to capture, analyze, report and understand that data. Not every company is the same and so they need different tools and implementations.

To be clear, the architect is the one who creates the strategy. A Data Solutions Engineer would be someone who then helps implement that strategy, all the way from adding and configuring tools to writing code for specific features.

What is VBA Programming?

VBA stands for Visual Basic for Applications and it's a programming language created by Microsoft. The syntax is very similar to Visual Basic, as it’s a direct offshoot of it.

The main goal or even benefit of VBA is that it allows you to automate Microsoft Office applications. If you wanted to, you could create a basic email autoresponder with just Outlook and VBA!

However, VBA is most commonly used to help automate functions, actions or repetitive/redundant tasks inside of Microsoft Excel.

For example

Let’s say that you work with another company and they send you data reports that you want to import into Excel. The issue is, the formatting is always messed up. Every time they send a file you have to clean it up, make sure the cells are in the correct place etc, and it takes 5-10 minutes to do.

As you can imagine, this would be both frustrating and time consuming to do manually.

But with VBA, you could code a procedure to do all that for you so that you don’t have to do it yourself. Then every time the miss formatted reports come through, VBA resolves it in seconds and adds the correct information!

Not only will this save you time and money, but it also helps you bypass potential user error, where you might not reformat the form correctly yourself one day. The VBA process would always do it correctly for you.

What can I do with VBA Programming?

Although you might think that VBA is basic (no pun intended), it’s actually a Turing complete language, which means it can do the exact same things that other programming languages can do. You can literally build applications and programs with VBA, with working interfaces similar to what you might build with anything else.

Is VBA hard to learn? What's the best way to learn it?

No. VBA is actually really easy to learn. So easy in fact, that I feel it’s a great way to get your feet wet and start learning to code.

One of the main reasons for this, is that you can actually kind of learn to code inside of VBA by reverse engineering one of their tools…

So let me explain. VBA has something called a ‘macro recorder’ built into it that records keystrokes.

It takes those keystrokes and the actions you’ve done, and then translates those actions into VBA code so that it can do it again if needed. This is how easy it is to record and then automate repetitive tasks, but it’s also a great way to look at the code that VBA generates and learn by reverse engineering and examining what was created.

By studying your code like this, you start to get an intuitive understanding of how VBA works and how to code with it. Especially if you go in and start tweaking the code directly and letting it run, and then seeing if the changes you made work the way you thought it would.

Is Python better than VBA for Excel?

It depends. Let’s say that:

If you already know Python
You’re a good Python programmer and you don’t know VBA at all
And you have no issues with getting Python installed on your work computer (security or access issues etc)

Then sure, Python would be a better option than learning VBA from scratch. You can add in Python Excel packages and automate Excel directly with Python.

However, if you’re not a developer but perhaps an analyst or head of department in your company, then you might struggle convincing the IT department to allow you to install Python on your computer. VBA however is already inside of Excel so you don’t need to jump through hoops to get started.

Also, VBA is dedicated to automating Excel. Not only that, but because it’s an older language, all the features that you might need have already been created, whereas they may not exist in Python packages.

Tl;dr if you know Python already and have it on your work machine, then sticking with Python is an easy option. If you’ve not learned to code before and may have trouble getting new software on your machine, then VBA is the better choice.

What are VBA Macros?

A Macro is simply the name for a program that’s been written in VBA. (Sometimes called subroutines also).

A cool feature that you can use this for, is to create user tools inside of Excel that perform specific functions. Even cooler, you can send those tools to other Excel users and have them be able to use them in their sheets also.

What is a VBA Array?

A VBA array is simply a type of data structure inside of VBA. It allows you to store data in almost a list like structure or table but inside the memory, not the spreadsheet itself.

Arrays have their benefits and uses and I’ll share an example in a second, but personally I prefer to simply just use Excel for basic tasks, simply because Excel is fantastic at storing large lists of data on its own.

For example

For a basic task I might be tempted to fetch data from the spreadsheet into an array, manipulate it in the array, and then write it back into the spreadsheet. Easy enough, but because Excel is basically a large array anyway, I just cut out the middle man! I fetch it directly from the spreadsheet and then write it back to the spreadsheet cells, without needing to involve that extra step or the array.

However, here’s an example of when you might want to use the actual array function instead.

Let’s say that you’re looking for outliers in your data, and you want to find each of them inside a large data set and then isolate and analyze them. Well to do that, you would need to iterate over that entire range of cells, and for each cell, you would need to test them with a complex logical condition to find the outliers, and then store the values of any of those cells that meet that set condition into an array.

You could then take those cells that met your criteria from the array and display summary statistics of those outliers, and look at the standard deviation etc. (Excel has a whole range of statistical functions built in also).

What is VBA Range?

A range is simply the term we use to describe a collection of cells inside a spreadsheet. When you’re using VBA with Excel, you’re going to see the term range come up often in the macro recorder code, simply because almost every action we take is to manipulate information in a range of cells.

Note: A single cell can also be referred to as a range.

What is a VBA ‘For Loop’?

A loop is simply a programming construct that lets you do repetitive actions. It ‘loops’ back to the start and does again etc.

A ‘For loop’ is simply a type of loop, and there are a few different flavors of loop inside VBA. In fact, there are 2 different types of ‘for loop’ inside VBA!

We have the ‘For Next’ loop and the ‘For Each’ loop.

The ‘For Next’ Loop allows you to loop a task for a set number of times
The ‘For Each’ loop will iterate over a collection of objects, allowing you to perform tasks over multiple tabs or worksheets

What is your favorite VBA tool/function/formula in Excel?

One of my favorite things about VBA really, is that it's a very practical outcome oriented language vs an aesthetic language. The users don’t care about spaces vs tabs- they care about the bottom line and getting the correct end result.

If I were to pick a specific function, then I would have to say, my favorite is any function that I have built custom for myself. Mainly because once you’ve done this, you’ve usually progressed from someone who’s getting started and has written scripts for small tasks, and are actually becoming more of a Software Developer.

Heck, you could even write a collection of user defined functions and put them together as an add-in for other people to use, which basically becomes you sharing a piece of software with each person!

Is VBA going away? Is it worth still learning this language?

There was a rumor a few years back that Microsoft was trying to transition away from VBA.

Now, you might think this is a bad sign but the thing is, when those rumors occurred there was a huge public outcry, simply because VBA is deeply embedded in multiple industries. This caused Microsoft to rethink their plans, and sure, they are experimenting with using typescript in Excel for Web but honestly, VBA is used in so many industries that it’s not going anywhere soon.