CODEWITHSUNDEEP
excelvbaoutlook
Alluvian
Alluvian

Reputation: 139

VBA: Getting Excel conditional formatting to work from Outlook

I have an Outlook VBA script that is doing some email parsing and putting the results in a new excel document created by the script.

The VBA outlook sections in that are problematic are:

sub test
    Dim oXLApp As Object, oXLwb As Object, oXLws As Object

    '~~> Establish an EXCEL application object
    On Error Resume Next
    Set oXLApp = GetObject(, "Excel.Application")

    '~~> If not found then create new instance
    If Err.Number <> 0 Then
        Set oXLApp = CreateObject("Excel.Application")
    End If
    Err.Clear
    On Error GoTo 0
    Set oXLwb = oXLApp.Workbooks.Add
    Set oXLws = oXLwb.Sheets("Sheet1")

    ' lots of stuff here that parses emails and puts them in the spreadsheet

    Call formatRN(oXLws)
end sub

Sub formatRN(xlsheet As Object)

    ' row and column sizes
    xlsheet.Cells.RowHeight = 15
    xlsheet.Columns("A:A").ColumnWidth = 50
    xlsheet.Columns("B:B").EntireColumn.AutoFit
    xlsheet.Columns("C:C").EntireColumn.AutoFit
    xlsheet.Columns("D:D").ColumnWidth = 50
    xlsheet.Columns("E:E").ColumnWidth = 50
    xlsheet.Columns("F:F").ColumnWidth = 50
    xlsheet.Columns("G:G").ColumnWidth = 50
    xlsheet.Columns("H:H").ColumnWidth = 50
    xlsheet.Columns("I:I").ColumnWidth = 50


    ' conditional format for empty cells
    xlsheet.Columns("A:I").Select
    xlsheet.Columns("A:I").FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(TRIM(A1))=0"
    xlsheet.Columns("A:I").FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With xlsheet.Columns("A:I").FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
    End With
    xlsheet.Columns("A:I").FormatConditions(1).StopIfTrue = False


End Sub

The line: xlsheet.Columns("A:I").FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(TRIM(A1))=0"

Is giving me the error: "Invalid proceedure call or argument"

The following code works FINE in Excel though. I am not sure what the issue is:

sub test()
    Dim xlsheet As Worksheet
    Set xlsheet = ActiveSheet

    xlsheet.Columns("A:I").Select
    xlsheet.Columns("A:I").FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(TRIM(A1))=0"
    xlsheet.Columns("A:I").FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With xlsheet.Columns("A:I").FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
    End With
    xlsheet.Columns("A:I").FormatConditions(1).StopIfTrue = False
end sub

Since I am making the excel sheet in the script, I can't really have this vba code pre-exist in the excel sheet, and I would rather not had to put a separate global level vba code in excel on the computer of the end user for this code.

Any tips would be GREAT, thank you!

Upvotes: 1

Views: 606

Answers (1)

David Zemens
David Zemens

Reputation: 53623

If you are using late-bound Excel (without explicit Reference to the Microsoft Excel object model) as it appears you're doing, then enumerated constants which are part of that model, such as xlExpression will be interpreted as an undeclared variable with a zero value. Its value should be 2

Put this at the top of your code to warn you of all such problems in the future:

Option Explicit

Then, declare your variable like:

Const xlExpression As Long = 2

The code you have from Excel,when used in Excel does not rely on an external reference to Excel's object model and associated enumerated constants, so that is why the code works in Excel, but does not appear to work when called from another application (without using early-binding).

Upvotes: 3

Related Questions