Call C# function in Excel VBA; not working

Can you post the code you're using from within VBA. As silly as it sounds, I think the error might reside there.

What version of office are you using?

Jell

Hi Jell,
I'm using office 2003.

Private Function mf_DoTrades(ByVal blnCalculateOnly As Boolean, ByVal lngCurrentPage As Long, ByVal lngPageCount As Long) As eActionStatus
    Dim strTradeBasis As String
    Dim strDealType As String
    Dim strBook As String
    Dim strStock As String
    Dim strBasketDesc As String
    Dim strBasketRef As String
    Dim strPriceType As String
    Dim strNarrative As String
    Dim dblNominal As Double
    Dim dblPrice As Double
    Dim dblCommission As Double
    Dim strCommType As String
    Dim strCommArg As String
    Dim strTradeComm As String
    Dim strSalesman As String
    Dim strTradeDate As String
    Dim strValueDate As String
    Dim strExchange As String
    Dim strBasketId As String
    Dim lngCurrentRow As Long
    Dim lngErrorTrades As Long
    Dim lngWarningTrades As Long
    Dim wsCurrent As Worksheet
    Dim blnIsBasket As Boolean
    Dim blnHasMultiTrades As Boolean
    Dim bIgnoreCommCap As Boolean
    Dim bIgnorePriceVariance As Boolean
    Dim asTradeResult As eActionStatus
    Dim lngTradeCount As Long
    Dim lngRightmostColumn As Long
    Dim lngResultColumn As Long
    Dim lngSplitResultColumn As Long
    Dim blnSplitsExist As Boolean
    Dim rngCommCell As Range
    Dim lngPageLoop As Long
    Dim blnLoopPages As Boolean
    Dim dblSplitRowTotal As Double
    Dim lngPageOriginal As Long
    Dim lngSplitColumn As Long
    Dim sdSplitData As CSplitData
    Dim blnSplitSuccess As Boolean
    Dim blnAllocatedBasket As Boolean
    Dim vntSplitAccts As Variant
    Dim vntSplitNominalsOneRow As Variant
    Dim vntSplitNominalsAll As Variant
    Dim lngFirstRow As Long
    Dim lngLastRow As Long
    Dim strClient As String
    Dim colTrades As Collection             'holds the trade data
    Dim tdCurrentTrade As CTradeData        'holds the data for the trade currently being processed

    Dim lCount As Long

    Dim basket As basket
    Dim dtTrader As trade
    Dim dtSplitTrade As allocation
    Dim dtTradeCharge As TradeCharge
   
    'Set up error
    On Error GoTo DoTradesError

    err.Clear
   
    With Application
        .ScreenUpdating = False
        Set wsCurrent = .ActiveSheet
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With

    Set colTrades = New Collection

    lngTradeCount = mf_GetTradeCount(wsCurrent)
    blnHasMultiTrades = (lngTradeCount > 1)
    blnLoopPages = (lngPageCount > 0)
    lngPageOriginal = lngCurrentPage

    '
    'now we retrieve the values from the various named ranges.
    '
    '**********************************************************************************************
    'NOTE:  It is of VITAL importance that the named ranges (e.g. "TRADE_BASIS", "BOOK_CODE", etc.)
    'exist in the worksheet and also that they point to the appropriate cells or the macro will not work.
    '**********************************************************************************************
    '

    'read "common" fields and assign their values to local variables

    With wsCurrent
        strTradeBasis = .Range("TRADE_BASIS").Value
        strBook = UCase$(CStr(.Range("BOOK_CODE").Value))
        strClient = CStr(.Range("CPTY_CODE").Value)
        strSalesman = UCase$(CStr(.Range("SALESMAN").Value))
        strExchange = UCase$(CStr(.Range("EXCHANGE").Value))
        strTradeDate = Format$(CDate(.Range("TRADE_DATE").Value), "yyyymmdd")
        strValueDate = Format$(CDate(.Range("VALUE_DATE").Value), "yyyymmdd")
        strPriceType = UCase$(CStr(.Range("PRICE_TYPE").Value))
        strBasketDesc = CStr(.Range("BASKET_DESC").Value)
        strBasketRef = CStr(.Range("BASKET_REF").Value)

        'Should we ignore the difference between enterd price and market price?
        If UCase$(CStr(.Range("IGNORE_LOCAL_PRICE").Value)) = "Y" Then
            bIgnorePriceVariance = True
        Else
            bIgnorePriceVariance = False
        End If

        'the trades will be 'basketed' only if the user has specified this in the "BASKET_FLAG" cell
        'AND if there's more than one trade on the sheet
        blnIsBasket = ((UCase$(Left$(CStr(.Range("BASKET_FLAG").Value & " "), 1)) = "Y") _
            And blnHasMultiTrades)

        lngFirstRow = .Range("SUCCESS_HEADER").Row + 1
        .Range("BASKET_ID").ClearContents
        lngResultColumn = .Range("SUCCESS_HEADER").Column
        strCommType = CStr(.Range("COMMISSION_TYPE").Value & "")
        strCommArg = CStr(.Range("COMMISSION_ARGUMENT").Value & "")
        lngSplitResultColumn = .Range("SPLIT_RESULT").Column
    End With

    lngLastRow = (lngFirstRow + lngTradeCount - 1)
    ReDim vntSplitNominalsAll(lngFirstRow To lngLastRow)

    lngErrorTrades = 0
    lngWarningTrades = 0
    blnAllocatedBasket = False

    If blnIsBasket And (Not blnCalculateOnly) Then
        'Avoid Generating BASKET_ID during validation

        If strBasketRef = "" Then
            strBasketRef = " "
        End If

        If strBasketDesc = "" Then
            strBasketDesc = " "
        End If
       
        basket.SetObjectValue "BookId", strBook
        basket.SetObjectValue "CounterpartyId", strClient
        basket.SetObjectValue "DealtPriceType", strPriceType
        basket.SetObjectValue "SettlementDate", strValueDate
        basket.SetObjectValue "TradeDate", strTradeDate
        basket.SetObjectValue "BasketType", "JP"
        basket.SetObjectValue "TradeCount", lngTradeCount
        basket.SetObjectValue "BasketReference", strBasketRef
        basket.SetObjectValue "BasketDescription", strBasketDesc

    End If

    If blnLoopPages Then
        Call ms_ShowSubAcctPage(lngCurrentPage, 0, False)
    End If


    'retrieve trades from worksheet

    Do      'we have to go through the trades at least once, hence the Do/Loop While construct

        For lngCurrentRow = lngFirstRow To lngLastRow

            blnSplitsExist = False      'reset semaphore for next trade

            With wsCurrent
                'find out if there's any splits...
                'find the rightmost column in use, if it's not at least as far across as
                'the first split nominal column, then we know there's no splits for this trade

                lngRightmostColumn = .Cells(lngCurrentRow, 256).End(xlToLeft).Column

                If lngRightmostColumn >= .Range("FIRST_SUBACCT_NUMBER").Column Then

                    'grab all of the columns, this way we guarantee that the variant will hold an array
                    'of values and not just a single value

                    'first, the account numbers...
                    vntSplitAccts = .Range(.Range("FIRST_SUBACCT_NUMBER"), .Cells(.Range("FIRST_SUBACCT_NUMBER").Row, _
                        MAX_COLUMN_IN_USE)).Value
                    '... and then the nominals
                    vntSplitNominalsOneRow = .Range(.Cells(lngCurrentRow, .Range("FIRST_SUBACCT_NUMBER").Column), _
                        .Cells(lngCurrentRow, MAX_COLUMN_IN_USE)).Value
                    blnSplitsExist = True
                    dblSplitRowTotal = 0#
                Else
                    vntSplitNominalsOneRow = Empty
                End If

                'at this point, vntSplitNominalsOneRow will be in one of two states:
                '       Empty, if no nominals are present
                '       2-dimensional Array of strings (1,n) if one or more nominals are present

                vntSplitNominalsAll(lngCurrentRow) = vntSplitNominalsOneRow

            End With        'end of "With wsCurrent" block

            If mf_GetNextTrade(wsCurrent, lngCurrentRow, strStock, dblNominal, dblPrice, strDealType, strTradeComm) Then

                If lngPageLoop = 0 Then
                    'we're on the first page of subaccounts, then the trades need to be added to the
                    'trades collection
                    Set tdCurrentTrade = New CTradeData
                    With tdCurrentTrade
                        'reverse the buy/sell flag so that the users can interpret this from their perspective
                        .DealType = IIf(strDealType = "B", "S", "B")
                        .Nominal = dblNominal
                        .Price = dblPrice
                        .RowNumber = lngCurrentRow
                        .Stock = strStock
                        .Commission = strTradeComm
                        .Status = AS_SUCCESS
                        'references to the result cells for the trades are stored within the CTradeData instance
                        'this is done to enhance code readability where these cells are populated
                        'with result codes, it should also be an improvement in performance over
                        'the alternative, which is using repeated Range/Cells references
                        Set .SplitResultCell = wsCurrent.Cells(lngCurrentRow, lngSplitResultColumn)
                        Set .TradeResultCell = wsCurrent.Cells(lngCurrentRow, lngResultColumn)
                        Set .GrossConsidCell = wsCurrent.Cells(lngCurrentRow, wsCurrent.Range("GROSS_CONSID_HEADER").Column)
                        Set .NetConsidCell = wsCurrent.Cells(lngCurrentRow, wsCurrent.Range("NET_CONSID_HEADER").Column)
                    End With

                    colTrades.Add Item:=tdCurrentTrade, Key:=CStr(lngCurrentRow)
                Else
                    'we're on a subsequent page of subaccounts, and the trades are already
                    'in the collection so grab the trade object that corresponds to the current row
                    tdCurrentTrade = colTrades.Item(CStr(lngCurrentRow))
                End If

                If blnSplitsExist Then

                    For lngSplitColumn = LBound(vntSplitNominalsOneRow, 2) To UBound(vntSplitNominalsOneRow, 2)

                        'proceed only if there's a nominal and a subaccount specified for the column
                        If Not IsEmpty(vntSplitNominalsOneRow(1, lngSplitColumn)) And Not IsEmpty(vntSplitAccts(1, lngSplitColumn)) Then
                            vntSplitNominalsOneRow = vntSplitNominalsAll(lngCurrentRow)
                            'the nominals are stored in an array of collections, one element per spreadsheet row.
                            'this is done because the number of nominals per row can vary wildly,
                            'so by using a collection we can store only the nominals specified and
                            'later we can (and do) iterate through the collection using a For Each loop
                            'to add up the nominals (for Calculate) or to Commit them.
                            sdSplitData = New CSplitData
                            sdSplitData.AccountNumber = CStr(vntSplitAccts(1, lngSplitColumn))
                            sdSplitData.Nominal = CDbl(vntSplitNominalsOneRow(1, lngSplitColumn))
                            'Consider the split trade only if nominal is greater than zero
                            If sdSplitData.Nominal > 0 Then
                                tdCurrentTrade.Splits.Add Item:=sdSplitData, _
                                                        Key:=sdSplitData.AccountNumber
                            End If

                        End If

                    Next lngSplitColumn

                End If

                'If this is a basket trade and there are splits then it is an allocated
                'basket.  Set this variable to true, which is used later to send
                'MODIFY transaction to basket record.
                If blnIsBasket And tdCurrentTrade.Splits.Count > 0 Then
                    blnAllocatedBasket = True
                End If
               
                If Not blnCalculateOnly Then
                    If blnIsBasket And tdCurrentTrade.Splits.Count > 0 Then
                        basket.SetObjectValue "AllocatedBasket", "Y"
                    Else
                        basket.SetObjectValue "AllocatedBasket", "N"
                    End If
                End If
            Else

                'set the status cell when the trade entry is missing one or more fields
                With wsCurrent.Cells(lngCurrentRow, lngResultColumn)
                    .Value = "INCOMPLETE"
                    .Interior.Color = RED
                    lngErrorTrades = lngErrorTrades + 1
                End With

            End If              'end of "If mf_GetNextTrade..." block

        Next lngCurrentRow

        'if there's more than one page of subaccounts then we need to iterate through all of them
        'to get all of the split nominals
        If blnLoopPages Then
            lngPageLoop = lngPageLoop + 1
            If lngPageLoop <= lngPageCount Then
                Call ms_ShowSubAcctPage(lngPageLoop - 1, lngPageLoop, False)
            End If
        End If

    Loop While (blnLoopPages) And (lngPageLoop <= lngPageCount)

    'Set CommissionType to either FIXED_AMOUNT or BASIS_POINT
    If strCommType = "FXTR" Then
        strCommType = "FIXED_AMOUNT"
    ElseIf strCommType = "FXBP" Then
        strCommType = "BASIS_POINT"
    End If
   
    lCount = colTrades.Count
    'process trades

    'trap errors returned from DaiwaTrader so that they can be displayed
    'in the worksheet in the status column
    On Error Resume Next

    For Each tdCurrentTrade In colTrades
        Set dtTrader = New trade
        With dtTrader
            .SetObjectValue "RepresentativeId", strSalesman
            .SetObjectValue "BuySell", tdCurrentTrade.DealType
            .SetObjectValue "InstrumentCode", tdCurrentTrade.Stock
            .SetObjectValue "CounterpartyCode", strClient
            .SetObjectValue "BookId", strBook
            .SetObjectValue "TradeBasis", strTradeBasis
            .SetObjectValue "Quantity", tdCurrentTrade.Nominal
            .SetObjectValue "MarketId", strExchange
            .SetObjectValue "ValueDate", strValueDate
            .SetObjectValue "BidPrice", tdCurrentTrade.Price
            .SetObjectValue "OfferPrice", tdCurrentTrade.Price


        If strPriceType <> "N" Then
            'This should load the charges for the deal if price structure is not NET
            Set dtTradeCharge = New TradeCharge
            If IsEmpty(wsCurrent.Cells(tdCurrentTrade.RowNumber, wsCurrent.Range("COMMISSION_HEADER").Column)) Then
                'if the commission Type is set then
                If strCommType <> "" Then
                    '.SetValue(COMMISSION_METHOD, strCommType)
                    dtTradeCharge.SetObjectValue "CalculationMethod", strCommType
                    'If strCommType = "FXTR" And_
                    If strCommType = "FIXED_AMOUNT" And _
                        (Not IsEmpty(wsCurrent.Cells(tdCurrentTrade.RowNumber, wsCurrent.Range("COMMISSION_HEADER").Column))) Then
                        '.SetValue(COMMISSION_ARG, tdCurrentTrade.Commission)
                        dtTradeCharge.SetObjectValue "CalculationValue", tdCurrentTrade.Commission
                    Else
                        '.SetValue(COMMISSION_ARG, strCommArg)
                        dtTradeCharge.SetObjectValue "CalculationValue", strCommArg
                    End If
                Else
                    'do nothing so that it uses default commission
                End If
            Else
                dtTradeCharge.SetObjectValue "CalculationMethod", strCommType
                'check if the commission type is FXBP
                If strCommType = "BASIS_POINT" Then
                    dtTradeCharge.SetObjectValue "CalculationValue", strCommArg
                Else
                    'users have entered fixed commission against each trade
                    .SetObjectValue "Commission", tdCurrentTrade.Commission
                End If
            End If
           
            .AddTradeCharge (dtTradeCharge)
            'Check to see if TradeCharge has been added to Trade
            Dim tradeCharges As FidessaDataModels.TradeChargeSet
            Set tradeCharges = New FidessaDataModels.TradeChargeSet
           
            tradeCharges = dtTrader.GetObjectValue("TradeCharges")
           
            For Each dtTradeCharge In tradeCharges
                Dim s As String
                s = dtTradeCharge.GetObjectValue("ChargeId")
            Next
            On Error GoTo DoTradesError
            err.Clear

        End If

        If tdCurrentTrade.Status = AS_SUCCESS Then
            Dim RetVal As Boolean
            If blnCalculateOnly Then
                'asTradeResult = .PerformAction(ACTION_VALIDATE)
                If Not .ValidateTrade() Then
                    On Error GoTo DoTradesError
                End If
            'Else
                'asTradeResult = .PerformAction(ACTION_COMMIT)
                'RetVal = .Insert()
            End If


            If asTradeResult = AS_FAILURE Or err.Number <> 0 Then
                tdCurrentTrade.Status = AS_FAILURE
                'tdCurrentTrade.TradeResultCell.Value = "ERROR: " & .GetLastErrorDescription()
                tdCurrentTrade.TradeResultCell.Interior.Color = RED
                lngErrorTrades = lngErrorTrades + 1
                tdCurrentTrade.TradeID = "FAILED"
            Else
                If asTradeResult = AS_WARNING Then
                    If tdCurrentTrade.Status = AS_SUCCESS Then
                        tdCurrentTrade.Status = AS_WARNING
                        lngWarningTrades = lngWarningTrades + 1
                        tdCurrentTrade.TradeResultCell.Interior.Color = YELLOW
                        'tdCurrentTrade.TradeResultCell.Value = .GetLastErrorDescription()
                    End If
                Else
                    tdCurrentTrade.Status = AS_SUCCESS
                    tdCurrentTrade.TradeResultCell.Interior.Color = STATUS_GREY
                    If blnCalculateOnly Then
                        tdCurrentTrade.TradeResultCell.Value = "Calculated"
                        tdCurrentTrade.TradeID = "OK"
                    Else
                        tdCurrentTrade.TradeResultCell.Value = .GetObjectValue("TradeId")
                        tdCurrentTrade.TradeID = .GetObjectValue("TradeId")
                    End If
                    tdCurrentTrade.GrossConsidCell.Value = .GetObjectValue("GrossConsideration")
                    tdCurrentTrade.NetConsidCell.Value = .GetObjectValue("NetConsideration")
                End If

                If strTradeComm = "" Then
                    rngCommCell = wsCurrent.Cells(tdCurrentTrade.RowNumber, wsCurrent.Range("COMMISSION_HEADER").Column)
                    rngCommCell.Value = .GetObjectValue("Commission")
                End If

                'If strCommType = "FXBP" Then
                If strCommType = "BASIS_POINT" Then
                    rngCommCell = wsCurrent.Cells(tdCurrentTrade.RowNumber, wsCurrent.Range("COMMISSION_HEADER").Column)
                    rngCommCell.Value = .GetObjectValue("Commission")
                End If

            End If              'end of "If asTradeResult = as_FAILURE ..." block

            If Not blnCalculateOnly And tdCurrentTrade.Splits.Count > 0 Then
                'This is a commit, Block trade is already committed, now commit split
                Set dtSplitTrade = New allocation
                dtSplitTrade.SetObjectValue "TradeSetId", tdCurrentTrade.TradeID
               
                For Each sdSplitData In tdCurrentTrade.Splits
                    'multiple split nominals
                    dtSplitTrade.MoveNext
                    dtSplitTrade.SetObjectValue "CptyAccountId", sdSplitData.AccountNumber
                    dtSplitTrade.SetObjectValue "CtacAllocationQuantity", sdSplitData.Nominal
                    'dtTrader.AddAllocation (dtSplitTrade)
                Next

                dtSplitTrade.MoveTo (0)

                'If dtSplitTrade.PerformAction(ACTION_COMMIT) = AS_SUCCESS Then
                '    blnSplitSuccess = True
                'Else
                '    blnSplitSuccess = False
                'End If
                With tdCurrentTrade.SplitResultCell
                    If blnSplitSuccess And err.Number = 0 Then
                        .Value = "OK"
                        .Interior.Color = STATUS_GREY
                    Else
                        .Value = dtSplitTrade.GetLastErrorDescription()
                        .Interior.Color = RED
                        lngErrorTrades = lngErrorTrades + 1
                        err.Clear
                    End If
                End With

                'set the split trade object to nothing
                Set dtSplitTrade = Nothing
            ElseIf blnCalculateOnly Then
                'just add up the nominals to see if they match the trade nominal...
                '...if they don't match, it's an error and will be flagged accordingly
                '...if there are no splits at all for the trade, then it merits only a warning to the user
                dblSplitRowTotal = 0#
                For Each sdSplitData In tdCurrentTrade.Splits
                    dblSplitRowTotal = dblSplitRowTotal + sdSplitData.Nominal
                Next
                If dblSplitRowTotal = 0# Then
                    If tdCurrentTrade.Status = AS_SUCCESS Then
                        tdCurrentTrade.Status = AS_WARNING
                        tdCurrentTrade.TradeResultCell.Interior.Color = YELLOW
                        tdCurrentTrade.TradeResultCell.Value = "NO SPLITS"
                        tdCurrentTrade.SplitResultCell = Empty
                        tdCurrentTrade.SplitResultCell.Interior.Color = STATUS_GREY
                        lngWarningTrades = lngWarningTrades + 1
                    End If

                Else
                    With tdCurrentTrade.SplitResultCell
                        If dblSplitRowTotal = tdCurrentTrade.Nominal Then
                            .Value = "VALID"
                            .Interior.Color = STATUS_GREY
                        Else
                            .Value = "INVALID"
                            .Interior.Color = RED
                            lngErrorTrades = lngErrorTrades + 1
                        End If
                    End With
                End If
            End If  'end of if not blnCalculateOnly

        End If              'end of "If tdCurrentTrade.Status = AS_SUCCESS Then" block

        End With                'end of "With dtTrader" block
       
        If blnIsBasket And Not blnCalculateOnly Then
            basket.AddTrade dtTrader
        End If
       
        Set dtTrader = Nothing
       
    Next tdCurrentTrade

    err.Clear
    On Error GoTo DoTradesError
   
    If Not blnCalculateOnly Then
        If basket.CreateBasket() Then
            strBasketId = basket.GetObjectValue("BasketId")
            wsCurrent.Range("BASKET_ID").Value = strBasketId
        Else
            Dim strError As String
            Dim error As Variant
            For Each error In basket.FieldErrors
                strError = strError + error.Text
            Next
            MsgBox "Unable to create Basket. " + vbCrLf + strError, vbCritical
        End If
    End If

DoTradesError:

    If (err.Number <> 0) Then
        MsgBox ("Error: " & err.Description & " in " & err.Source)
        lngErrorTrades = lngErrorTrades + 1
    End If

    'clean up all objects and arrays
    wsCurrent = Nothing
    rngCommCell = Nothing
    sdSplitData = Nothing
    Set dtSplitTrade = Nothing
    Set colTrades = Nothing
    tdCurrentTrade = Nothing
    vntSplitNominalsAll = Empty
    vntSplitNominalsOneRow = Empty
    vntSplitAccts = Empty

    'show the page of subaccounts that was displayed when the user clicked "Calculate" or "Commit"
    If blnLoopPages Then
        Call ms_ShowSubAcctPage(lngPageCount, lngPageOriginal, False)
    End If

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With

    'return result code, based on whether or not trade processing resulted in any errors or warnings
    If (lngErrorTrades > 0) Then
        mf_DoTrades = AS_FAILURE
    ElseIf (lngWarningTrades > 0) Then
        mf_DoTrades = AS_WARNING
    Else
        mf_DoTrades = AS_SUCCESS
    End If

End Function

The errors are

Sorry!  It is a lot of code!

OK,
Problem(1)
I'm going to try this out, although i have other methods with return values which work.

Problem (2)
both Call .AddTradeCharge(dtTradeCharge) and
.AddTradeCharge dtTradeCharge do not cause the argument not optional error and work!  

Problem(3)
Any idea why the intellisense might not be working?!

PS - thanks for you help so far!

Problem(3)

Not really an expert in com interfaces but

Try adding:

[ComVisible(True)]

and/or setting this property to true in the properties window:

ReferenceAssemblyFromVbaProject

Even without the intellisense working, when i inherit from multiple interfaces, it is only picking up one of them and not the other.  So the methods/properties of ITrade are picked up, but not those of IDataContainer.  It does not however throw an error saying that the methods and properties don't exist, but the calls to the methods jsut don't do anything.  I really can't see anything that I'm missing here.  I know for a fact that when I have a class that inherits just from IDataContainer, I can access it's methods/properties as I am using this elsewhere.