If you are new to workbooks with macros here is a short 2 min video on the quick setup needed to work with them on your PC’s Excel and some basic points in which to be aware.
For those new to workbooks with macros Read More »
I don’t often re-post, but hard not to with this really good post from Kevin Roper of the Society of Actuaries on Excel VBA speed and efficiency Well worth the read. https://www.soa.org/news-and-publications/newsletters/compact/2012/january/com-2012-iss42/excel-vba-speed-and-efficiency/
Great article on VBA efficiency Read More »
This is another handy personal macro I use in my day to day spreadsheeting and application building. It sits in my Add-In which has it’s own tab on my custom ribbon, but you might just use the ribbon QAT or a personal.xlsb file for these kind of personal efficiency macros. I did a post
Personal Macros – Locked & Unlocked cells Read More »
Following on from my recent post of Fix duplicated Conditional Formatting Rules , I mentioned Debra Dalgaleish’s website page had some great VBA to fix duplicated Conditional Formatting rules. I tweeked this code just a bit so that all you have to do is select anywhere in any Table in the active worksheet and it
Macro to fix Duplicated Conditional Formatting rules in Tables Read More »
I did this very thing today, so thought I would share this tip with you as it saved me a lot of time. Say you wanted to have this formula go into column 8 of the range you created in VBA for the number of dynamic rows you extracted using Advanced Filter. =CHOOSE( OR(C6={“EL”,”IL”})*1+(C6=”MT”)*2+OR(C6={“IP”,”EP”})*3+(C6=”SC”)*4+(C6=”AD”)*5, “Labour”,”Materials”,”Plant”,”SubContract”,”Overheads”)
Use the macro recorder for VBA code for a relative formula Read More »
As might know there is no autocomplete functionality or any other features such as different font sizes with good old Data Validation cell dropdowns. On a client’s project I worked on today we really needed autocomplete capability. The dropdown list was 80 rows long and the values were messy to scroll through some with 3
Autocomplete Workaround for Validation Dropdowns Read More »
My last blog was on accessing personal macros from my custom ribbon. This blog is about where to store these personal macros. These personal macros are the ones that save you time and tedious work. There are basically two ways to go. Some save them to their Personal.xlsb file that loads on opening Excel. Others
Personal Macros – Personal.xlsb or Add-In.xlam? Read More »
I recently worked on a project that had three distinct and large datasets for different geographical areas in three workbooks. Starting with the first workbook and data set in a Table in its own worksheet, I created a worksheet of seven Pivot Tables to provide seven Pivot Charts needed in their own sheet. This could
VBA to Change Pivot Table Source Read More »
Anyone who has used VBA to create formulas in cells knows it can be a little tedious and tricky to get the syntax right for Worksheet formulas. Tricky because string quotes need to be doubled for VBA and this is tedious because unlike a worksheet formula which gives you instant feedback, entering via VBA can
Tip for creating syntax in VBA for Worksheet formulas Read More »