Convert Entire Excel Workbook to Values

• March 12, 2019
• Marcus Small

Convert an entire workbook from formulas to numbers instantly.

Dependent and Non Dependent Comboboxes

• August 24, 2016
• Marcus Small

Cascading combo boxes have been done online and I had a problem recently which knocked me for 6 as we say here in Australia.  Anyone who deals in cascading combo boxes will know only too well that if you are using a list and the Indirect formula to manage the combo boxes then the process can get very unruly very quickly.  One of the issues with comboboxes is that they are set up to work in sequence.  Combo box 1 feeds into combobox 2 which feeds combo box 3.  The combo boxes need to be filled out in that order.

Excel Change Print Area with VBA

• May 12, 2016
• Marcus Small

Setting the print area on a moving range can be a difficult and frustrating thing to trap.  You may wish to trap a specific range with your print area or have the print range only consider certain columns.  Let's say you have a couple of helper columns in an Excel workbook and don't want these columns to be printed but you want the columns to be visible.  You could manually set the print area to exclude the print area but now you have more data which you have added and you would like the print area to update in kind.

VBA Check if Sheet Exists

• February 21, 2016
• Marcus Small

I have a solution for checking if an Excel worksheet exists or not.  This can come in handy if you are creating a new sheet based on  a dataset.  You check for the new sheet and if the sheet exists you can alter the old sheet if the sheet does not exist then you can create a new sheet.

If Not Evaluate("ISREF('" & [A1] & "'!A1)") Then

Where Excel cell [A1] contains the name of the sheet you are checking for.

Now if this procedure was part of a loop you may wish to check all of the cells from say A1 to the last used row in column A.  Then the following might be more relevant.

Copy and Paste an Excel Chart into PowerPoint with VBA

• December 22, 2015
• Marcus Small

Creating Powerpoint presentations automatically is handy if you want the power to control another office application.  In this post I will copy a chart in Excel and paste it into a fresh instance of Power Point.  I will show two examples, once where the data will be pasted into a presentation with a title and one without.   This is a building blocks approach, creating a procedure which pastes one chart might seem like time better spent done manually but if there were 20 charts it would be faster for VBA to do the work for you. 

Import Website Data to Excel

• October 15, 2015
• Marcus Small

I was recently asked to repair a webquery which had gone bad.  The company controlling the website had changed the layout of the content.  I decided to extract the data from the website into excel using VBA and a call on the HTML tables inside of the website itself.  Obviously this technique works best where there are dedicated tables on a website.  It does not discriminate as all data from the page is imported but with some smart manipulation you can inject some VBA to extract the relevant tables into a fresh worksheet.

Excel VBA to Sort an Array

• February 23, 2015
• Marcus Small

I was recently researching the sorting of an array.  I sat in my reading chair and read through John Walkenback’s excellent Power Programming tome on the subject and did some research online when I came across the brilliant .Net feature System Collections ArrayList.  What a find it was as this feature allows you to sort an array and send that array right back into Excel.

Excel Merge Sheets to Master

• February 23, 2015
• Marcus Small

This article draws on the post from the site which is dedicated to merging worksheets in a workbook into a single master sheet Consolidate Sheets.  This technique can be significantly simplified with a VBA macro.   I have posted this type of reply on forums dozens of times.  Howeve this reply differs just slightly.  The following will consolidate data so the data tables appear side by side in the consolidaiton tab.  So if Sheet 1 appears: