CODEWITHSUNDEEP
excelvbaexcel-formula
DG.Finance
DG.Finance

Reputation: 85

Referencing another excel tab in VBA function

Once the VBA function conditionals are met, I want the function to execute the normal excel formula below:

=SUMIF('Barclays - Interest'A:C, CONCATENATE(A1,"-",B1),'Barclays - Interest'C:C)

  Function RECON (AccountName, Broker, Forex)
     If AccountName = "Barclays" Then 
         RECON = SUMIF('Barclays - Interest'A:C, CONCATENATE(AccountName,"-",Broker),'Barclays - Interest'C:C)

The above code is what I'd essentially like to achieve, any ideas on a workaround to this? (This failed for me each time) Ideally I'd like to keep the formula format the same so colleagues without VBA knowledge can easily manipulate it based off future data changes.

Upvotes: 0

Views: 55

Answers (1)

urdearboy
urdearboy

Reputation: 14580

Create a variable to store your sheet name and just reference the range. 

Function RECON(AccountName, Broker, Forex)

    If AccountName = "Barclays" Then
        Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Barclays - Interest")
        RECON = Application.WorksheetFunction.SumIf(ws.Range("A:C"), CONCATENATE(AccountName, "-", Broker), ws.Range("C:C"))

    '.... More Code
    '.... Make sure to END that IF

End Function

You may also want to give RECON a variable type. Long? Double? idk. 

Function RECON(AccountName, Broker, Forex) as [Variable Type]

Upvotes: 1

Related Questions