No Announcement posts

Excel VBA Examples

Here are some examples of client problems I solved with Excel, VBA and Macros.
If you’re in need of help with Excel, you can contact me here. Thanks Eric

 

Client: Proquest.
Problem: Update the sales quoting sheet so that it works better and looks better.
Before the sheet looked too much like Excel and was not user friendly …

After, the sheet has a proper user interface, the looks good and uses VBA code to create proper quotes.
The VBA code processed quotes with advanced pricing models that increased sales.

Client: Ford Motor Company.
Problem: Help the engineers with a better user interface so they could focus on what’s important. And not get distracted with all the data.
What you see below, is are a number of interfaces in Excel, to help people see what’s important.
This shows a menu system to take engineers to the correct tab (upper left), a diagram to help new engineers see vehicle loading rules (upper right),
and a push button interface to calculate the weight loading of a van based on the number of seats (lower).

Client: UnitedHealth.
Problem: With over 5,000 calculations, this large sheet could generate rounding errors.
Solution: Use VBA to run the same calculations and check when rounding errors were significant.

Client: Small business Client.
Problem: Wanted to show how stock data might be displayed it in their app.
Solution: Use Excel to get data from the web, and display it in mock-up of a laptop and tablet app.

Client: Krup Engineering.
Problem: Client needed a database in Excel. Normally a task for Microsoft Access.
Solution: Used VBA to access a hidden tab (sheet) that accessed the data with an unlimited number of rows.
Each row in indexed in the first column (far left, black and yellow striped).
This index allowed for fast lookups, and data could be deleted by removing the indexed row.

Client: KitchenWorks.
Problem: Client wanted a way to format cells, by color, so that people would see when contractors were used.
Excel’s standard Conditional Formatting left the client with too few options.
Solution: Use VBA code to allow a full range of options based on the drop down.


Client: Baughman Tile.
Problem: Client ships pipe but the cost of shipping is based on the weight AND size of the load.
Smaller 30″ diameter pipe can fit within 42″ diameter pipe, thus saving space and shipping
charges. Other Excel experts tried to solve the problem of packing smaller pipes into larger
to quote customers shipping costs. They used complex if-then statements that failed.
Solution: Use a VBA loop to figure out how may pipes could fit inside each other, then
calculate the shipping load based on the size reduction.

Client: You.
Problem: Some Excel expert creates a worksheet for you, using VBA code. And when
you want to update it, the code is unreadable by you or the next developer.
Solution: All the VBA code I develop is modular and easy to understand for other developers.

You’ll notice the code below has six smaller sub routines (each one is called from a button
in the application).
You’ll also notice that each of the smaller routines does not do very much. They each call the top routine.
The top routine does all the work. Any developer can see that my making changes to the main routine,
all six of the smaller routines will benefit. This is called code reuse, and it results in less code, and
code that is easy to maintain.

For help with Excel, please contact me, thanks Eric.