none
WorksheetFunction Method

    Question

  • Hi!, I tell you:

    Error on run time in Application.Worksheets.Function method only with condicional

    Example:



    Option Explicit On
    Option Strict On
    
    Imports System
    Imports System.Globalization
    Imports System.Threading
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office.Interop.Excel
    
    
    Dim xlApp As New Microsoft.Office.Interop.Excel.Application
    Dim xlBook As Microsoft.Office.Interop.Excel.Workbook = CType(GetObject("C:\Address\Book1.xlsm"), Workbook)
    Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet = CType(xlBook.Worksheets("Hoja1"), Worksheet)
    
    
    Private Sub BtnReporte_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnReporte.Click
    
    Dim result As Double
    
    result = CInt(xlApp.WorksheetFunction.CountIf(CType(xlHoja.Range("A1:A10"), Range), 5))     '<<<<<<---- ERROR!
    
    MessageBox.Show(CStr(result))
    
    End Sub




    If method is not condicional, run perfect ! for example only Count, within " If " or " Ifs " 


    result = CInt(xlApp.WorksheetFunction.Count(CType(xlHoja.Range("A1:A10")))



    This happens to me with all the functions (Count, Sum, Average, etc.)



    "InvalidCastException"


    i try with:


    resultado = CInt(xlApp.WorksheetFunction.CountIf(CType(xlHoja.Range("A1:A10"), Range), "5"))

    resultado = CInt(xlApp.WorksheetFunction.CountIf(CType(xlHoja.Range("A1:A10"), Range), CStr(5)))

    resultado = CInt(xlApp.WorksheetFunction.CountIf(CType(xlHoja.Range("A1:A10"), Range), REFERENCIA A UNA CELDA))

    resultado = CInt(xlApp.WorksheetFunction.CountIf(CType(xlHoja.Range("A1:A10"), Range), "=5"))

    resultado = xlApp.WorksheetFunction.CountIf(xlHoja.Range("A1:A10"), 5)

    resultado = xlApp.WorksheetFunction.CountIf(xlHoja.Range("A1:A10"), "5")

    resultado = xlApp.WorksheetFunction.CountIf(xlHoja.Range("A1:A10"), "=5")

    But nothing results :

    Any suggestions?

    Thanks you very much !

    Monday, April 3, 2017 11:22 PM

All replies

  • What is the exception (error) message?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, April 4, 2017 2:31 AM
  • Hi,

    result = CInt(xlApp.WorksheetFunction.CountIf(CType(xlHoja.Range("A1:A10"), Range), 5))

    I suppose CountIf function cannot apply to CType(something).
    Try to remove "CType":
    result = CInt(xlApp.WorksheetFunction.CountIf(xlHoja.Range("A1:A10"), Range), 5)

    Regards,
    Ashidacchi
    Tuesday, April 4, 2017 3:02 AM
  • Hi,

    result = CInt(xlApp.WorksheetFunction.CountIf(CType(xlHoja.Range("A1:A10"), Range), 5))

    I suppose CountIf function cannot apply to CType(something).
    Try to remove "CType":
    result = CInt(xlApp.WorksheetFunction.CountIf(xlHoja.Range("A1:A10"), Range), 5)

    Regards,
    Ashidacchi

    Did you try:

    result = xlApp.WorksheetFunction.CountIf(xlHoja.Range("A1:A10"), 5)


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, April 5, 2017 12:56 PM
  • Hi Paul,

    I have done nothing. What I wrote was only on desk.

    Did it try Ismael?

    Regards,

    Ashidacchi

    Wednesday, April 5, 2017 1:30 PM
  • Hi Paul,

    I have done nothing. What I wrote was only on desk.

    Did it try Ismael?

    Regards,

    Ashidacchi


    Sorry I confused you with the OP. I guess he never replied.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, April 5, 2017 2:30 PM