Accounting Seed Financial Cube Formula (ASFC) for Salesforce

Public Function ASFC(ledger As String, glAccount As String, glav1 As String, glav2 As String, glav3 As String, glav4 As String, period As String, amt_type As String)
 Dim addin As Office.COMAddIn
 Dim automationObject As Object
 Set automationObject = Application.COMAddIns("TaralexLLC.SalesforceEnabler").Object
 
 
  '------------------VALIDATIONS----------------------
    
    If IsNull(ledger) Or IsEmpty(ledger) Then
        ASFC = "Ledger is required"
        End
    End If
    If IsNull(glAccount) Or IsEmpty(glAccount) Then
        ASFC = "GL Account is required"
        End
    End If
    If IsNull(glav1) Or IsEmpty(glav1) Then
        glav1 = "NONE"
    End If
    If IsNull(glav2) Or IsEmpty(glav2) Then
        glav2 = "NONE"
    End If
    If IsNull(glav3) Or IsEmpty(glav3) Then
        glav3 = "NONE"
    End If
    If IsNull(glav4) Or IsEmpty(glav4) Then
        glav4 = "NONE"
    End If
    If IsNull(period) Or IsEmpty(period) Then
        ASFC = "Period is required"
        End
    End If
    If IsNull(amt_type) Or IsEmpty(amt_type) Then
        ASFC = "Amount Type is required"
        End
    End If
    
    If Not amt_type = "OPB" And Not amt_type = "BUD" And Not amt_type = "MTD" And Not amt_type = "YTD" Then
        ASFC = "Invalid parameter: 'Amount type'"
        End
    End If
    '------------------END VALIDATIONS----------------------
    If amt_type = "OPB" Then
        q_amt_type = " sum(AcctSeed__Opening_Balance__c) "
    End If
    If amt_type = "BUD" Then
        q_amt_type = " sum(AcctSeed__Amount__c) "
    End If
    If amt_type = "MTD" Then
        q_amt_type = " sum(AcctSeed__Current_Period__c) "
    End If
    If amt_type = "YTD" Then
        q_amt_type = " sum(AcctSeed__Year_To_Date__c) "
    End If
    
    If glav1 = "NONE" Then
        q_glav1 = "and AcctSeed__GL_Account_Variable_1__c = NULL "
    Else
        If glav1 = "ALL" Then
            q_glav1 = " "
        Else
            q_glav1 = "and AcctSeed__GL_Account_Variable_1__r.Name = '" + glav1 + "' "
        End If
    End If
    
    If glav2 = "NONE" Then
        q_glav2 = "and AcctSeed__GL_Account_Variable_2__c = NULL "
    Else
        If glav2 = "ALL" Then
            q_glav2 = " "
        Else
            q_glav2 = "and AcctSeed__GL_Account_Variable_2__r.Name = '" + glav2 + "' "
        End If
    End If
    If glav3 = "NONE" Then
        q_glav3 = "and AcctSeed__GL_Account_Variable_3__c = NULL "
    Else
        If glav3 = "ALL" Then
            q_glav3 = " "
        Else
            q_glav3 = "and AcctSeed__GL_Account_Variable_3__r.Name = '" + glav3 + "' "
        End If
    End If
    If glav4 = "NONE" Then
        q_glav4 = "and AcctSeed__GL_Account_Variable_4__c = NULL "
    Else
        If glav4 = "ALL" Then
            q_glav4 = " "
        Else
            q_glav4 = "and AcctSeed__GL_Account_Variable_4__r.Name = '" + glav4 + "' "
        End If
    End If
    
 
    q_ledger = EscapeQuote(ledger)
    q_glAccount = EscapeQuote(glAccount)
    q_period = EscapeQuote(period)
    query = "SELECT " + q_amt_type + " FROM AcctSeed__Financial_Cube__c WHERE AcctSeed__Ledger__r.Name = '" + q_ledger + _
    "' and AcctSeed__GL_Account__r.Name = '" + q_glAccount + _
    "' and AcctSeed__Accounting_Period__r.Name = '" + q_period + _
    "' " + q_glav1 + q_glav2 + q_glav3 + q_glav4

    ar = automationObject.RetrieveData(query, False, False, errorText)
    
    If errorText = "The column(s) you selected in your query is (are) empty in all the records." Then
    ASFC = 0
    ElseIf Not errorText = Empty Then
      ASFC = errorText
    Else
      ASFC = ar(0, 1)
    End If

End Function
Function EscapeQuote(value As String)
    Test = Replace(value, "'", "/'")
    EscapeQuote = Test
End Function