Setting HorizontalAlignment with VBA

[Jo]

Hello,

I encounter a problem, when setting the
HorizontalAlignment of a range. Excel changes the
alignment of all cells and not only the cells in the
range. Here ist the code I use:

Dim doc As Excel.Application
Dim wrkbk As Excel.Workbook
Dim wrksh As Excel.Worksheet
Dim range As Excel.Range
Dim style As Excel.Style

doc = CreateObject("Excel.Application")
doc.Visible = True
wrkbk = doc.Workbooks.Add()
wrksh = wrkbk.Worksheets(1)
range = wrksh.Range("A1")
style = range.Style
style.HorizontalAlignment = Excel.Constants.xlCenter

Could anybody help me? Thank's
Jo

 

[Dave Peterson]

You were changing the style. So any cell that used that style (probably
normal--and all cells would default that way) would get changed.

I don't like range, style, and doc as variable names. The first two are objects
in excel and doc sounds like it should be associated with MSWord.

And you'll need to use the keyword Set (well, I did when I called the code from
a macro in Word):

Option Explicit
Sub aaa()
Dim myXL As Excel.Application
Dim wrkbk As Excel.Workbook
Dim wrksh As Excel.Worksheet
Dim myrange As Excel.range
Dim mystyle As Excel.style

Set myXL = CreateObject("Excel.Application")
myXL.Visible = True
Set wrkbk = myXL.Workbooks.Add()
Set wrksh = wrkbk.Worksheets(1)
Set myrange = wrksh.range("A1")
myrange.HorizontalAlignment = Excel.Constants.xlCenter
End Sub

(If this is that .net stuff, then maybe you don't need the Set statement (but
that's beyond me!))