Excel Custom Function Creation with Existing Functions

[PBJ1099]

Is it possible to create a custom Excel function that incorporates pre-existing Excel funcions? If so, what is the propert syntax to do this in the VBA Module builder?

To give you more info, I currently have to add multiple SUMPRODUCT functions to capture data on multiple tables to summarize this into one table. When I add new tables (which represent different accounts), I have to manually go into each page that utilizes this conglomeration of SUMPRODUCT functions and edit them. I would rather just edit one custom function than edit all of these formulas.

 

[FDibbins]

Maybe if you showed what you are working with, what you are using, and what you want, it might be easier to assist?

 

[Rick Rothstein]

Is it possible to create a custom Excel function that incorporates pre-existing Excel funcions? If so, what is the propert syntax to do this in the VBA Module builder?

To give you more info, I currently have to add multiple SUMPRODUCT functions to capture data on multiple tables to summarize this into one table. When I add new tables (which represent different accounts), I have to manually go into each page that utilizes this conglomeration of SUMPRODUCT functions and edit them. I would rather just edit one custom function than edit all of these formulas.

As Ford indicated, you really have not given us enough information to help you construct the code; however, I can tell you that a large number of Excel functions can be implemented in VBA via the WorksheetFunction object. To see what is available, type this (note the dot on the end)...

WorksheetFunction.

and VB's intellisense will show you all the function you have available with this object./

 

[PBJ1099]

Hey FDibbins and Rick, thanks so much for reaching out. I'd be happy to explain a little more with what I'm working with.

My goal is to create a custom function that incorporates multiple SUMPRODUCT functions. The reason being is that I want this custom function to be editable to include a high number of tables (which are referenced in the function as seen below. Right now, the SUMPRODUCT funcions I use work great but needs to be built out for each table it references. I have about 20 tables to reference and and slowly adding more over time. When I add a new table, I have to edit the equation on every tab I use it for. I would just rather edit 1 custom function that includes all these SUMPRODUCT functions.


Here is the breakdown of the SUMPRODUCT function I have created:
SUMPRODUCT(table_name[column_1],table_name[column_2]=”category_name”)+0,(LEFT(table_name[column3],3)=LEFT(cell,3))+0, -(table_name[column_4]>=date_cell), -(table_name[column_4]<=date_cell)


I then input this data into multiple income statements depending on the cagetory_names so it gets tedious to repeatedly update these when new table_names come into existence so this custom Excel function would be easier to change than the current method. Below you can see what the function looks like referencing just 2 tables.


Example
-(SUMPRODUCT(Citi_Costco[Payment Amount],(Citi_Costco[Category]="Income Statement")+0,(LEFT(Citi_Costco[Transaction],3)=LEFT($A5,3))+0, -(Citi_Costco[Date]>= IS!$A$4),-(Citi_Costco[Date]<= IS!$B$4)) + SUMPRODUCT(Chase_FU[Payment Amount],(Chase_FU[Category]="Income Statement")+0,(LEFT(Chase_FU[Transaction],3)=LEFT($A5,3))+0, -(Chase_FU[Date]>= IS!$A$4),-(Chase_FU[Date]<= IS!$B$4))) + (SUMPRODUCT(Citi_Costco[Deposit Amount],(Citi_Costco[Category]="Income Statement")+0,(LEFT(Citi_Costco[Transaction],3)=LEFT($A5,3))+0, -(Citi_Costco[Date]>= IS!$A$4),-(Citi_Costco[Date]<= IS!$B$4)) + SUMPRODUCT(Chase_FU[Deposit Amount],(Chase_FU[Category]="Income Statement")+0,(LEFT(Chase_FU[Transaction],3)=LEFT($A5,3))+0, -(Chase_FU[Date]>= IS!$A$4),-(Chase_FU[Date]<= IS!$B$4)))


If you would like, here is a link to the Excel file the shows you this equation in motion: https://drive.google.com/open?id=0B990IftVyrAJejJVVllPTEZoSTQ

Let me know if you have any further questions!

 

[FDibbins]

I am not permitted to access file hosting sites, so I am unable to view your file, but my 1st question would be - why do you need to many different tables? The generally accepted method for data storage, is to have all data in 1 table, then extract from that table. Have you considered trying to take this approach?

 

[PBJ1099]

Hey FDibbins, I wish I could use just one single table as you mentioned. However, each table services a different account which has it's own balance check that is run on a single table basis.