A blog update

Hello again to all Internet readers.

Sorry for the long delay and thanks for your patience.

My wife and I since last September 2012 welcomed a new baby.

Since then I have been very busy taking care of family commitments and other priorities as they arise.

Just taking a moment now to inform readers some changes I have decided to make for my blogging experience manageable going forward.

Firstly, I am currently reviewing my website and be posting new pages/information monthly. Don't be surprised if some pages are shuffled about, this is part of the review. Also I have dropped the numbering of blog names and adapted a casual approach of writing a blog. Soon I'll discuss a book about VBA.

Secondly, I am looking into exploring some software development practices I have heard lately of enabling VBA code be stored into a repository known as Github. A repository is basically like a database to holds lines of VBA code. The benefits or "yellow hat" thinking is to track code changes made over time. Github has a service called "Gists". As I understand gists for now, provides a snapshot of code which can be shared with a hyperlink. To put this in another way, social media like Facebook, Youtube or Linkedin can share information about people, gists can share information about computer code; promoting discussion between users. The interesting thing I believe this website could use is to format computer code nicely with indentation and colours of syntax or reserved words making it easier to read/discuss.

Thirdly, I hold a vision that readers can learn more about test driven development in VBA which is one of the new emerging software development practices. This presents a challenge for me as I don't do much testing as I used to do however it is encouraged. I plan to present how to go about setting up such in VBA,  the means to do some unit tests and allow changes be committed into Github.

That's all I have for now,

Til then,
Peter.

Generating Goods over 12 months

This expressive blog shows the opportunity to see how VBA can translate the previous blog’s formula based approach using list of goods within a given financial year.

We introduce the readers to a fictional conversation of how Mick attempts to convince Jane, Shelly and Walter the benefits of using Excel VBA for business to simplify the practice.

Mick and shortly afterwards Walter arrived in the shop / warehouse late in one morning. Mick gave a short hello wave to Shelly who is currently taking a telephone business order request for goods. Shelly motioned Mick and Walter to come inside during her phone conversations.

Walter directed Mick that he will arrive shortly after checking the staff regarding some matters. Mick approached behind the receptionist’s counter towards the main office in which Mick finds Jane reading the local newspaper at his tidied desk against one wall during his coffee break before starting his daily work. Mick noticed that Joe is not at his desk against the opposite wall and his office space is messy loaded with business papers and scribbled notes.

Mick made a brief knock at the open door frame to announce his presence which Jane welcomed him again and suggests in helping him set up a folded table that is used as business meeting table in the middle of the office. Walter came in momently to prepare his part of the table with his usual monthly business meeting.
Jane started the meeting by addressing Mick in indicating Joe will out of his routine traffic jam during peak traffic on his way back so the hand over and monthly catch up meeting with Walter will get started. “so ah, thank you Mick for helping out collecting our suppliers details and presenting the sheet in a way that helps Walter see the strengths of which one is more expensive so quickly that I could have counted them. I may be not good with numbers as Walter does however I have a good eye on some deals for the business. So what ideas you bring to our table today, Mick”

“Thanks for the feedback on that Jane, I have several points to offer today. Firstly, I’ll put on my white hat to begin with about facts on goods list. I have observed that your sales last financial year which contains several boxes are being too full with some left overs.”

“Yes, I overcome this by having a loose bag to compensate buying more crates than we need. “ Jane explained.

“Oh, okay” reflected Mick “then if I switch onto my green hat and discuss alternatives in practice, do you find it easier to know which of the two produces being Fruits or Vegetables are the strongest for the month?”
“Ah, Walter” queried Jane “any ideas?”

“At present, no we are normally not interested in knowing this for a couple of reasons. The cash receipts we received daily tell us which products sold the most. The books recorded tell us that business has been relatively steady of about plus or minus three percent for the financial year.”

Mick looked a bit disappointed from Walter’s response. “Perhaps I’ll put this what I mean this way, using a blue hat which reflects on my thinking about goods list sales. We want to get an overview how Fruits and Vegetables performed during the financial year easily, does this help Walter?” He slightly nodded at the idea.

“Granted! “continued Mick “Excel is good for collecting and storing information quickly. I was able to extract the information from Shelly’s notes and generated a report that summarise these outcomes. Here let me show you.” Mick did a demonstration of the macro after Jane grabbed Shelly after a phone call.

After the demonstration, Jane seems impressed with the graph showing the differences what he realised that Mick may be up to something good over Walter's methods at this moment.

“One last thing before I go, Jane. Using a black hat of caution with the interpretation of results, I am starting to see where you are running short of money when Walter asks the bank to pay for goods as Fruits or Vegetables prices are higher last month than normal. Here are some suggestions…”

So Mick is now offering good sound advice based on the extra information before Walter had a chance to say something.

How to do this using VBA?
There are a number of components involved that we will need to cover. The process consists of creating a new workbook to contain the data, generating the 12 months of the financial year, populating the data with random orders of items and display a graph on a summary sheet.

How to create a new workbook?
To create a new workbook, we need to have an object that is declared as type Workbook as follows.


In this case, discussing in a top-down manner, Excel being the Application, we invoke the methods for workbooks, containing a behaviour named Add. This tells Excel to create a new workbook into memory. As there is an assignment with the Set keyword, we are passing this information about the object creation into memory to wbGoods which is referenced several times at later stages.
How to generate sheets for the 12 months of the financial year?
The code for this is as follows


The code is arranged to process the current workbook using the With statement. During this object selection, I have an inner loop to create all the sheets ahead of time before an additional inner loop is made rename the sheet tabs according to the list of Month’s array in sequence.
How to populate the data?
The code for this is as follows

The code does not show the array data used for sake of brevity; however the complete code is available for download. We initialise several variables to help us track which of the two factors that may be the largest in the month. The data contains columns of data, so the loop counter needs to skip each row (a set of 5 fields) before the next.
While the loop is processing each row, we are populating each field in turn, hence populating different cells according to type of field we need. Once the entire data is populated, we add at the end, the count of fruits/vegetables and total cost from each.

Formula calculation
The formula calculations involved at VBA level is as follows.
After generating the random number for required order which is the incoming orders, the calculation for Required Crates is after checking that is that required orders exceeds the maximum of the crates, we divide the orders by the maximum of crates. Should the required order is less than the crates; we correct the division error by using 1 in its place.

How do I make the Charts?
The code for this is as follows
Once the summary sheet is crated and selected, we do another loop examining the totals of each month populating the Fruits and Vegetables tally counters and costs involved. In addition, we review which of these two categories’ are dominate, then the chart is crated. I have chosen to the use the code in part from his book Excel 2007 VBA Programmer's Reference (2007) John Green et al, page 186 to help me clear any chart objects before creating the chart itself.

Next blog will discuss how orders of goods received from customers.
Til then,
Peter.

Random Suppliers over Financial Months in VBA.

This blog continues with Suppliers for the same VBA class and will expand the results to spread over the given financial year. As we know the financial year is from July to June each year and also we have an average of 30 days per month.

Let us further suppose that Mick has found out that the daily results have overall helped the business and received a request to find out is the business is improving month by month.

Mick then updated into Excel the additional requirements.

Story ID	Title	As a…[Role]	I want to…[Feature]	So that…[Benefit]
2	Supplier	Business Owner	See the list of supplier’s expenses over the financial year. July to June.	I can determine which supplier to continue trading.

And the acceptance criteria, Hint: $2,000 per day times the average 30 days equals $60,000

Story ID	Scenario Title	Given… [Context]	When… [Event]	Then… [Outcome]
2	High monthly cost of the Supplier	The supplier monthly cost	the supplier monthly cost is calculated exceeds $150,000	Flagged too costly and continues traded with extreme caution
	Low monthly cost of the supplier	The supplier monthly cost	The supplier monthly cost is calculated between $60,000 and up to $150,000	Flagged too cheap and continues traded with caution
	Average monthly cost of the supplier	The supplier monthly cost	The supplier monthly cost is calculated under $60,000	Flagged as cheaper and continues traded as caution.

The suggested solution for this exercise is use “Random Supplier in VBA” as a base and make additional changes to the range of random values and allow another loop to cycle through months.

The Supplier class cSupplier will not require the Rank so we remove this from the class and introduce Excel’s average formula to create an average across the months.

In Excel 2007 or above, I have set the conditional formatting to show the rank by different colours. The colours are Green for below the average, Grey around the average and Red if above the average.

Next blog will discuss how to create a random list of goods for a given supplier.

Til then, Peter.

Fictional Business - Random Supplier in VBA

This blog begins by making a feature list about J&J's Groceries, focusing on Suppliers for the VBA class.

Let us suppose that Mick, the IT guy who is gaining some work experience was asked to see if there are some ways of improving the business using Excel. Mick commenced by introducing Jane and Joe the six thinking hats in order for Mick to establish many common ways of discussing business from different points of view. Mick used extensively at present the white hat thinking of facts and figures about the way suppliers are organised and managed.

The feature list contains user stories and acceptance criteria to define what is “done”. This framework is derived from Behaviour Driven Development which is found on Wikipedia (2001-present) "Behaviour Driven Development" [accessed 13 May 2012].

Mick then wrote into Excel as a list of feature that incorporates the key words below as shown in the table headings.

Story ID	Title	As a…[Role]	I want to…[Feature]	So that…[Benefit]
1	Supplier	Business Owner	See the list of supplier total cost of the day	I can determine which supplier for J&J Grocers continue orders for tomorrow

And the acceptance criteria follows this format structure.

Story ID	Scenario Title	Given… [Context]	When… [Event]	Then… [Outcome]
1	High Total cost of the Supplier	The supplier total cost for today’s order	the supplier total cost is calculated exceeds $5,000	Flagged too costly and traded with extreme caution
	Low total cost of the supplier	The supplier total cost for today’s order	The supplier total cost is calculated between $2,000 and up to $5,000	Flagged too cheap and traded with caution
	Average total cost of the supplier	The supplier total cost for today’s order	The supplier total cost is calculated under $2,000	Flagged as cheaper and traded as caution.

The solution for this exercise is to create classes namely cSupplier with few methods (behaviour) and a macro procedure SupplierExample to execute the code via two buttons. The first button is to clear all the fields. The second button allows the VBA class to run again easily.

The Supplier class cSupplier contains these private variables Name, Amount and Rank.
Supplier class has a method to create a random amount and provide output of the rank given the amount randomized.

Next blog will discuss how to spread the days over a period of one financial year, know in business circles "Year to Date" or YTD for short.

Til then,
Peter.