VBA in Excel.

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, …)
Filename/URL
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:

195.246.13.119 – – [09/Mar/2004:01:48:28 -0800] “GET /favicon.ico HTTP/1.1” 200 1078

you’d like your function to return

GET /favicon.ico

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.

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Benefits of our college essay writing service

  • 80+ disciplines

    Buy an essay in any subject you find difficult—we’ll have a specialist in it ready

  • 4-hour deadlines

    Ask for help with your most urgent short tasks—we can complete them in 4 hours!

  • Free revision

    Get your paper revised for free if it doesn’t meet your instructions.

  • 24/7 support

    Contact us anytime if you need help with your essay

  • Custom formatting

    APA, MLA, Chicago—we can use any formatting style you need.

  • Plagiarism check

    Get a paper that’s fully original and checked for plagiarism

What the numbers say?

  • 527
    writers active
  • 9.5 out of 10
    current average quality score
  • 98.40%
    of orders delivered on time
error: