This is a list of the sorts of things that can be done using VBA as far as Office 2016 is concerned. I’m now on Office 365, so there may be slight differences in coding techniques for some of these things: I’ll update this note as I find them.
This is certainly not an exhaustive list and it only contains things I know I can do (because I’ve done them, either for myself, for my employer’s benefit or for someone else). I’m fully aware there’s lots more I’ve hardly touched on – I think it’s safe to assume that if you can imagine it, someone’s already done it before and the solution is out there just waiting to be re-cycled.
Access/Excel
Menu-driven forms-based front-ends; open file dialog boxes and select folders or files; interact with disk filing system; output selected data in CSV or PDF format; drop-down boxes to select data items including dates; animated objects in forms.
- Load Web pages and ‘scrape’ data off them; create Web pages in HTML or PHP; interact with remote PHP scripts and MySQL databases; update Web content automatically; connect to remote SQL/Oracle/dBase servers; navigate Web sites programmatically including entering data in forms, clicking buttons, downloading files, etc.
- Read contents of Outlook mail folders (including attachments), tasks and calendar; use data from a database or workbook to create new tasks or appointments; create and send mail via Outlook as HTML or plain text including address book lookups, multiple To/Cc/Bcc, attachments, preview before sending, etc
- Export data to new or existing Word documents; read/write binary and text files.
- Open databases or workbooks using Windows task scheduler to execute timed tasks; unattended automated updating of databases and workbooks.
Access
- Create Excel workbooks; transfer data to new or existing worksheets within Excel workbooks; read data from Excel workbooks; invoke Excel functions; merge and append PDF files; turnkey production runs of large or complex documents including data from external sources (Excel workbooks, Web pages, etc).
- Read object names and attributes; play WAV/AVI/etc files in a form; use FTP to interact with a remote server.
Excel
- Process data programmatically including filtering, sorting and creating/deleting worksheets; transfer data to Access databases; read data from Access databases.
- Create and configure command buttons and shape objects; create/modify/delete cell comments; use cell comments to hold additional data (date/time cell modified, instructions, explanatory notes, other textual information, etc).
- Monitor or log when people open, modify or save workbooks; track/control changes by date/time, userid, old/new data; lock cells programmatically (with/without delay).
- Customised functions to return results or perform complex manipulations of data, including referring to external sources (Access databases, Web pages, etc).
- Create timed events to run asynchronously to main worksheet (scheduled saves, interactions with Web sites, checking folders for files to be imported, etc).
- Generate email(s) from Excel/Access with alert/exception information, including attachment(s), data extracted from workbook/database, etc
Outlook
- Detect incoming mail, check for attachments and decide whether to save them to disk automatically.
- Process mail items programmatically, copying data out of body of email or attachments into new or existing Excel workbooks or Access databases.
- Generate email(s) from Excel/Access with alert/exception information, including attachment(s), data extracted from workbook/database, etc.
Word
- Process contents of documents programmatically including creating or deleting objects (e.g. pictures, shapes, tables, etc), modifying and reformatting text, etc.
- Use Word forms to collect data, process data into Excel/Access.
General
- Archive files and folders to ZIP format.
- Obtain information on running processes and kill unwanted ones.
- Obtain information on the current environment (logon id, domain, PC number, home directory, operating system).
- Shell out to execute external tasks including DOS programs and proceed or wait as required.