Nick's blog

Nick's picture

XLA routines: EE_Table

If you use Excel as a database, EE_Table will return the range object of your data.
Nick's picture

XLA routines: EE_DeleteTempSheets

EE_DeleteTempSheets is used when your routine creates temp sheets that need to be deleted at the end of the routine.
Nick's picture

XLA routines: EE_RearrangeSheetsAlphabetic

EE_RearrangeSheetsAlphabetic is useful to use at the end of your procedure when you have created sheets all over the place and want them rearraged alphabetically.
Nick's picture

XLA Routines: EE_ReplaceSheet

EE_ReplaceSheet is a useful routine that replaces a sheet, and does not error if the sheet does not exist meaning you can use the routine to create a new sheet.
Function EE_ReplaceSheet(strSheet As String) As Worksheet
    Dim wksNew              As Worksheet
    Dim wbk                 As Workbook
    Dim blnDisplayAlerts    As Boolean
 
    Set wbk = ActiveWorkbook
 
    On Error Resume Next
        Set wksNew = wbk.Worksheets.Add(after:=wbk.Worksheets(strSheet))
    Err.Clear: On Error GoTo 0: On Error GoTo -1
 
    If wksNew Is Nothing Then
        Set wksNe
Nick's picture

XLA Routines: EE_DeleteSheets

Following on from EE_DeleteSheet, here's a routine that deletes multiple sheets
Sub EE_DeleteSheets(ArrayOrRange)
    Dim blnDisplayAlerts    As Boolean
    Dim arr
    Dim wbk                 As Workbook
'http://excelexperts.com/xla-routines-eedeletesheets
    
    Set wbk = ActiveWorkbook
 
    Select Case TypeName(ArrayOrRange)
        Case "Variant()", "String"
            arr = ArrayOrRange
        Case "Range"
            arr = Application.Transpose(ArrayOrRange.SpecialCells(xlCellTypeConstants))
Nick's picture

XLA routines: EE_DeleteSheet

Deleting a sheet using VBA is more work than it should be.
Nick's picture

XLA routines: EE_End

At the end of your code, it's good practise to turn events back on, enable alerts, and put calculation back to where it was. Here's the code to turn them on:
Public lngCalc As Long
 
Sub EE_End()
'http://excelexperts.com/xla-routines-eeend
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.Calculation = lngCalc
End Sub
See here to turn them off at the start of your code: EE_Start
Nick's picture

XLA routines: EE_Start

At the start of your code, it's good practise to turn off events, disable alerts, and put calculation on manual. At the end of your routine, turn them back on. Here's the code to turn them off:
Public lngCalc As Long
 
Sub EE_Start()
'http://excelexperts.com/xla-routines-eestart

    Application.EnableEvents = False
    Application.DisplayAlerts = False
    lngCalc = Application.Calculation
    Application.Calculation = xlCalculationManual
End Sub
See here to turn them back on: EE_End
Nick's picture

Stamp Duty Changes From Autumn Budget 2014

Stamp Duty Changes

Nick's picture

Impact Of Labour's Mansion Tax

 

Mansion Tax Analysis in Excel - Labour's nationalisation of London homes

 

Today, we'll use Excel to look at the impact of Labour's Proposal for Mansion Tax for properties over 2 million,and demonstrate some interesting findings.

The Labour party is proposing a tax on properties over 2 million. They label this a "Mansion Tax". The idea is that wealth is redistributed from the "Mansion" owners, and used to fund the government's overspending.

Nick's picture

Comparing Data sets

An excellent way to compare data sets with the same column headings is to list them under each other, and add a new column called "source", and populate that source.

Then create a pivot table, and add source as the column field.

Then you can easily spot differences. Here's an example for comparing Trade PVs

Nick's picture

Excel's Power BI

Hi all

This is worth a watch.. it's exactly what Excel needed - a powerful BI tool.. In terms of functionality, there's nothing that cannot already be done, but I liked the ability to merge identical CSVs from a directory.

https://www.youtube.com/watch?v=War1pSs2LAM

Nick

Nick's picture

How to become an Excel MVP

 

So you want to become a Microsoft Excel MVP ?

Well, there's no exact formula to this journey but I might be able to offer a few tips on things that will help your case. Becoming a Microsoft Excel MVP is about being an expert in Excel, volunteering your expertise, and being known for your contribution. You cannot apply for this award, and you need simply to be recognised.

Here are a few things that might help to get you noticed:

 

Dos:

Nick's picture

Q3-2012 - ExcelExperts.com Update

 

Hi all

ExcelExperts continues to go from strength to strength thanks to our fantastic volunteers.

Top Volunteers at the moment are mannyArgyronet and almir 

Nick's picture

Excel Experts XLA

Hi all

We are writing an XLA for Excel VBA developers... Please let me know if you can recommend something to be added.

Nick

Nick's picture

Dos and Don'ts Of Spreadsheet Design

 

 

It occurred to me that don't have a section on the Dos and Don'ts Of Spreadsheet Design... I think the best way to do this is to start you all off, and you can add your own pet loves / hates

Do   Separate inputs from outputs

Do   Use Data validation on input cells
       - make it as restrictive as possible
       - this reduces the chance of bad data

Nick's picture

33. VBA Tips - Output Array without looping.

A common approach to outputting an array is to loop through each element of the array, and write each element to an Excel cell.

This methodology however is very inefficient. If you have a big array, you will really notice that performance is awful, especially if calculation is on automatic.

Here's a code sample that picks up the values of a range, and writes them back:

Nick's picture

Feb-2010 ExcelExperts.com Update

January had a slow start as everyone was on holiday, but the new year picked up quickly, and everyone came out wanting new spreadsheet systems.

Most encouraging this month has been the huge increase in total visitors up almost 40% on last month. Can we sustain this increase ?

Well... if we continue to offer good content, don't spam people, and bring Excel and VBA jobs to people who need them, of course we can !

Going forward, we are particularly interested in large complex projects. If you know of any, please refer ExcelExperts.com.

Nick's picture

98. Excel Tips - Sort Columns

Excel Tips - Sort Columns (Excel 2007)



We all know how to sort rows, but did you also know that you can sort columns ?

Here's a screen shot of our data in Excel:

sort-columns

And here's what happens when we sort columns:

Nick's picture

97. Excel Tips - Camera Tool



Excel Tips - Camera Tool (Excel 2007)

  • This is an excellent time saving tip to get screen shots from Excel into outlook
  • Use Camera Tool

I have the Camera Tool added to my custom toolbar. See this tip for more info on how to customise your toolbar.

Once you have the camera tool on your toolbar:

Nick's picture

What can't be done in Excel ?



What can't be done in Excel ?

When I ask customers why they're not using Excel for more things, they often say to me: "Well, we'd like to do XXXX, but you can't do that in Excel can you ?"

Often, the answer is: "YES YOU CAN !"

So my question to all of you, is this:  What do you think can't be done in Excel ?

Nick

Nick's picture

96. Excel Tips - Keyboard Shortcut To Open Excel

Keyboard Shortcut To Open Excel


There's no actual pre-defined Keyboard Shortcut To Open Excel, but you can set your own one up.

Here's how:

Go:  Start => All Programs => Microsoft Office

Then Right click on the Excel entry, and select Properties

Nick's picture

Jan-2010 NEW Excel VBA Jobs section



Excel VBA Jobs section

 

ExcelExperts.com is pleased to announce a new section for jobs.

Recruiters looking for Excel / VBA experts will be posting jobs here.

Excel / VBA Recruiters:

Nick's picture

95. Excel Tips - Cause Of Big Excel Files



Cause Of Big Excel Files

 

We've all been there happily developing our spreadsheet, and all of a sudden, the file size balloons.

Q: How did that happen ??!!

A: Most likely, it's uneven formatting that's causing the problem

 

Nick's picture

32. VBA Tips - Turn Off Autofilter

Turn Off Autofilter

Turning off autofilter using VBA is easy and quick, but you need to know how it's done.

Here's some code to toggle the autofilter on and off:

Nick's picture

Jan-2010 ExcelExperts.com Update

Happy new year !

This year should be an exciting one with ExcelExperts.com making a big push for business.

The first large scale system is in production at a canadian company. They are very pleased so far, and have requested 2 more systems to compliment their sales operation.

We have taken on board feedback that the site was confusing and cluttered, and have changed the layout to be more intuitive.

Nick's picture

31. VBA Tips - Convert Text To Number VBA

Convert Text To a Number using VBA

 - use: EVALUATE()

Here's a step through demo sub routine:

Nick's picture

Dec-2009 ExcelExperts.com Update

Hi all

This month has been very busy with ExcelExperts.com officially opening the doors to customers.

We have been swamped by requests for quotes, and it seems people realise that Excel is an increasingly popular business tool.

If you are a business using Excel, we guarantee that we can save you time and money with a little investment from you. Tell us your business processes, and we can advise on and implement changes.

We are looking for Excel Experts to join the team.

Nick's picture

30. VBA Tips - Fast Search



Do you have a large amount of data held in an Excel Spreadsheet ?

  • If so, you will be well aware how difficult it is to find things

This tip demonstrates a way using VBA that enables you to search through your data really quickly, and helps you to find what you are looking for.

What I have written is a demo I used for searching through flight details.

Nick's picture

ExcelExperts.com is HIRING !

Yep, that's right, ExcelExperts.com is looking for Excel Experts to get paid for Excel consultancy.

...but this is no ordinary interview.

To apply for this job, you need to prove the following:

  1. You must be an Excel Expert, or be smart enough to learn quickly.
  2. You need excellent communication skills. (Written more important than verbal)
  3. You have to prove that you have the tenacity to see problems through to completion.

How do you prove this ?

Syndicate content