Sales report

Tags

Hey, Georgina! Could you write a program?

Georgina
Georgina

Sure!

Here's the workbook:

Workbook

The user types an output file name in the worksheet, and types sales figures as well.

When the user clicks the Run button, the program makes a file with a sales report, like this:

  • Sales report
  • Goat smiles sales: (some number)
  • Goat cuteness sales: (another number)
  • Total sales: (total)

Put it in a plain, unformatted text file.

Oh, and the Run button fills in total sales in the workbook, too.

Georgina
Georgina

Let's see. Start Excel...

Now make the workbook...

Workbook

OK, now the code. Start the code editor with Alt+F11...

I like to plan programs by writing comments first. Looks like IPO, so I'll add comments for that.

Planning

Georgina starts by planning her code. She likes to do that with comments. Other people like to draw diagrams. Do whatever suits you best.

Georgina
Georgina

Here's a start.

  • ' Input.
  •  
  • ' Processing.
  •  
  • ' Output.

Hmm, I'll start with the output I want, and then work backwards. Now, what patterns? The program makes a file, writes out some variables. Oh, and it outputs the total to the worksheet as well.

  • ' Input.
  •  
  • ' Processing.
  •  
  • ' Output.
  • ' Pattern: Output to a file, all the variables.
  • ' Pattern: Variables to cells, for total sales.
Ray
Ray

Hey, Georgina! Why did you start with the output? Why not start at the top of the program, with the input?

Georgina
Georgina

The output is the goal, what the program does. I start there, and figure out what the code has to do to meet the goal.

Working backwards

Georgina is working backwards. Write the output code first. Then you know what variables you'll need from processing. Once you've designed processing, you know what you need from input.

But you can work from input to output, too, if you already have a good idea of how the program is going to look.

Principle

Working backwards

Start by understanding a program's goal, then work backwards.

Georgina
Georgina

I think I'll add in the glue variables now. Then I'll know what the input and processing steps need to give to the output step.

I'll need variables for smiles sales, cuteness sales, and total. Oh, and the filename gets input from the worksheet, too.

  • Dim sSmileSales As Single
  • Dim sCutenessSales As Single
  • Dim sTotalSales As Single
  • Dim tFileName As String
  •  
  • ' Input.
  •  
  • ' Processing.
  •  
  • ' Output.
  • ' Need: sSmileSales, sCutenessSales, sTotalSales, tFileName
  • ' Pattern: Output to a file, all the variables.
  • ' Pattern: Variables to cells, for total sales.
Marcus
Marcus

Ooo! I was wondering why you didn't start with the Dims. You did that once you worked out what variables you need.

Cool!

Note

You can see how Georgina plans her work. You can plan differently, but you should do it. Don't jump in and start writing code; it will take longer, and be more frustrating.

Georgina
Georgina

OK, I could start writing the output code here, or plan some more...

Maybe make sure I have the variables I'll need. For processing, I'll need the total, and that will be from the smiles and cuteness sales.

  • Dim sSmileSales As Single
  • Dim sCutenessSales As Single
  • Dim sTotalSales As Single
  • Dim tFileName As String
  •  
  • ' Input.
  •  
  • ' Processing.
  • ' Compute total sales.
  • ' Need: sSmileSales, sCutenessSales
  • ' Provide: sTotalSales
  •  
  • ' Output.
  • ' Need: sSmileSales, sCutenessSales, sTotalSales, tFileName
  • ' Pattern: Output to a file, all the variables.
  • ' Pattern: variables to cells, for total sales.

Needs and provides

Each chunk of code needs some data to do its work, and provides something to the next piece of code. The processing step computes total sales. To do that, it needs smiles sales, and cuteness sales. It provides total to the output step.

Each pattern tells you what it needs and provides. Open the pattern catalog (remember the right-click-on-a-link trick), and check out a few of the patterns (no need to read them through). Each one has Needs and Provides, kind of like each pattern has its own input and output.

Georgina
Georgina

Might as well show what the input will provide.

  • Dim sSmileSales As Single
  • Dim sCutenessSales As Single
  • Dim sTotalSales As Single
  • Dim tFileName As String
  •  
  • ' Input.
  • ' Provide: sSmileSales, sCutenessSales, tFileName
  •  
  • ' Processing.
  • ' Compute total sales.
  • ' Need: sSmileSales, sCutenessSales
  • ' Provide: sTotalSales
  •  
  • ' Output.
  • ' Need: sSmileSales, sCutenessSales, sTotalSales, tFileName
  • ' Pattern: Output to a file, all the variables.
  • ' Pattern: variables to cells, for total sales.

Breaking a task into smaller tasks is called decomposition.

Principle

Decomposition

Break a task into smaller tasks, do each smaller task, and put them back together.

Georgina
Georgina

OK, time to start writing some code. I'll start at the bottom again.

Note

Georgina could have started writing the input code here. She's planned things out so well, that she knows what the input code has to do.

Georgina wants to keep working backwards, and that's OK, too.

Georgina
Georgina

Let's see. The first pattern is output to a file.

Just remind myself of what that's like. Check the pattern catalog.

Open the pattern catalog

Pattern

Output to a file (plain text)

Open the file, Print to it, and Close the file.

Open the file, write to it, and close. Open first. The file name is in the variable sFileName; the input chunk does that, I'll work out how later.

  • ' Output.
  • ' Need: sSmileSales, sCutenessSales, sTotalSales, tFileName
  • ' Pattern: Output to a file, all the variables.
  • Open ThisWorkbook.Path & "\" & tFileName for Output As #1
  • ' Pattern: variables to cells, for total sales.

I'll add the close while I'm thinking about it.

  • ' Output.
  • ' Need: sSmileSales, sCutenessSales, sTotalSales, tFileName
  • ' Pattern: Output to a file, all the variables.
  • Open ThisWorkbook.Path & "\" & tFileName for Output As #1
  •  
  • Close #1
  • ' Pattern: variables to cells, for total sales.

Copy-paste - with care!

Georgina copied the Open statement from the pattern. Then she changed it to what she wanted.

Important! If you don't understand the pattern and your own task, chances are that copy-paste won't help. Copy-paste-and-cross-fingers will take longer, and be more frustrating.

Georgina
Georgina

Now put in the code to do the actual output.

  • ' Output.
  • ' Need: sSmileSales, sCutenessSales, sTotalSales, tFileName
  • ' Pattern: Output to a file, all the variables.
  • Open ThisWorkbook.Path & "\" & tFileName for Output As #1
  • Print #1, "Sales report"
  • Print #1, "Goat smiles sales: " & sSmileSales
  • Print #1, "Goat cuteness sales: " & sCutenessSales
  • Print #1, "Total sales: " & sTotalSales
  • Close #1
  • ' Pattern: variables to cells, for total sales.

That looks right, but I wonder if it is? Maybe I should test it

Principle

Incremental testing

Write a little code, then test it. Write a little more code, then test it.

Georgina
Georgina

OK, save, and press the run button in the worksheet.

Workbook

Huh?

Path not found

Hmm... anyone know what that could be?

Note

Asking friends about errors is a Good Thing.

Adela
Adela

All the variables are empty when the program starts. tFileName is empty, so maybe the Open statement won't work.

  • Open ThisWorkbook.Path & "\" & tFileName for Output As #1
Georgina
Georgina

Oh, yeah! Thanks, Adela!

I'll add a value for tFileName, just for testing.

  • ' Pattern: Output to a file, all the variables.
  • ' Test.
  • tFileName = "test.txt"
  • Open ThisWorkbook.Path & "\" & tFileName For Output As #1

Try it now... Hey! No error!

It should have made a new file, in the folder where the workbook is, called test.txt.

File list

Hey, there it is! M0000t!

Open the file in Notepad:

File in Notepad

Got it!

Hooray! Georgina's on the right track.

Georgina
Georgina

You know, I might need that test line again. So I'll leave it there, and comment it out.

  • ' Pattern: Output to a file, all the variables.
  • ' Test.
  • 'tFileName = "test.txt"
  • Open ThisWorkbook.Path & "\" & tFileName For Output As #1

Commenting out

Georgina put a ' in front of the test code, making it a comment. Excel will ignore it now, but the code is still there, in case it's needed later.

Georgina
Georgina

Now to finish the output chunk, with the code for the second pattern.

  • ' Output.
  • ' Need: sSmileSales, sCutenessSales, sTotalSales, tFileName
  • ' Pattern: Output to a file, all the variables.
  • ' Test.
  • 'tFileName = "test.txt"
  • Open ThisWorkbook.Path & "\" & tFileName For Output As #1
  • Print #1, "Sales report"
  • Print #1, "Goat smiles sales: " & sSmileSales
  • Print #1, "Goat cuteness sales: " & sCutenessSales
  • Print #1, "Total sales: " & sTotalSales
  • Close #1
  • ' Pattern: variables to cells, for total sales.
  • Cells(8, 2) = sTotalSales

The processing code is one new line:

  • ' Processing.
  • ' Compute total sales.
  • ' Need: sSmileSales, sCutenessSales
  • ' Provide: sTotalSales
  • sTotalSales = sSmileSales + sCutenessSales

The input is:

  • ' Input.
  • ' Provide: sSmileSales, sCutenessSales, tFileName
  • sSmileSales = Cells(6, 2)
  • sCutenessSales = Cells(7, 2)
  • tFileName = Cells(3, 3)

OK, try it out, click Run... there should be a new file... there it is!

Report

Hooray! It worked!

Ray
Ray

Thanks for showing us this, Georgina! Seriously, it helps seeing how you work.

There's a lot to learn from Georgina. After you've done some more exercises, or if you get stuck and frustrated, you might want to read through this lesson again.