Introduction to Visual Basic for Applications (VBA) in Excel

Visual Basic for Applications can be used to extend the capabilities of Excel with custom calculations. Microsoft Excel itself is not a programming language but does include an integrated development environment for recording and running macros (VBA environment). Excel macros are a series of commands that can be repeated to operate repeatedly on sets of data.

Below is an example problem that demonstrates whether a tank of particular dimensions will overflow with a certain amount of time. The tutorial shows how to import data from the worksheet, perform volume calculations, determine whether the tank will overflow with an IF statement, print out a message box, and export the volume values to the active worksheet.

This same example problem is also demonstrated with MATLAB and in the Python programming language

Generative AI Learning

Use these prompts to test your understanding after completing the tutorial. This is the first exercise where AI-generated code is on the table - practice the full pattern: specify, generate, verify, defend.

"Quiz me with 5 questions, one at a time, on VBA in Excel: the difference between a recorded macro and a written Sub, the difference between a Sub and a Function and which one can be called from a worksheet cell, how VBA reads and writes cells (Range and Cells, and why Cells(row, col) is 1-indexed), what an If-Then-Else block does in the tank-overflow example, and why a message box is a poor place for results a later calculation needs. Grade my answers and list my misconceptions."
"I need a VBA macro and I will write the specification; you write the code ONLY from my spec. My spec: {inputs: which cells, what units; calculation: state it precisely, e.g., tank volume and overflow check; outputs: which cells and message; edge cases: negative or empty inputs}. After the code, list the assumptions you made that my spec did not pin down, and give me 3 test cases (input values with expected outputs, one at a boundary) that I must run before trusting the macro."

Tip: The second prompt is the pattern for every code generation in this course: an ambiguous spec produces confident wrong code. Run all 3 test cases, including the boundary case - a macro that works on one example is not verified. Compare the generated macro to your own recorded/written version: differences are where the learning is.

What to Turn In

Submit a short report (PDF, 1-2 pages) with your homework that curates your results into a demonstration of what you learned. You may use Generative AI to help write the report, but you must guide it to the correct code, justifications, and assumptions. Answer these questions:

  1. Include your own macro (written or recorded and cleaned) for the assigned problem, with a comment on each block saying what it does.
  2. Include the spec you wrote for the AI, the generated code, and the results of the 3 verification test cases (one boundary case). Did any test fail, and what did you fix - the spec or the code?
  3. What assumptions did the AI list that your spec left open? Which one could have produced a wrong engineering answer?
  4. For the tank-overflow logic: hand-calculate one case (given dimensions and fill rate, does it overflow?) and show the macro agrees.
  5. From the quiz prompt: one question you missed and the corrected answer.

Course Information

Excel and VBA

Python

MATLAB

MathCAD

Related Courses

Admin