Excel File Recovery

How to Standardize the Texts in Your Spreadsheet with Excel VBA

We’ve all seen spreadsheet names and addresses with a mix of formats like JAMES JONES, sARAH bERNARDT or clint eastwood, all of which look amateurish on a report. This article shows how to standardise text in the proper way in a worksheet. This article assumes the reader has the Developer ribbon displayed and is familiar with the VBA Editor. If not, please Google “Excel Developer Tab” or “Excel Code Window”. The Excel file can be found here In this exercise we’ll build a...

Read more »

How to Call a SQL Server Stored Procedure from Excel VBA

Data on a server can be modified by examining the records ‘client-side’ in Excel VBA, changing them as required, and saving them back to the server. A more efficient way of doing this, particularly if the database is at a remote location and there’s a lot of traffic involved, is to do the work ‘server-side’. This exercise calls a stored procedure from Excel to categorise employees into age-ranges according to their dates of birth (i.e. 18-25 years, 26-35 years, etc.), without a...

Read more »

How to Auto Adjust Combo Box or List based on Dynamic Data Ranges in Excel

Where data downloaded from a database exceeds a combo box’s range, the new items are simply not shown. To counter this, the range that underlies List or Combo boxes needs to expand or contract to match the data.  This article examines how to do this automatically.  It is assumed the reader has the Developer ribbon displayed and is familiar with the VBA Editor. If not, please Google “Excel Developer Tab” or “Excel Code Window”. A professional way of displaying combo boxes is to...

Read more »