Monday, November 7, 2011

Why doesn't center alignment macro work?

Troubleshooting Excel macros
I was recently working on an Excel spreadsheet macro to automatically center the text with a cell on the cells within a sheet. This is a VBScript macro executed from a CAD system and exports desired data to an Excel file. I used the following code:

With Excel.Range("A"&"1", "F"&RwNum)

.Font.Name = "Arial"

.Font.Size = 9

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.ColumnWidth = 25

.RowHeight = 20

.Borders.LineStyle = xlContinuous

.Borders.Weight = xlThick

.Borders.ColorIndex = 1

.WrapText = True

.EntireColumn.Autofit


End With


Well, the Horizontal and Vertical alignment was not working at all. I tried numerous different methods, spellings, etc. Nothing seemed to work. Then I discovered I was missing a line of code. Simply add this statement before the With loop:

Const xlCenter = -4108

Because we're accessing a spreadsheet from outside of Excel the xlCenter constant is not declared which makes VBA treat it as 0. So in our code we need to set a value for xlCenter, hence the code line of: Const xlCenter = -4108. Here are some other values you may need to set if you are formatting or sorting your Excel sheet.

Const xlCenter = -4108
'Const xlAscending = 1
'Const xlYes = 1
'Const xlSortOnValues = 0
'Const xlSortNormal = 0
'Const xlTopToBottom = 1
'Const xlPinYin = 1

To figure out the value number go into excel VBA editor and press crtl+g then type ?xlAscending and it will list the current value.

To sort a list using VBScript as opposed to VBA here is an example code you might need:

'sort parts in numerical order then sort fasteners
Excel.Range("A:G").Select

Excel.Selection.Sort Excel.Range("G1"),1,Excel.Range("A1"),,1,Excel.Range("B1"),1,1,1,False