EXCEL-VBA Application Development Strategy

As an EXCEL-VBA based BI Consultant, I faced many situation where the requirements are legacy system. There is no ready made, built-in solution available. Hence the Business Analyst and System Analyst have to find out a solution on their own. As a third part consultant, I too was asked any suitable strategy. With the experiences I gained during those many brain storming sessions, I learned many feasible, practical solution strategy. I would like to share them with you!

1. Simple Switch Board

We can have the first sheet as Main Menu. (Recently User Interface specialists call this as SWITCHBOARD). The main menu can be

• set of carefully arranged command buttons
• Custom menu (menu added to EXCEL menu)
• Custom tool bar (new toolbar added to existing toolbars)

2. Data Entry

Data Entry can be achieved with the help of UserForm or specially designed excel worksheet.

3. Data Source

The data source can be anything including

• text file
• another worksheet
• user data entry
• ORACLE, SQL Server, mySql (ODBC)

4. Output

The output of your EXCEL-VBA application can be

• another workbook
• chart
• email
• word document
• text file

I will continue other aspects of EXCEL-VBA Application Development Strategy in next blog.

We will be benefited from sharing of our knowledge. So please feel free to share your valuable knowledge with me by your comments/email etc.

Cheers!

Contact me @ karthiganesh@yahoo.com