Some Help Please to Translate an Excel Macro into VB.Net Code

E

eBob.com

I am building an Excel spreadsheet using VB.Net code. I want the vertical
alignment for all cells to be "Top". To know what code I'd need I turned on
macro recording and went through the manual process. I got the following
result ...

Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

I only care about the vertical alignment, so I am not trying to do any of
the other stuff in VB.Net. Instead of the Cells.Select I found that in my
code I'd need "objSheet.Cells.Select()" - that compiles and builds without
error. It's the vertical alignment line that is causing me problems.
"objSheet.Selection.VerticalAlighment = objApp.xltop" does not work because
Selection is not a member of Excel._Worksheet and
"objSheet.VerticalAlighment = objApp.xltop" does not work because
verticalalignment is not a member of Excel._Worksheet.

I'll appreciate any help anyone can offer. Thanks, Bob
 
J

JMB

In Excel VBA, this would normally be done with
objSheet.Cells.VerticalAlignment = xlTop

(I've not worked w/VB.Net so you may need objApp.xlTop as you posted. If
xltop doesn't work, try using -4160 instead).
 
E

eBob.com

Thank you JMB. I did have to use -4160; objApp.xlTop compiles and builds OK
but results in a runtime error. Additional thanks for including the hint
about -4160. I would have been spinning my wheels on that one for awhile!

Thanks, Bob
 
J

JMB

Only because I've spun my wheels on that when I first tried to automate Word
w/an Excel macro <g>. Come to find out (thanks to Peo) I couldn't use word
constants as I was using late binding instead of early binding (which also
required a reference set to Microsoft Word through Tools/References in the
VBA editor).

At any rate, if you need the underlying value for XL constants, go into the
VBA editor in XL (Alt F11) and enter

?xltop

in the immediate window (Cntrl+G or View/Immediate Window).

Glad to hear you got it working.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top