Excel methods

[cedx]

Hi,

Anyone know where can I find the definition of what each method and property in a MS Excel object does?

More specifically, I need to know how to format the contents of cells in an Excel spreadsheet.

Thanks in advance.

It depends on what formatting you want to do, I read extensively and could only find about making cells bold italic etc. I did try to get currency but did not have time to get it working. I used the following simple code to make my column names bold.
For i = 1 to numberofcells
Set xlr = xls.Cells(1, i)
xlr.Select
xlr.Font.Bold = True
Next i
Hope this simple bit of code helps. It could probably be done better but my time was limited and I don't have a lot of VB Knowledge as I am self taught.

Fiona

[Scott Penner]

An easy way you can play around with all the formatting is similar to VB in that you can use the format$ method when changing the values of cells. To use Excel formatting a bit more, you should take a look at the Range object. From the range object you can change borders, fonts, and number format.
Do a search from Excel VBA on the range object and look at the properties.

Here's a little preview of what you will do:

Range(Cells(1, 1),Cells(10, 10)).Borders.LineStyle = xlThick
Range(Cells(1, 1),Cells(10, 10)).NumberFormat = "$#,##0.00"

The above code will act on the active worksheet. To act on any worksheet, you can use the specific worksheet object before the range object.

Hope that helps,

Thanks Scott,
I will try that, as I said did not have a lot of time.

Fiona

[Terence]

Try if this lazy way work or not.

Turn on the macro option and start recording. Then do whatever you wish on your excel spreadsheet, stop recording and start macro editor. You will see the coding for what you have done. Copy it !

Terence

[hmcheung]

Hi I've tried your lazy method. However when I copy the code from macro to VB and run it, it prompts me that the variable is not defined for xlsolid. The macro code is like this:

    With O.selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With

I've checked in my object browser that there is only something called 'msoPatternSolidDiamond' in there. I want add color to the cell and at the same time keep its border. Thanks!

[Terence]

I have done a simple test :

start an excel object, then create a macro to select some cells to set up borders and set color. It worked.

The coding is as follow :

Private Sub Form_Load()

Dim objExcel As Excel.Application
Dim objWs As Excel.Worksheet
Dim objWb As Excel.Workbook

Set objExcel = CreateObject("Excel.Application")
With objExcel
.Visible = True
Set objWb = .Workbooks.Add
With objWb
Set objWs = objWb.Worksheets.Add
Range("A1:C3").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End With

End Sub

By the way, I don't know what your "diamond" object is. Where does it come from ?

[hmcheung]

Oh thanks for the quick reply. I'm able to get around with it. I've found out that I didn't add the Excel object reference, after I have added it, it works fine. the diamond object is from the Office object reference and I have no idea of what it is. thanks!