Advanced Excel VBA programming

Years of Excel VBA programming and finally we can say that our Excel based solutions are almost perfect. We know Excel. We know VBA. We understand your business needs. We built our own code library and use it for all common tasks. You will pay only for building something special and useful for your business, not for writing VBA code for common functions.

The most common techniques we use in almost every solution:

SQL

We run SQL queries from VBA code to work with data stored in Excel, CSV or Access files, MySQL or Microsoft SQL databases. That is how our Excel solutions works very fast even with a large amount of data (up to 1 million of rows per Excel sheet). That is how we can extract filtered specific data into Excel even without opening source files in Excel. That is how we can easily and fast join data from multiple source, search and filter specific records, build complex reports etc. That is how clients who used to work with Excel as reporting application can still use Excel even if other developers says it’s not possible with so large amount of data.

Example: you have a large list of products in Excel and need to be able to find a record by product name. You can use built in “Find” function. But we will use custom form with SQL engine. Our search form is really interactive. While you still entering product name character by character our macro will refresh a list of matching products.

Ribbon tabs and buttons

We use custom ribbon tabs (the strip of buttons and icons located above the work area in Excel)  to add new buttons (toggle buttons, radio buttons, drop down lists etc.), so it’s easy to work with the application and it looks great. Buttons can be arranged into multiple tabs and groups, buttons labels and buttons availability can dynamically change depends of the file/sheet/range selected below or depends of the value calculated.

2015-02-09_19-04-45[1]

 

Progress Bars

For any operation which takes more then just a moment to complete we use progress bar (graphical control element used to visualize the progression of an operation). This is how you always will know how long it can take to complete an operation and will be sure that an application is not frozen. And if required you can cancel the operation in one click.

2015-02-09_19-10-40[1]

 

Advanced Error Handling and Logging

We use advanced error handling and logging in our VBA solutions.  It can include clear error messages for end user, detailed logging on custom LOG tab etc.

2015-02-09_19-25-57[1]

And if it’s really complex solution used by multiple users in multiple remote locations we use Centralized Error Handling and Logging System which include cloud based web service and MySQL database to store all logs. This is how we can fix bugs even before end user contacted the support team.

error-handler

Multiple Threading

For slow operations like web scraping or complex calculations we use VBScript to enable multiple threading (which is not available in VBA). This is how the solution can work many times faster.

Regular Expressions

To validate data, to remove or extract specific sub-strings from any text (i.e. remove HTML tags) we use regular expressions.