Year of 2016 and beyond.

Hi all,

I thank you all readers for keeping in touch with me and my blog via various social media sources and search engines sites.

This is encouraging news.

Just to post that this will be my last post on Google's blogspot site as I will be offine to focus my energies on activities related to developing VBA and integration using Github.

Once I have passed several hurdles I will post a link here to let readers know I welcome feedback from these endeavours.

*EDIT* - Just a short new updated message here to say that I'm back online however you can find me working away with RubberduckVBA on Github, https://github.com/rubberduck-vba/Rubberduck. 

This open-source project aims to provide make our Visual Basic for Application editor flexible to adapt the emerging modern programming concepts such as Refactoring and Unit Test-driven programming.

See you all there and cheers,
Peter.

Xmas of 2014

Just a small note to readers and viewers alike that although my blog has been quiet for several months I am been tending to several projects and family matters at present and will be resume blogging in the new year.

From peterlearningabout blog, I wish you all a happy and safe holidays for the festive season and a hopeful happy new year.

Kind regards,

Peter.

Resources Tab added

Hi there readers,

Just been tidying up my blog in the background and felt to include a resource tab to assist readers finding all the information and articles I have mentioned to date.

Let me know if there items of interest you wished me to review and I will schedule a moment to include it.

Til then and kind regards,
Peter.

Where will I want to develop from here now?

Hello again readers, many thanks for your patience.

I have taken some unplanned time off since last year for family commitments and now ready to continue writing additional content for the blog.

This blog will discuss some ideas I have had time to myself of wanting to move forward as a little developer in a big world with a vision that I hoped will benefit others doing the same things while enjoying having a go at learning something different.

Previously mentioned I would review the Jenkins software however during my long break I discovered readers were interested in my blog about the software development process. With further reflection, I feel here would be the time to show what my approach would look like and hopefully be encouraged how all may come together.

Here is a little diagram I wrote within Excel without gridlines that contains the main steps involved which further blogs will tackle over time.

In principle, from the top left hand corner of my diagram I will have done some work on the Excel files and the main workbook. Those saved changes or modifications will be managed under a new tool I will be learning by Microsoft called Powershell to script the means of taking a quick visual snapshot of my work in progress, and detecting which files I made in the directory is changed in turn develops a list for Git at the commit to repository phrase automatically.

Once the changes are made directly into Git, I can optionally script which files I may want to share onto another feature of Git called Gists. Gists is like the Microsoft cut/paste clipboard if you like that enables owners to quote computer code safely within Blogger and promote discussion where needed.

After processing files into Git, I will have the script to make a copy of these latest changes into another area for testing so that Jerkins (an integrated code manager) can run a script of tests. The aim of this testing area is to develop a form of unit and integration testing by means of proving that certain features work correctly as intended.

If all goes well or not!, I will love to get email to myself the outcome of the test results which I will have a good record of changes made as time goes by. However, as with tests chances are more likely to make further modifications again and so the cycle or evolves as often as needed to introduce new features over time.

So in conclusion, I will hope the additional lessons I need here to fulfil my vision will helps other developers some inspiration and ideas for their projects.

Til then,
Peter.

VBA software development workflow for beginners using Git

Following on from my previous blog, now that we have Dropbox to hold our files together sorted. The next step is to allow changes made to these files into Git to track my changes made over time during the software development cycle.

So what is Git? A software (cloud based) solution to hold a snapshot of your file changed made over time and also to allow other people to create a branch from your code changes if you choose to.

In context, a practical and well known version control software. Git is an active member of the Software Freedom Conservancy which is a non for profit organization that promotes open source projects under the Creative Common License. Here is their free ebook from Git as background reading.

Let's get started...

I'm highly recommending our newcomers to have a go using their interactive web lessons learning from scratch what and to know how their Git "shell commands" are.

Download from Github their Windows / Mac / Linux platform of software onto your desktop, following their directions from the article to help set up your identity for Git and name your initial repository then your all set to go.

I prefer to use Git Shell than the graphical interface as this allows my fingers type away on the keyboard to ask Git to act on files I'm ready to import into my Git repository.

So, once I click on Git Shell icon which opens up the black dialog box. As indicated from my previous blog page. I have set up a directory for groceries from Dropbox. This is shown as a mapped drive letter Y:\ as my suggestion to save time locating the directory where ever you set up on your PC.

Y:\>cd groceries

The next thing to do is to initialize the directory for Git.

Y:\groceries>git init

Git will create some hidden files along the lines of "Initialized empty Git repository in Y:/groceries/.git".

A good programming habit to develop here is to always check the current status of what Git has before continuing.

Y:\groceries>git status

This shows a message that master is the first branch of changes and no other commits available (if shared with other people as they will have their own branches to update with your master)

# On branch master
#
# Initial commit
#
nothing to commit (create/copy files and use "git add" to track)

I will have placed a copy (using explorer onto this location) of my Excel file "groceries.xlsm" which basically acts a template going forward. I need to add this onto a staging area so Git has something to work with before committing my changes onto Github web page. While typing in "git add groce" you can use the TAB key to speed up typing the command to save time as the Git shell will recognise you wanted to complete the rest quickly.

Y:\groceries>git add groceries.xlsm
Y:\groceries>git add README.md

also need to ensure there is a README.md file. Readme file allows people on your Github have an idea what the repository is all about which we can update all sorts of useful information as we go along. "md" is short for markdown which I provide a link here that expands this concept better than I could.

Now I am ready to commit into Git.

Y:\groceries>git commit -m "J&J Groceries and first template"

then several message will display the result of committing your changes with the master. All this is doing at the moment is sitting on the "development side" of the equation (on Dropbox). To put this on the "production side" (to the cloud like Git) so to speak, we need to push the changes over to Github. Using the Github identity you have made eariler, you will have a secure web page address (with https://) which you can push your changes onto your web page. I have already set mine, the interactive web page provides a good example for you to do one.

Y:\groceries>git push

however when you examine my own Github page. I have made some prior commits as a test to check things are looking good. Now to see what changes I have actually made, you can visit my Github website, click on groceries.xlsm and select view raw to download yourself a copy to see what's happening.

Please let me know if there are issues arises attempt to find and see the groceries.xlsm file.

Next, let see if I could get a software like Jerkins to pick up my any changes directly from Github to do its own testing separately of what I made in production works.

Til then,
Peter.   

VBA software development workflow for beginners using Dropbox

This blog gives everyone the latest update from me on software development I want to adapt going forward.

I'll begin by talking about software development as I know it to date, followed by how I would go about it using Dropbox to manage my work files in progress, using Git to store my Excel changes accordingly and I hope later using Jerkins to see how the changes I made can be impacted and registered to do some other kind of things.

Background

Talking in general about what software development is. In essence it is the practice of both, the discipline and the art of creating effective software. Taking the traditional path, known by various names as the waterfall approach, from the late sixties to the nineties, stages of collecting requirements ("what do you want the software to do") before designing the software ("how are we to write software"), also constructing the software before testing the software to see if it works. Release is the last stage to provide software out the to customer.

In contrast, in the twenties, Manifesto for Agile Software Development [WWW Document], n.d. URL http://agilemanifesto.org/ (accessed 20/07/13) is a website showing the dedication of software developers who were invited over a few days to examine software development challenges. The manifesto is the conclusion whereby they valued items listed on the left more than topics indicated on the right.

Agile methodology following the manifesto's spirit is a style of delivering software often to/for the customer using a small group of mixed skilled people that iterate (repeat) smaller cycles called sprints. Before the cycle starts there is an an agreed prioritized list of stories (requirements) which the allocated team will estimate how long it will take to deliver and once agreed the team will use the sprint to produce/deliver the results for review and feedback.

I have been reading from Scott Sehlhorst who has a software consulting and project management website about the company of the same name "TynerBlain LLC". For a while I enjoy reading his tips/ideas on how to address software development issues from a product's point of view. One of his article he quoted and mentioned from a guest post Wayne Mulligan for "On Product Management" who discusses the challenges in implementing Agile over traditional software development processes. I find this an interesting contribution along the lines of how Agile works.

On the same note, how would Agile work on a personal level? If software contains features and these features are ranked in order of importance to be done. Personal life-goal's vision can be managed this way into three categories, to do, doing and done. I have come across an excellent ebook on Agile developed by an Agile coach whom applied the principles to her own family. Ronen-Harel, S., Kovatch, D., Kaye, A., 2011. Agile Kids, 1 edition. ed. ButterKnife Marketing.

For all sense and purposes, I'll will be using some parts of agile for my own software development. Trello by Fog Creek software is a great product I find on the web for flexibility to manage both the vision of this blog and projects.

Below are the tools I've elected to build our Excel application further which readers is welcome to use.

Tools

Let's start with Dropbox first. I choose Dropbox as a secure and reliable means to save files/photo snaps and store my development work over the internet/cloud. In other words, I can show some development by login onto the internet anywhere that suits me in order to share my work in progress. The good news it is free for use with initial space once you register your details. Perhaps later once you decide to share the directories or files, dropbox rewards you with additional space. Interesting way to spread the word so to speak. :-)

To set up Dropbox for development work, we need to download the latest version for Windows desktop to help manage files within Window. I also respect Mac users for Excel so I'm sure Dropbox does supports Macs desktop tools as well. Macs may have something similar as their setup, I'm happy to hear any feedback on this note.

This download also helps to set up a connection across the Internet using best practice protection encryption and synchronises the Dropbox account with your changes. Here is an picture which may looks something like this in the tool bar and the green tick on the box to say all changes are updated.

Once Dropbox is installed. I recommend associating a drive letter of your choice to the Dropbox directory. This will improve your programming workflow by switching easily to the drive letter to continue working.

The following table shows the steps needed to complete for using the Windows command dialog box
(to open the command dialog box go to Start->Search field->type in "cmd") or via Windows Explorer for those using the easy to use mouse. We are going to use "groceries" as the project name and I will assume you are located on the root directory of your Dropbox or switched by drive letter of your choice mapped to that drive letter.

Instructions / Steps	In Command Box	In Window Explorer
Create directory called “groceries”	mkdir groceries	File->New->Folder->type in groceries

This is a very simple set up. I'll talk about the next blog about where and how the files are managed before imported into Git to store my code changes.

Til then,
Peter.

Book Review : Excel Programming with VBA Starter by Robert Martin

This blog will focus on a book review that I was invited to join in helping out in doing a technical review with publisher Packt Pub (2010-present).

Robert Martin (2012) "Excel Programming with VBA Starter" [accessed 10 June 2013] has done a good job of laying out a pathway  for beginners to pick up the pace of doing Excel formulas into becoming a VBA programmer.

The book cover these broad topics into sections.

The first section discusses what is Excel's macro and shows possibilities to simplify complex using the power of macros. Introduces the reader the macro environment known as the Visual Basic Editor (VBE) and how would the reader go about in finding out where to create their first macro.

Once the reader develops confidence of creating macros with the macro recorder and learning the "words" of the macro language so to speak. This macro language are described as objects. Objects are represented to perform specific actions as formulas do in calculation or to alter the way information is displayed or presented like changing colours of cells in a spreadsheet.

Next sections covers modules which are a grouping the similar tasks and/or macros. The book discusses how to create modules and functions that returns information after working out a complex maths formula. Variables are mentioned which are often used by programmers to track how many times certain information has been processed. Variables do differ according to their type so the author has done well to expand the reader understanding of what type of variables are used for many things.

The same section assists understanding that there many types of tasks as there are variables which macros can be used to perform on Excel's data. To provide an example, the reader will appreciate how to follow down a large list of financial data in a sheet using a loop that examines the totals with a calculation of a discount in the next cell.

Lastly, the understanding of what objects is and how they affect with each other is expanded to help readers learn more about complex topics of VBA such as Classes and Enumeration.

The book contains good examples to guide the reader through and offers a comprehensive list of website links for readers to seek further on their own art of programming.

Til then,
Peter.

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.

List of random goods from the supplier

This blog progresses onto another topic for the fictional business regarding the Suppliers goods.

We are going to develop a list of goods of fruit and vegetables. The orders are deliberately random generated so demand gives us an idea of the expenses we may pay those goods.

What is the source of the Goods list ?
I have found a good Australian website of Nicole Avery (2011-present) "Planning with Kids" [accessed 03 June 2012] to list the fruit and vegetables which you can see this and record the list into Excel.

I have assumed the maximum capacity for each item on a wooden box which is indicated as a column, how much each box would cost, how many items we need to ask our supplier to satisfy demand and how many boxes in total to check our shipment.

What the cost of Crates?
I researched on the internet what price may be reasonable for wooden crates and settled on $60 per box. Sounds pricey for many businesses as it serves the purpose for this model of costs per demand.

If anyone has some suggestions to show me how wooden crates are meant to be priced, leave a comment and I will update costing assumption in later blogs.

How did you calculate the number of boxes required?
I used a mathematical formula in Excel to see how many boxes as in quantity.

The formula is expressed as =IF(INT(F5/D5)<1,1,(INT(F5/D5)))

I expand the meaning of the Excel formula as follows:-
The first part of the formula is =IF(INT(F5/D5)<1,1,(INT(F5/D5)))

Let F5 mean the “Required amount from the supplier”. To put this in another way, this means how many goods we need once all the orders have been added up for the day before we distribute the goods tomorrow to our customers.

Let D5 mean Total Wooden Box capacity. I used my imagination to see what would be the practical maximum amount of goods could fit in a wooden box for certain items.

We then divide the Required amount from the supplier from the total capacity to see how many boxes is required.

As an example, let F5 = 325 and let D5 = 70 thus 325 / 70 is 4.6 boxes. I use the Excels function called INT for INTEGER to strip any decimal points as this exercise isn’t necessary.

So the 4.6 boxes become 4 with any loose or extras are placed on top of the last box when shipped to the warehouse.

The second part of the formula is =IF(INT(F5/D5)<1,1,(INT(F5/D5)))

There is a logic decision in place to check that we have no errors with the division. As the general assumption is, we cannot divide any number by zero as this is a mathematical impossibility.

Also, if any division returns a lower or small result like 0.46 for an example, we ask Excel to place at least a minimum of 1 wooden box regardless.

Next blog will discuss how to spread the cost of Goods over the financial year as I had done for Suppliers.
Til then,
Peter.