MS EXCELandVBA MACROPROGRAMMING:
The document is outdated and is subject to be updated with new projects
Introduction:
Thank you for your interest in my Excel skills inventory document. My goal here is to give a good insight regarding what I can do using Excel in a data analysis role at a large organization. What motivated me to create this document and put it online was that for each contract job interview I have to explain my Excel skills and when doing so depending on the audience, I feel that sometimes I can make the connection but there are times that I can not. I decided to have a document like this explaining my skills level in a greater detail that resume or even job interview may not sufficiently cover.
The items under the heading below “Sample Excel problems that I worked with…” areactually sampled specific problems that I worked on so far. I tried to show what I can do in Excel utilizing the power of VBA (Visual Basic for Applications). The following sectionon “Excel Functions and Formulas”does the same thing except they do not focus on VBA but instead it focuses on Excel’s own features that are accessible through the user interface.
They are from my personal compilation from various Excel projects that I have worked on in the industry. I have been working as a contractor for the past 4 years using Excel extensively to create spreadsheet based business solutions and worked before on several permanent positions also utilizing Excel heavily. In my graduate school I also had to leverage the power of Excel for my projects in statistics and finance. Most of the below stuffis accumulated over time project by project and some got here because of my personal interest like when trying to find something I came across with another interesting concept or code in the process or I was trying to help my own personal financial management.
For the most part when tackling with Excel VBA macro problem Iutilize various resources. Those are internet research using newsgroups, Google, MSDN (Microsoft Developers Network), user forums, Microsoft TechNet knowledgebase resources, Excel VBA programming books, InformIT Online computer books library, CDs, VBA language reference books.
Simply I do whatever it takes to find that code and then tweak it to get that code to work right for the problem at hand. First I determine what needs to be done in Excel. Before delving into the VBA side I need to convince myself that problem can not be solved byusing the available built in functions and features of Excel or using formula line logic within spreadsheet.
To create a VBA code within the VB Editor environment (VBE) of MS Excel as a second step I start my search for the right object, property or a method or a function or a sub procedure/routine then a finding/creating the code for it. Unless it is a simple enough automation that can be done with a macro recorder or a slight modification of it then the only solution is to use VBA code that can be stored under modules as macros.
I believe any person who works extensively with data within spreadsheet environment whether it is Excelcan easily relate to below problems.For each case the solution is in for the most part achieved via VBA code if the complexity exceeds the built-in capabilities of Excel.
Although I must admit that there are many ways of getting something done in Excel which is a wonderful thing. Excel also offers enormous capabilities even without touching VBA code through its own spreadsheet level formulas and built in functions. As I am not coming from hardcore programming and application development background my first inclination is to utilize the available functionalities of Excel to solve the problem only when that falls short then I explore my options with VBA.
Sample Excel Problems that I worked with utilizing VBA:
Identifying unique values in a range
Custom user defined functions
Dynamic ranges
Adding columns using VBA
Calling subroutine from a different file
Populating List boxes and Combo boxes on user forms
Advance Find and Replace
VLOOKUP on multiple columns
Changing cell colors based on the day of the week
Getting totals based on a cell color
Linking checkbox to a formula
VLOOKUP via VBA
Replace function (not under Find menu option)
Message Box prompt
VBA to retrieve data from Access into Excel
Filtering on text color
Outlook email from within Excel
Number of workdays between workdays excluding company specific holidays
Creating a range object
Macro that sets up an email
Using VBA to lock cells based on a value in another cell
Cell contents as Strings
Macro to move from cell to cell after entering data
Using input box to populate arguments in a formula
Changing cell values in macro
List Box and Combo Box
VLOOKUP with condition
VBA array formula
Loop function
Selecting next blank cell
Check boxes, command buttons and other controls
Functions and sub procedures
Event handling such as an active cell changes its color
Conditional copy of a cell
Delete interior color
Data source changes
Counting by range
Class Modules
Import large delimited file
Cell=textbox value
How to recognize string variable in an array
Printing out range names
Extracting Word info into Excel
Select current cell row
Data entry form
Several workbooks into one summary
Print range macro
Separating columns
Fill combo boxes
Run SQL statement in VBA not in MS Query
Prevent a cell from being selected
Combo box populating
Option buttons
Finding a blank cell in a column using VBA
Removing duplicates
Skipping blank lines
Changing query references
Summing visible cells only in a range
Counting visible cells in a range
Extracting the nth element from a string
Does the text match a pattern
Returning an acronym
A user defined functions for commission, discount or interest calculations
MS EXCEL FORMULAS and FUNCTIONS:
The below sample cases can be solved via writing logic in the formula line of MS Excel and incorporating the built-in functions available through the Excel user interface (menu items listed in the tool bar).As in the VBA cases these are also representing typical problems and scenarios that I had to deal with over time at various companies working as a contractor.
Sample Excel Problems that I worked with using Formulas and Functions:
Various mortgages,loan amortization tables, buy vs. lease scenarios, credit card debt management spreadsheetsusing the financial functions and applying formula line logic
Comparing two different data sets to see the newly added data using VLOOKUP
Populating formula based spreadsheets with dynamic ranges to get updated calculations from another worksheet or workbook or another data source such as MS Access.
Utilizing array functions for the below goals
Data validation
Summing the largest values only within a range
Computing the average without zero values
Determining if a particular value appears in a range
Counting the number of differences in two ranges
Finding the row of a value’s nth occurrence in a range
Determining if a range contains valid values
Recovering non-numeric characters from a string
Returning non-blank cells from a range
Returning a list of unique items in a range
Manipulating text strings to apply conditional logic based on a certain value in that string
Using MS Query to import and export data via ODBC in conjunction with other applications such as Crystal Reports, MS Access, Oracle, SQL Server or any application or data warehouse that support ODBC technology
Utilizing pivot tables and customizing them to present data in several dimensions for reporting purposes
Using conditional formatting
Creating views and using them in Macros for custom views for reporting goals
Creating named ranges and using them within spreadsheet based formulas
Using List function (this is available in Excel 2003)
Using XML Import/Export (this is available in Excel 2003)
This feature comes very handy when it comes to upload spreadsheets to web, to corporate intranet and making web service calls talking to other applications residing on the intranet
Data validation, ensuring that user data entry is error free either at spreadsheet level or using Forms
Advance Filter (Microsoft can improve this function to make it more user friendly)
Working with subtotals
Linking cells across the worksheets
Charts and Graphics
Financial Functions and Statistical functions of Add-Ins
Used statistical functions heavily such as simple and multiple regression models, curve fitting, hypothesis testing also financial functions such as Time Value of Money problems
What-If scenarios – Iterative approach to estimate the best possible solution for the goal to be reached
Working with mega formulas
Protecting the formula cells only on a spreadsheet but making other cells unprotected
How do I Rate My Excel Skills:
Any time I had a job interview either with a technical recruiter or with client one of the most typical questions are “On the scale of 1 to 10 how do you rate your Excel skills?”
My response to that question goes like this
Short answer
I was tested on my Excel skills numerous times on those standardized PC based Excel skills test applications. On the average my scores hover around the neighborhood of 90%. As far as the person asking this question I place myself I guess on 9 out of 10 for my Excel skill and that is very satisfactory for the most part. I understand that by using this test they gauge candidates and narrow down their options as anyone can claim expertise in Excel and even sincerely think they know a lot about it without even realizing what they are not even aware of. Most of the client needs fall into the category that can be handled without leveraging the true power of Excel. So it makes sense that they use this test but it certainly does not measure the programming abilities, and creativity of the power user in order to create highly complex spreadsheets nor it shows how the user tackles a certain problem for data manipulation. For more complex tasks such as creating a pricing decision tool in already complex setting like a trade room, or complex rate sheet development to support and automate shipment process typical Excel skills just do not cut it.
Long Answer
This 90% and this test itself do mean almost nothing to me. Person who is asking this question is most likely not even thinking about the vast capabilities of Excel and asking this question from the perspective of Admin functions in my terminology. So it even bothers me some as I see my capabilities is a bit better than that.
My long answer to that question would be like this
There are two dimensions to Excel one is the one that most people are familiar with, Excel’s user interface and famous spreadsheet, ok maybe some functions and formulas utilizing the formula line, even pivot tables and MS Query and there you go you have so called advance Excel skills. Even Advance Excel classes that they cover in technical colleges do not go any further than that.
The other dimension of Excel is wide open and if one does a little bit internet research it is possible to see the possibilities and capabilities are literally endless there. This dimension is literally Visual Basic for Applications programming and using Excel only as a platform. The same thing can be done by another application too except VBA is native code of Excel and can leverage the power of the Excel’s object model hence it is a very good platform. How do I rate myself here? Well you are now asking a very serious question, as there are people with years of programming and development experience, those who work on full blown application and tool development, who perform as software engineers, it would be very hard for me to compare to them and say I rate myself as 4 or 5 or 8 out of 10. So thisquestion should be rephrased or changed to capture all these possibilities.
Career Direction as a Data Analyst:
For all practical purposes for most of the Excel based work within the industry whether it is financial or operational or logistic work I believe I am a good source per se. My personal career goal in data analysis world is well exceeding what I have done so far with Excel. I eventually would like to develop enough expertise in spreadsheet based and database applications that I can provide tool based complete business solutions.
This would be complex application or tool development for a particular business need. I envision doing this as a private consultant for clients. My work so far as a contractor gave me some exposure to various challenging projects. I value this experience a lot and believe it forms a base for my future career goals. I intend to keep at it for a while until I am better armed with more experience as nothing substitutes experience and then go out and offer a full array of data centric business solutions as a private consultant.
Needless to say as one looks at the industry trends it is easy to see the technological trend regarding the data analytic work. MS Office applications development is moving to .Net framework and VB.Net will eventually replace VBA. And the integration of Office and Visual Studio will bring lot more encompassing capabilities. Also data analysis and reports generation capabilities combined with XML based web technologies is adding yet another powerful choice and this is Web based business solutions. Trying to constantly keeping yourself up-to-date in this area is a serious challenge and the feeling is you are always behind hopefully not terribly but never the less this is my professional growth area and where my direction is headed.