How to Convert My VBA Code

Aug 17, 2014 at 9:19 AM
Edited Aug 17, 2014 at 9:54 AM
Hi Support,

I have some VBA Code, and it use Excel function ie: sumifs, but I do not know how to pass it my parameters,because it just only accept paramarray object,if you can help to take a VB.net sample for my VBA code?

Thanks a lot.
Function ReturnRat(ByVal ValueRange_1 As Range, ByVal ValueRange_2 As Range, ByVal CriteriaCatRange As Range, ByVal CatFilter As String, ByVal CriteriaRange_1 As Range, ByVal Filter_1 As String, ByVal CriteriaRange_2 As Range, ByVal Filter_2 As String, Optional ByVal CriteriaRange_3 As Range, Optional ByVal Filter_3 As String) As Variant

Dim MyRatio_1 As Double, MyRatio_2 As Double, MySumRatio_1 As Currency, MySumRatio_2 As Currency

If TypeName(CriteriaRange_3) = "Nothing" Then
    MyRatio_1 = Application.WorksheetFunction.SumIfs(ValueRange_1, CriteriaRange_1, Filter_1, CriteriaRange_2, Filter_2)
    MyRatio_2 = Application.WorksheetFunction.SumIfs(ValueRange_2, CriteriaRange_1, Filter_1, CriteriaRange_2, Filter_2)
Else
    MyRatio_1 = Application.WorksheetFunction.SumIfs(ValueRange_1, CriteriaRange_1, Filter_1, CriteriaRange_2, Filter_2, CriteriaRange_3, Filter_3)
    MyRatio_2 = Application.WorksheetFunction.SumIfs(ValueRange_2, CriteriaRange_1, Filter_1, CriteriaRange_2, Filter_2, CriteriaRange_3, Filter_3)
End If

MySumRatio_1 = Application.WorksheetFunction.SumIfs(ValueRange_1, CriteriaRange_1, Filter_1, CriteriaCatRange, CatFilter)
MySumRatio_2 = Application.WorksheetFunction.SumIfs(ValueRange_2, CriteriaRange_1, Filter_1, CriteriaCatRange, CatFilter)

If MySumRatio_1 = 0 And MySumRatio_2 <> 0 Then
    ReturnRat = MyRatio_2
ElseIf MySumRatio_1 <> 0 And MySumRatio_2 = 0 Then
    ReturnRat = MyRatio_1
ElseIf MySumRatio_1 = 0 And MySumRatio_2 = 0 Then
    ReturnRat = 0
Else
    ReturnRat = MyRatio_1 * 0.5 + MyRatio_2 * 0.5
End If


End Function
Coordinator
Aug 18, 2014 at 5:21 PM
Edited Aug 18, 2014 at 5:24 PM
Instead of using the COM object model for calling the Excel functions, you might call it using the native API (implemented in Excel-DNA via XlCall).
Then you'd just take Object's for those parameters, and your code might look like this (The Excel call you see is actually XlCall.Excel, but with the Imports it's a bit neater):
Imports ExcelDna.Integration
Imports ExcelDna.Integration.XlCall

Public Module MyFunctions
    Public Function ReturnRat(ByVal ValueRange_1 As Object, ByVal ValueRange_2 As Object, ByVal CriteriaCatRange As Object, ByVal CatFilter As String, ByVal CriteriaRange_1 As Object, ByVal Filter_1 As String, ByVal CriteriaRange_2 As Object, ByVal Filter_2 As String, ByVal CriteriaRange_3 As Object, ByVal Filter_3 As String) As Object
        Dim MyRatio_1 As Double, MyRatio_2 As Double, MySumRatio_1 As Double, MySumRatio_2 As Double

        If TypeOf CriteriaRange_3 Is ExcelMissing Then
            MyRatio_1 = Excel(xlfSumifs, ValueRange_1, CriteriaRange_1, Filter_1, CriteriaRange_2, Filter_2)
            MyRatio_2 = Excel(xlfSumifs, ValueRange_2, CriteriaRange_1, Filter_1, CriteriaRange_2, Filter_2)
        Else
            MyRatio_1 = Excel(xlfSumifs, ValueRange_1, CriteriaRange_1, Filter_1, CriteriaRange_2, Filter_2, CriteriaRange_3, Filter_3)
            MyRatio_2 = Excel(xlfSumifs, ValueRange_2, CriteriaRange_1, Filter_1, CriteriaRange_2, Filter_2, CriteriaRange_3, Filter_3)
        End If

        MySumRatio_1 = Excel(xlfSumifs, ValueRange_1, CriteriaRange_1, Filter_1, CriteriaCatRange, CatFilter)
        MySumRatio_2 = Excel(xlfSumifs, ValueRange_2, CriteriaRange_1, Filter_1, CriteriaCatRange, CatFilter)

        If MySumRatio_1 = 0 And MySumRatio_2 <> 0 Then
            ReturnRat = MyRatio_2
        ElseIf MySumRatio_1 <> 0 And MySumRatio_2 = 0 Then
            ReturnRat = MyRatio_1
        ElseIf MySumRatio_1 = 0 And MySumRatio_2 = 0 Then
            ReturnRat = 0
        Else
            ReturnRat = MyRatio_1 * 0.5 + MyRatio_2 * 0.5
        End If
    End Function
End Module
There are other approaches - you can get an ExcelReference as input, which can be converted to a COM Range object. Search on the Google group for "ReferenceToRange" for more info to go in that direction.

-Govert