Excel VBA Programming in Toronto

2015-09-21
Share:
column_chart3How do you make the most of Excel?  The answer is…  use VBA!!!  “Visual Basic for Applications” is the programming language built into all of the Microsoft Office applications.  VBA allows you to automate and extend the functionality which comes native to each of these products alone.  In Excel’s case, it allows you to:

Create Custom Functions

Excel provides a wealth of functions, however, each business is different and many have unique requirements. With VBA you can write a function, that can be written in the formula bar for any cell selected. With custom functions you can create a simple function that might replace a number of nested functions.

Validate User Input

Excel provides you with the “Data Form” which can be used for entering or searching for records. This form is a step in the right direction, but it does not allow you to validate the data, to ensure that correct values are entered, or to simplify data entry with tools such as drop-down lists. Data validation and data entry tools can be added to custom forms which are built within the VBA editor.

Create PivotTables

If the data within a worksheet is not structured properly, then no matter what you do, the PivotTable will not look or operate the way you want it to. There is a solution to this. The data can be restructured and written to a new worksheet and the PivotTable created from this new worksheet. The new worksheet can be hidden and the procedure automated so it works seamlessly with the click of a button. Also the data source used for the PivotTable can be changed on the fly with some simple tools added to the page, so that one PivotTable can be created to serve multiple purposes.

Customize Charts

Charts are the best way to “spruce up” a report, grab a reader’s attention, or instantly communicate the status of an operation. A chart does not need to be static, but can be totally interactive and allow the user to change the information displayed by clicking on controls such as option buttons or drop-down lists. One chart can be used to display sales for all regions, or just one region at a time. The information if dated can be displayed over different periods and can show daily, weekly, monthly, or yearly production or sales.

Print Reports

Many consider reports as an essential tool for managing a company, and the data needed for a report can be contained in several Excel worksheets, and possibly several workbooks. A procedure which can gather this data, store it, use it to create a report, and then print the report is an invaluable time saver. It is even more valuable if it can be structured so that it finds the most current data every time, and be run periodically (weekly, monthly, quarterly etc.).

Import and Export Data

Excel is a good family member and works well with the other microsoft office products. Data can be exported directly, or via a csv or text file, and then imported into word or access. Similarly, data can be imported either directly from access or from another excel file. This allows you to use access`s reporting tools to format the data currently residing in an excel workbook, or to send company address information from excel to word for printing letters or labels.

Datacon’s Role

At Datacon we take pride in building user-friendly software. Our goal is to make it as easy as possible for you to capture the data that you need and to display it in a way that is most meaningful to you. We will sit down with you and look at how you are currently doing things and will make suggestions on how we might be able to improve upon what you already have in place. We will provide you with a written quote once we have agreed on the scope of the project and we will work out a timeline that is doable for both parties. Why not contact us today, and let us consult with you to determine how we might help. Why not take the first step, and make “Datacon” your data connection.

See contact information in footer our use the following form:

Your Name (required)

Your Email (required)

Subject

Your Message