Bad variable type in Excel object library

[brazilnut52]

This is odd I am getting Bad Variable Type exception in the line: oXL.WorksheetFunction.CountA(rng.Rows(i)) = 0 Then

However, the line:

oXL.WorksheetFunction.Sum(ws.Range(ws.Cells(i, colNumStart), ws.Cells(i, lastCol)))
is executed before the first... and it works fine.

here are the lines that call the class functions:

Dim oSheet As Excel.Worksheet
Dim oXL As Excel.Application
 Create Excel objects
oXL = New Excel.Application()
Dim oWB As Excel.Workbook
 Open file
oWB = oXL.Workbooks.Open(fileToFormat)
oSheet = oWB.ActiveSheet
Dim oGenFuncs As New EAL_Format.generalFormatFunctions(oSheet, oXL)
Call oGenFuncs.generalFormat()
.
.
.
Call oGenFuncs.GetUsedRange(lastRow, lastCol)

the call to oXL.WorksheetFunction works fine in the first call....(though they are different functions one is Sum and the other is CountA...)

Have any ideas of why Iam getting the Bad Variable Type error? Also if you have any suggestions for my code in general I would appreciate it.







Here is my class:

Public Class generalFormatFunctions

    Private ws As Excel.Worksheet
    Private oXL As Excel.Application

    Sub New(ByRef worksheet As Excel.Worksheet, ByRef excel As Excel.Application)
        ws = worksheet
        oXL = excel
    End Sub

    Public Function generalFormat()
        
         generalFormat Macro
         Macro recorded 1/31/2003 by Andre de Araujo Jorge
         performs general required formating to both income and market value files

           Delete left three and Up 50 and Down 50 cols

        ws.Columns("A:C").Delete()
        ws.Columns("E").Delete()
        ws.Columns("F").Delete()

         Divide all numerical cells by 1000
        Dim cellType As Excel.XlCellType
        Dim pasteType As Excel.XlPasteSpecialOperation
        Dim pstVals As Excel.XlPasteType
        Dim numFormat As Excel.XlPasteSpecialOperation
        pasteType = Excel.XlPasteSpecialOperation.xlPasteSpecialOperationDivide
        pstVals = Excel.XlPasteType.xlPasteValues
        cellType = Excel.XlCellType.xlCellTypeConstants
        ws.Range("J9").Value = 1000
        ws.Range("J9").Copy()
        ws.Range("B:H").SpecialCells(cellType).PasteSpecial(pstVals, pasteType)
        ws.Range("B:H").SpecialCells(cellType).NumberFormat() = 0
        ws.Range("J9").Clear()

        Call DeleteInvalidRows()

    End Function

    Public Function DeleteInvalidRows()
        Dim colNumStart As Integer = 2
        Dim wsFuncs As Excel.WorksheetFunction
        wsFuncs = New Excel.WorksheetFunction()
        wsFuncs = oXL.WorksheetFunction
        Delete all rows that have 0 sum - assuming numbers start in col 2
        Dim lastCol = ws.UsedRange.Columns.Count
        Dim i As Integer
        Dim sumTotal As Double
        For i = ws.UsedRange.Rows.Count To 1 Step -1
            If oXL.WorksheetFunction.Sum(ws.Range(ws.Cells(i, colNumStart), ws.Cells(i, lastCol))) = 0 Then
                ws.Cells(i, 1).EntireRow.Delete()
            End If
        Next i
    End Function

    Public Function inArray(ByRef stringArray() As String, ByVal searchString As String) As Boolean
        Dim upperBound As Integer
        upperBound = UBound(stringArray)
        Dim i As Integer
        For i = 0 To upperBound Step 1
            If stringArray(i) = searchString Then
                inArray = True
                Exit Function
            End If
        Next
        inArray = False
    End Function

    Public Function GetUsedRange(ByRef lastRow As Integer, ByRef lastCol As Integer) As Boolean
        
         GetUsedRange Macro
         Macro recorded 3/5/2003 by dearaan
        
         Keyboard Shortcut: Ctrl+u
        
        Assumes that Excels UsedRange gives at least a superset of the "real" non-null data
         then get the effective range of non-null data

        Dim s As String, x As Long
        Dim rng As Excel.Range
        Dim newUsedRange As Excel.Range
        Dim r1fixed As Long, c1Fixed As Long
        Dim r2Fixed As Long, c2Fixed As Long
        Dim i As Long
        Dim r1 As Long, c1 As Long
        Dim r2 As Long, c2 As Long

        Start with Excels faulty used range
        rng = ws.UsedRange

        Get bounding cells for Excels UsedRange
        That is, Cells(r1,c1) to Cells(r2,c2)
        r1 = rng.Row
        r2 = rng.Rows.Count + r1 - 1
        c1 = rng.Column
        c2 = rng.Columns.Count + c1 - 1

        Save existing values
        r1fixed = r1
        r2Fixed = r2
        c1Fixed = c1
        c2Fixed = c2

        Check rows from top down for all blank rows
        if found, shrink rows
        For i = 1 To r2Fixed - r1fixed + 1
            If oXL.WorksheetFunction.CountA(rng.Rows(i)) = 0 Then
                empty row -- reduce
                r1 = r1 + 1
            Else
                nonempty row, get out
                Exit For
            End If
        Next



        Repeat for columns from left to right
        For i = 1 To c2Fixed - c1Fixed + 1
            If oXL.WorksheetFunction.CountA(rng.Columns(i)) = 0 Then
                c1 = c1 + 1
            Else
                Exit For
            End If
        Next

        reset the range
        rng = ws.Range(ws.Cells(r1, c1), ws.Cells(r2, c2))

        Start again
        r1fixed = r1
        c1Fixed = c1
        r2Fixed = r2
        c2Fixed = c2

        do rows from bottom up
        For i = r2Fixed - r1fixed + 1 To 1 Step -1
            If oXL.WorksheetFunction.CountA(rng.Rows(i)) = 0 Then
                r2 = r2 - 1
            Else
                Exit For
            End If
        Next

        repeat for columns fro right to left
        For i = c2Fixed - c1Fixed + 1 To 1 Step -1
            If oXL.WorksheetFunction.CountA(rng.Columns(i)) = 0 Then
                c2 = c2 - 1
            Else
                Exit For
            End If
        Next

        lastRow = r2
        lastCol = c2

    End Function

End Class

[edit]Please use

 tags [/vb ][/edit][/COLOR]

 

[brazilnut52]

bump...