Home » Blogs

Nick's blog

Nick's picture

XLA routines: EE_GetLastPopulatedCell

Submitted by Nick on 25 January, 2015 - 11:17
Finds the last populated cell on a worksheet, or an empty cell representing the max row and max col. 
Function EE_GetLastPopulatedCell(Optional wks As Worksheet) As Range
'-          Works how specialcells (lastCell)  SHOULD work
'-          Returns single cell range
    Dim lngCol      As Long
    Dim lngMaxRow   As Long
    Dim lngRow      As Long
    Dim lngMaxCol   As Long
 
'http://excelexperts.com/xla-routines-eeGetLastPopulatedCell for updates on this function

    If wks Is Nothing Then
        Set wks = ActiveSheet
    End If
 
    If wks.UsedRange.Rows
»
Nick's picture

XLA routines: EE_Table

Submitted by Nick on 25 January, 2015 - 11:15
If you use Excel as a database, EE_Table will return the range object of your data.
»
Nick's picture

XLA routines: EE_DeleteTempSheets

Submitted by Nick on 25 January, 2015 - 11:11
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

Submitted by Nick on 25 January, 2015 - 11:08
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

Submitted by Nick on 25 January, 2015 - 11:02
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

Submitted by Nick on 25 January, 2015 - 10:57
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

Submitted by Nick on 25 January, 2015 - 10:54
Deleting a sheet using VBA is more work than it should be.
»
Nick's picture

XLA routines: EE_End

Submitted by Nick on 25 January, 2015 - 10:49
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

Submitted by Nick on 25 January, 2015 - 10:41
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

Submitted by Nick on 4 December, 2014 - 10:48

Stamp Duty Changes

»
Nick's picture

Impact Of Labour's Mansion Tax

Submitted by Nick on 2 October, 2014 - 13:28

 

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

Submitted by Nick on 28 March, 2014 - 09:51

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

Submitted by Nick on 6 February, 2014 - 15:34

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

Submitted by Nick on 30 November, 2012 - 22:03

 

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

Submitted by Nick on 11 November, 2012 - 22:11

 

Hi all

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

Top Volunteers at the moment are manny, Argyronet and almir 

»
Nick's picture

Excel Experts XLA

Submitted by Nick on 20 September, 2012 - 20:03

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

Submitted by Nick on 14 June, 2012 - 19:57

 

 

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.

Submitted by Nick on 11 May, 2011 - 13:04

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

Submitted by Nick on 3 February, 2010 - 09:42

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

Submitted by Nick on 2 February, 2010 - 16:12

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

Submitted by Nick on 26 January, 2010 - 11:36



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 ?

Submitted by Nick on 25 January, 2010 - 10:48



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

Submitted by Nick on 18 January, 2010 - 10:01

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

Submitted by Nick on 15 January, 2010 - 14:36



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

Submitted by Nick on 14 January, 2010 - 16:03



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

Submitted by Nick on 4 January, 2010 - 14:43

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

Submitted by Nick on 4 January, 2010 - 12:20

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

Submitted by Nick on 30 December, 2009 - 10:39

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

Submitted by Nick on 12 December, 2009 - 13:53

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

Submitted by Nick on 12 December, 2009 - 11:39



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.

»
Syndicate content