Excel VBA : ActiveCell not interpreted correctly when pasting into range

Question

I have written a VBA function to return the column letter of the current cell. The first example below accepts a column number as an argument, and returns the associated column letter. (functions stripped down to bare minimum that reproduces problem).

Function ColumnName(colnum As Integer) As String
   ColumnName = Chr(colnum + 64)
End Function

When used in a formula with the current column number as an argument (as below), this function always returns the correct column letter; whether I copy-and-paste a cell values which uses this function into a single other destination cell, and when pasting into a range of destination cells:

=ColumnName(COLUMN())

For example if I enter the formula =ColumnName(COLUMN()) into cell A1 and then copy and paste A1 into cells B1 through F1 I get the result A, B, C, D, E, F (as excpected).

I then changed the function to procedurally determine the current cell from within the function rather than accepting an argument.

Function CurrColumnName() As String
   Dim colnum As Integer
   colnum = ActiveCell.Column
   CurrColumnName = Chr(colnum + 64)
End Function

If I enter the formula =CurrColumnName() into cell A1 and then copy A1 into B1, C1, D1 this second function also works correctly. But oddly, if I copy the formula from A1 and paste it into a range of destination cells all at once, it returns the same column letter for each destination cell.

For example if I enter the formula =CurrColumnName() into cell A1 and then copy A1 and paste that into cells B1 through F1 I get the result A, B, B, B, B, B.

It seems as though the COLUMN() argument in the first example is being interpreted for each destation cell, but the activeCell.Column method of the second example is being interpreted just once for all destination cells, not once for each destination cell.

Is there a difference between "active cell" and "current cell"? Am I using the wrong method?

Thanks...

Kazimierz Jawor Kazimierz Jawor · Accepted Answer · 2013-12-05T19:49:40

Change your function into something like this:

Function CurrColumnName() As String
   Dim colnum As Integer
   colnum = Application.ThisCell.Column
   CurrColumnName = Chr(colnum + 64)
End Function

Application.ThisCell is a key in this solution. For more information you could see this Application.ThisCell property by MSDN.

Excel VBA : ActiveCell not interpreted correctly when pasting into range

1 Answers