Excel VBA Programs and IT Requirements

I am a big believer in understanding the impact of actions and decisions as related to corporate IT requirements for any software programming or automation, including Excel VBA Programming. The article entitled, “How the Capabilities of an Excel Programmer Can Help Your Business” at ExcelVBAWizard opens with the exclamation:

If you’re an Excel super-user who has finally outgrown a few of those Excel built-in worksheet abilities, and you’re wishing that they could accomplish even more, consider hiring someone with the capabilities of an Excel programmer to take your spreadsheets to the next level! Just tell a programmer what worksheet functions and results that you want, and you’ll be able to sit back and relax as they do the rest of the work! They will take it from there. They will also create an Excel add-in that you can install in Excel, and that you can distribute to others if you want. You can even sell it!

Don’t get me wrong, I LOVE Excel, and use it almost daily. But I also believe that you must use the right tool for the job, and in a corporate setting, it may be not only inappropriate to use Excel as the platform to support your business process, but it may be costly to the business, and may even be illegal.

Customer Requirements, Business Requirements, IT Requirements

As with any project, one of the more difficult tasks lies with you, the Business Unit client, in providing the details of what you want to accomplish from a business process perspective. Your customer has requirements for the information they need from you and the business process used to support them. For example, your customer know the information he needs as “Invoice” You, as a business unit, must translate the customer requirements into terms that you use within your business process, for example, External Customer Billing Document.

Those business process requirements are in terms that the people associated with your business function know and use daily, but are not in a language that an IT programmer would understand. The business process requirements must then be translated into IT requirements, meaning an IT professional who knows both the business lingo and IT semantics must then convert those business requirements into IT requirements. The IT programmer then translates those IT requirements into Specifications, and Code.

Business People Define Business Requirements

The mistake is frequently made when the business representative says, “I want you to use function XYZ to do ABC”, when in fact the business person needs to tell the IT consultant, “I need to have a program that solves this business problem, shortens the time it takes, and eliminates the possibility of errors”.  IT can then properly design the program so that it meets IT requirements, is portable and upgradeable, and is included in any infrastructure upgrade plans.

The IT Consultant Translates Business Requirements to IT Requirements and Specifications

It is the IT consultant who is responsible for writing the IT requirements and specifications so that the IT analyst/designer/programmer (this can be one person, or a whole team for a larger project) understands what the system or program is to accomplish. IT needs to establish what capabilities can be automated, on what platform, within what budget constraints, and include all the security, supportability, documentation, training, and data integrity aspects of the project.

Real Life in IT

Of course, you can just tell the programmer to do what you want, but the likelihood of having that program meet the IT requirements is small. I have seen this thousands of times:

  • The business user dictates what they want to a free-lance programmer who then creates a one-off program we will call Program Alpha that can be used by one workgroup.
  • Another workgroup sees the program, but needs slight changes to be able to use it for their specific business function, so another version of Program Alpha now becomes Program Beta, and so on.
  • Now, the environment is flooded with many different versions of Program Alpha, and by the way, the original programmer was dismissed after implementing the first project.
  • All of these programs were based on a version of baseline software we will call Infrastructure 1.0. Now, IT has to make changes to the systems, desktop images, network, or other something-or-other in the nebulous “IT Cloud” that was defined in the IT Strategy, and called Infrastructure 1.1.
  • The problem is that Program Alpha and all its offcast versions are not compatible with the IT requirements of Infrastructure 1.1, and do not work. IT does not know about this Infrastructure 1.0 dependency, because IT was not engaged in the requirements and specifications of the original Project Alpha, likewise, IT could not include Project Alpha iterations in the application and system tests conducted for Infrastructure 1.1.

Calls to 911

So, the day that Infrastructure 1.1 goes live, the IT  Help Desk gets flooded with many irate business people saying that their application, Project Alpha, etc. does not work. The Help Desk has no record of a Project Alpha in production, has no documentation on the product, has no programming resources that know about the requirements and development of Project Alpha, and has no budget to get these issues resolved. It is very likely that no backups were taken of Project Alpha, and the SQL code to query the corporate systems is no longer compatible with the new database version included in Infrastructure 1.1 IT requirements.

Finger Pointing, Mudslinging, Escalation

This business unit is obviously affected by the ability for Project Alpha to work in the new Infrastructure 1.1 environment. This now becomes an emergency for everyone involved:

  • The Business Unit  can no longer do their jobs
  • The Business Unit Management  must escalate this problem to IT Management
  • IT Management now forces a mad scramble in IT to find information about Project Alpha. No budget or resources were allocated to this project, nobody in IT knows anything about Project Alpha, and there is no documentation, backup, or database recovery specified.
  • All users must sustain a system outage because Infrastructure 1.1 must be rolled back to Infrastructure 1.0 configuration.
  • The Customers (for whom the Business Unit is using this data) are left without the necessary data to run THEIR business.
  • Regulatory  The use of mission critical data in spreadsheets must be controlled. Your company may be in violation of SEC and other regulatory statutes for not complying with the established IT security, data integrity, backup, and recovery criteria.

While this represents the worst case scenario, before putting mission critical data into a self-maintained system of any kind, you need to understand all of those “overhead” things and IT Requirements that make IT development so expensive and time consuming. You need to ask yourself the questions:

  • Can I live without this function?
  • What workarounds must I implement to do the same thing?
  • Can my customers live without the data provided by this function?
  • What is the cost to the business to lose these customers?
  • Will I be able to find a job elsewhere because I lost these customers?

Rich Moyer is Managing Partner (retired) of Spaho Consulting

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.