What is VBA used for other than obvious use to create Macros or automate repetitive calculations on Excel?
VBA or Visual Basic for Applications is the programming language of not only Excel but also for all other Office programs. VBA is like power that can help you conquer the world of Business and Academia. Let em quote some concrete examples. I recently worked on developing a module on outlook which could read incoming mails, look for a certain subject line and if it matches, opens the attachment, consolidates all the data on share-point, and sends a reply to the sender of the information thanking for the data. On the other side, the consolidated data from multiple sources was used by another excel to perform analysis, update charts and put them on a presentation. Let me provide another example: A Word and Excel based application wherein excel performs financial analysis and best on the best and worst case scenario a contact is generated in word with appropriate wording and clauses and saved as pdf. A manufacturing company wanted to examine what happens to their product during shipment and obtained a huge set of data from an accelerometer placed along with the product being shipped. Now this data was used to create a 3D representation and modelling of data on excel to demonstrate what happens to the product during shipping. Time stamps ar major movements were obtained from this simulation and cross verified with GPS data to know what happened and how the effects of these can be mitigated. With all the above examples the major takeaway is that capability and use of VBA extends far and wide beyond automation of repetitive calculations. You can make several applications interact with each other automate, innovate, estimate and in short give life to your imagination. Now that is a true power in Business and Academic world, waiting for a worth match in terms of requirements.
Lets assume that a person is in a top position of a fin-tech organisation and needs to close a deal with bank. Now how can this person make the best use of Microsoft office?
I will extend the assumption that this person has nothing but Microsoft office at disposal for task in hand. I would first start with communication. The bank needs to contact or to be contacted. There will be exchange of requirements, discussion of deadlines, exchange of files that would be needed and all this is possible with Outlook. Now that there are requirements, there will be a need for estimation of finances and analysis to finalise the deal. Now this is with Excel. There can be several simulations, scenario analysis etc.. which can be performed and saved under several versions within different files or one file. Once these numbers are crunched, there might be a need for a contract. Now this contract should be looking professional with company logos, no errors with grammar and spelling, with appropriate formatting, headers and footers. This is achievable with Word. Now if this person needs to store the data with as many details as possible to have a benchmark for future contracts there is access to create and maintain database. With time there are more collaborative tools on the cloud that are available and Microsoft office provides all those advantages and working on cloud. This was just a representative example. Imagine what an advance user can actually achieve!
What are some best practices when handling complex models or tasks in Excel?
Microsoft Excel is a tool that has utility way beyond simple calculations. Even without programming skills, it is the best tool available int he market for creating mathematical models, performing analysis, financial management, statistical analysis etc.. It is obvious that with increasing enormity and complexity of data, there are high chances of being lost on the way. Here are some ways to avoid those traps: 1. Feel Free to use multiple worksheets: Let data dumps be separated from a summary of analysis or presentation of final data. Complex calculations mean nothing if presentation or final conveying of data fails to provide right messaging. This is not only helpful from a presentation standpoint but also to follow how one ends up with such a conclusion starting from raw data. Apart from raw data, assumptions can be in one worksheet, workings or calculations cab be separated and commented and final results or summary can be separated. 2. Using references: Avoid repeating data and use absolute or relative references instead. One sheet with data with good granularity can form a basis on which all other parts of the workbook is built. This can be referred to multiple times throughout the workbook. If a range is being referred to quite often, you might also consider creating a named range which further simplifies formulas and sometimes helps understand methodology or structure of analysis. 3. Naming/ This is one part that is often overlooked in files that are commonly shared. Having right headers or renaming worksheets might really help navigating through complex sheets. 4. Hiding/Unhiding: If there are sheets that are used for information to look up or for references to build the sheet and if this information is not really important for the audience receiving the file, it may be better to hide the file in the background to remove clutter. With these it is definitely possible to perform even the most complex tasks by breaking them in to parts and tackling one item at a time.