I’m trying to study for my Excel course and I need some help to understand this question.
Problem 1: Super Coder [20 points]
You’ve just joined a new business analytics department and your first assignment involves analyzing an Excel VBA program written by a former employee of the company. This employee, who liked people to call him “super coder”, wrote a small Excel VBA program that did something with customer account data. The file you’ve inherited is named FlagOverdueAccts-BigMess.xlsm and it contains a subroutine called procedure1(). The file itself was named by the annoyed employee who inherited the totally uncommented code.
Part 1.1 – Cleaning and Commenting
Thoroughly comment the code so that anyone with basic VBA experience will know exactly what is going on in the program. Fix up the indentation and add appropriate blank lines to make the subroutine more readable.
You’ll notice that Range(“A3”) appears a few times. Modify code to avoid hard coding in the cell reference “A3”).
Several of the variables have “bad variable names” – rename them to something more appropriate. In particular, here are the Dim statements for some of the poorly named variables:
Dim cell1 As Range
Dim cell2 As Range
Dim n As Integer
Dim dollars As Currency
Fix the button captions. Rename the procedure1() and procedure2() Subs with more appropriate names
Part 1.2 – Program Enhancements
Modify the first procedure so that:
- The results range is sorted in descending order by amount due.
- Finish procedure2() so that it clears out the results as intended. Obviously, it needs to work no matter how many or how few rows are in the results table. Rename procedure2 to something more appropriate.
Problem 2: Break Even [20 points]
Finish up the BreakEven-W12-Problem.xlsm problem we started in class. See BreakEven-W12-Demo.xlsm for how it should look and work. Both files are available in the Downloads-Module05-VBA.zip file. There are also Screencasts on Moodle that walk you through the solution (yes, the answers are in the videos). This problem is designed to introduce you to basic range object referencing and manipulation. To get 15/20, you must get the “basic version” working correctly (described in the program comments) and to get 20/20 you need to get the “above and beyond version” working correctly. That’s the version that the Demo file illustrates and includes the message when the break even point is detected and the Goal Seek part.
Problem 3: User defined functions for log snooping [20 points]
I’ve provided you with a file called access_log.txt. It’s an Apache server log. For those of you don’t know, Apache is the primary server software underlying the internet. Learn more at http://httpd.apache.org/. You can read about the format of Apache log files at http://httpd.apache.org/docs/1.3/logs.html#common.
In this problem, you are taking on the role of a security analyst who is checking into some possible irregular traffic hitting your servers.
Browse the file to get sense of structure
- IP address (or domain name)
- date time
- HTTP command (GET, POST, HEAD, …)
- return code (2xx is good, 3xx is redirect, 4xx is error, 5xx is ?)
- data size returned
Part 3.1 – Manually import log file into Excel
Import the access_log.txt file into Excel. Import it as a TAB DELIMITED file so that each record gets imported into Column A in your spreadsheet. Here’s a screenshot of the top of my Excel file:
Save your file as apache_log_functions.xlsm (yes, as a macro-enabled workbook). You’ll be doing all the work for this problem in this file and you’ll be turning it in as part of the assignment.
Part 3.2 – Creating a function to extract date and time from a log file line
Let’s assume you want to be able to quickly filter lines by dates or times of the log line. You’ll see there is a datetime stamp right after a left bracket – “[“. To make this easy you are going to create a simple VBA function that returns the datetime value for a single line passed into the function. Then you could use this function in a worksheet formula in some column to the right of your data. Each row containing the string of interest will end up with a datetime value and then you could Autofilter the whole range and just see the records of interest. For lines which don’t contain dates, we’ll have our function return a zero.
This function will be called GetApacheDateTime. It will take one input arguments. The first is a string representing an Apache log line. To help you out, here’s a code skeleton. I STRONGLY SUGGEST YOU USE THIS CODE SKELETON.
Remember, you can and should debug and test your user defined function using the Immediate Window, or the spreadsheet itself, or a “tester Sub”. See the Variables and Procedure Basics screencast which covers creating, using and testing user defined functions.
IMPORTANT: Remember, functions take input arguments and return values. As you can see from the screenshot above, the first input argument is a string. So, your function should NOT reference anything else in the workbook or worksheet other than the argument passed in. That’s the whole point of functions. Also, as you can see from the function declaration, your function will simply return an Excel datetime value. It will NOT explicitly modify the spreadsheet. The spreadsheet will get modified when you use the function in a cell. A user defined function is just like any other Excel function.
Once you have it working, use it in formulas in Column B and then filter so that only those records from March 9, 2004, are shown. Here’s what the answer will look like.
Hacker Extra – Grabbing the HTTP command
While you are pretty excited about your GetApacheDateTime() function, you decide you’d like to also have a function that returns the HTTP command – e.g. GET or POST or whatever, along with the URL. For example, from a line that looks like this:
220.127.116.11 – – [09/Mar/2004:01:48:28 -0800] “GET /favicon.ico HTTP/1.1” 200 1078
you’d like your function to return
as a string. Notice we do NOT want the HTTP/1.1. We just want the HTTP command and the URL. We’ll call this function GetApacheHTTPCommand(). Just like the previous function, it takes a string representing the log line as its only input. As in the previous part, if you get this working, use it in Column C to show me that it works. Here’s my output.
Since this is a Hacker Extra, no screenshot. But, here’s a few hints:
- Obviously the HTTP command along with the URL appears between the double quotes.
- There’s a space between the HTTP command and the URL.
- The URL ends with the last character before a space.