RDetermine ODBC Drivers Using VBA and PowerShell
Ever wanted to determine which OBDC drivers are installed on a PC, maybe SQL Server specific OBDC drivers? Find out how easy it can be done via VBA.
VBA
Create Table in SQL Server using Excel Template
In this tip we look at an easy way to build DDL for SQL Server tables using an Excel template to generate the T-SQL for the table structure.
Excel
Excel Automation Explained: VBA Code + Sample Workbooks
Definition: Excel automation will streamline repetitive tasks such as updating data, formatting cells, sending emails, and even uploading files to SharePoint. Excel automation commonly uses custom-coded scripts written in VBA to complete tasks.
When using native Excel functions inside of VBA, use Application. Instead of Application.WorksheetFunction. The application will not throw any run-time errors.
You can use the underscore character to continue a VBA statement on a new line. This will make your code easier to read. In the sample workbook we provide, the email function uses this method.
Use WITH to shorten your VBA code. This is commonly used only to type a range once, then use WITH to do multiple things with the range.
Use the locals window in VBA to keep track and view the data inside variables. This is especially helpful for viewing arrays. You just need to add a breakpoint in your code. Highlight a line, then click F9. Then when the code runs, it will stop there, and all variables above that line will show in the locals window.
Excel VBA - VBA Best Practices
ALWAYS Use Option Explicit, Work with Arrays, Not With Ranges, Switch off properties during macro execution, Use VB constants when available, Avoid using SELECT or ACTIVATE, Always define and set references to all Workbooks and Sheets, Use descriptive variable naming, Document Your Work, Error Handling, Never Assume The Worksheet, Avoid using ActiveCell or ActiveSheet in Excel, WorksheetFunction object executes faster than a UDF equivalent, Avoid re-purposing the names of Properties or Methods as your variables
Actuarial Models in Excel
Actuarial models one of the most common usages of Excel to build complex mathematical models and test them under various conditions.
Actuarial Science