Looking for help?
< All Topics
Print

Accounting Seed Financial Cube Formula (ASFC)

In order to support the custom ASFC formula for Accounting Seed orgs, you’ll need to create a custom formula in your VBA editor as follows:

  1. Click on Developer -> Script Editor in Excel
  2. Create the following functions in VBA editor:
    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

Now if you want this macro to appear in all documents that you open on this computer click on File -> Save As and save the document as an Excel Add-In:

With these formulas you can create comprehensive Financial reports:

 

Table of Contents