Excel VBA Automation
Excel remains a very powerful business tool - one could argue that business would not be what it is today without Excel / spreadsheets in general. Yet most organisations invest vast sums of money in developing new systems in circumstances where a simple Excel solution would be far more appropriate.
Automated generation of web code
eMail marketing automation
Generating automated reports eg web based
Project management automation
Data / database / data list cleaning (very effective!)
Any repetitive task...
In the early days we had spreadsheet formulae, then the ability to record macros - now we have Visual Basic for Applications (VBA). It is a powerful development language in its own right and is perfectly suited to the task of automating simple business processes.
If you think that VBA can only handle a few basic programmatic concepts you would be mistaken - it is a powerful development language in its own right and is perfectly suited when a rapid solution is required.
To access the VBA development environment within Excel press Alt and F11. To start new procedure enter "sub test" then press enter. Excel should add "end sub" automatically. You can then copy and paste the following snippets, pressing the "Run" button that the top of the screen to run your test procedure. NB work on a blank worksheet.
VBA Code Examples
Loop through a sheet
Write to a text output file
Read from a text file
Select sheet / range / cell
Do something with a selection
Run code on opening the sheet
Get feedback - messagebox and inputbox
Write to a cell / read from a cell
Replace some text in a cell
Logic - it then else
Full VBA Examples
Here are a few code-snippets to show you some of the things that VBA can do for you:
This example shows looping and accessing an external text file (loading the entire textfile into a memory based array would have been more efficient)
- Open a text file containing a list of company names
- Find the column titled "company name"
- Find the next available free column also
- Hilite matches between the external list of company and the list in your spreadsheet
- Diplay a message when done
This example generates an html report containing contacts for a company / address.
A simple utility to group and outline eg similar company records
Glossary: excel automation, excel vba, excel visual basic for applications, high wycombe, neuro innovation, finance automation, financial automation