Tuesday, January 19, 2010

Date Format Fix in Excel 2003

Due to an issue in Excel 2003, sometime the date formats are not updated in the cells, which leads to the problem, that a cell shows a date but is not read as a date by macros and formulas.

You could go through each cell, press 'F2' and 'Enter' and Excel would finally identify the content of the cell as a date. But this you would do manually and not by script.

With the following VBA script for Excel, the format can be updated:


Sub UpdateColumnFormat()

Dim usedRangeOfRows As Integer
usedRangeOfRows = ActiveSheet.UsedRange.Rows.Count

'Date
Columns("A:A").Select
Dim e As Range
Dim currentValueE As Date
For Each e In Range("A2:A" & usedRangeOfRows).Cells
e.Select
currentValueE = ActiveCell.Value
ActiveCell.FormulaR1C1 = currentValueE
Next

End Sub


This problem occurs regularly, when I copy data from one sheet to another by VBA scripts.

With the above script, I could fix (at least work around it) this issue.

Enjoy :)

Monday, January 18, 2010

"Create List..." in Excel 2003 with VBA

While working on data within Excel with VBA, I like to use dynamic lists to address the data and eventually to give it a name.

To create a list, the same one as you would do manually by calling "Create List..." from the context menu, you can execute the VBA script below.


Private Sub CreateList()

Range("A1").Select

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Dim NumberOfColumns As Integer
Dim NumberOfRows As Integer
Dim StartCell As Range
Dim EndCell As Range

NumberOfColumns = ActiveSheet.UsedRange.Columns.Count
NumberOfRows = ActiveSheet.UsedRange.Rows.Count

Set StartCell = Cells(1, 1)
Set EndCell = Cells(NumberOfRows, NumberOfColumns)

Dim DataRange As Range
Set DataRange = Range(StartCell, EndCell)

ActiveSheet.ListObjects.Add(xlSrcRange, DataRange, , xlYes).Name = "List1"
Range(StartCell, EndCell).Select
ActiveWorkbook.Names.Add Name:="Data", RefersToR1C1:=DataRange
Range("Data").Select

End Sub


I also give the list the name "Data", which I do to access the data by name afterwords, but this is not required to create the list.

Enjoy :)