Custom Function in XLSX?

[WTHamIdoing]

I have a workbook that has a custom function in it. A custom function was the only way to make xcel give back the data needed. I want to use the workbook as a template to merge date from our CRM into. Unfortuntately FormStack (The app that does the merge from our CRM to Excel) does not support macro enabled workbooks. Anyone have an idea how I can make the resulting spreadsheet (*.xlsx) still utilize the function?
Below is the bulk of the function. THere are two other small pieces but I dont think they matter to my question.

Function CustomSummary(init_tab As String, end_tab As String, keyword As String, columnToSearch As String, columnToReturn As String) As Variant

Dim ws As Worksheet
Dim startFound As Boolean
Dim endFound As Boolean
Dim sumResult As Double

sumResult = 0
startFound = False
endFound = False

Dim initTabExists As Boolean
Dim endTabExists As Boolean
initTabExists = False
endTabExists = False

For Each ws In ThisWorkbook.Worksheets

If ws.Name = init_tab Then
initTabExists = True
startFound = True

If endFound Then
CustomSummary = "End tab '" & end_tab & "' found before Init tab '" & init_tab & "'."
Exit Function
End If

End If

If startFound And ws.Name <> init_tab And ws.Name <> end_tab Then

Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, columnToSearch).End(xlUp).Row

Dim rng As Range
Set rng = ws.Range(columnToSearch & "1:" & columnToSearch & lastRow)

Dim cell As Range
Dim buffer_val As Variant

For Each cell In rng

If cell.Value = keyword Then

Set buffer_val = ws.Range(columnToReturn & cell.Row)

If IsNumeric(buffer_val) Then

sumResult = sumResult + buffer_val.Value

End If

End If

Next cell

End If

If ws.Name = end_tab Then

endTabExists = True
endFound = True

If Not startFound Then
CustomSummary = "Init tab '" & init_tab & "' not found."
Exit Function
End If

Exit For

End If

Next ws

If Not initTabExists Then
CustomSummary = "Init tab '" & init_tab & "' not found."
Exit Function
End If

If Not endTabExists Then
CustomSummary = "End tab '" & end_tab & "' not found."
Exit Function
End If


CustomSummary = sumResult


End Function

 

[Bosquedeguate]

Could you do an intermediate step
Export your CRM data to CSV file then import the CSV file using your macro or a modified version of it?

 

[WTHamIdoing]

Could you do an intermediate step
Export your CRM data to CSV file then import the CSV file using your macro or a modified version of it?

Thanks for reading. Good thinking, and I wish I could do that but the data being exported has fomulas already built in and lots of formatting in order to produce a nice quote for our engineering services. And different data goes into different secions throughout the template.

 

[Bosquedeguate]

Ok …
How about this idea …
Compile your macros and save them in a file
Load your CRM data.
Add the macros back in as a library.

 

[Dave Patton]

You may be able to build a Lambda function or functions.
Review the information on Lambda functions and Dynamic formulas.

Someone may be able to help if you
- provide a clear explanation of what you require
- provide a concise example with expected results; see the forum's tool named XL2BB

 

[Bosquedeguate]

Here is what I just tried and it seemed to work.

• I created a very simple, do nothing function in VBA
• I saved the file form the Excel (not Developer) menu as an Excel Add-In
• Deleted the file that had the original macro/function code
• Opened a new Worksheet
• Went to the Developer tab added my new Excel Add-In
• Save the new Workbook as an Macro-free file.

So, if your function code is well tested this would be an option for you, I think.

If you want to test try it. the one function I have in the Add-In does nothing more than return TRUE.
You're welcome to try. Or the "big-boy" option would be to do this with your functions.

You may need to isolate your VB code to the functions and/or macros that are common to all of your similar workbooks. I.e. delete anything that is unique to a specific workbook.

 

[WTHamIdoing]

Here is what I just tried and it seemed to work.

• I created a very simple, do nothing function in VBA
• I saved the file form the Excel (not Developer) menu as an Excel Add-In
• Deleted the file that had the original macro/function code
• Opened a new Worksheet
• Went to the Developer tab added my new Excel Add-In
• Save the new Workbook as an Macro-free file.

So, if your function code is well tested this would be an option for you, I think.

If you want to test try it. the one function I have in the Add-In does nothing more than return TRUE.
You're welcome to try. Or the "big-boy" option would be to do this with your functions.

You may need to isolate your VB code to the functions and/or macros that are common to all of your similar workbooks. I.e. delete anything that is unique to a specific workbook.

OH so close now! I created the add-in with my actual function. Activated it. When I open up a blank workbook, the function is there to use. YES! When I fill in the variables in the function dialog box =CustomSummary("Customer Summary","Deliverables","TOTAL MACHINERY:","b","h"), it does not find the End Tab that is plainy there . The error is

End tab 'Deliverables' not found.

I suspect there is something in the code i had in my original post that sets the End Tab that is not activating in some way. This is the closes I have been in days, though! I am not a VBA expert BY FAR and only trying to make someone elses inherited workbook work in a new way so thanks for getting me this far!

 

[WTHamIdoing]

You may be able to build a Lambda function or functions.
Review the information on Lambda functions and Dynamic formulas.

Someone may be able to help if you
- provide a clear explanation of what you require
- provide a concise example with expected results; see the forum's tool named XL2BB

I am trying to create a summary page (Customer Summary) for a varying number of worksheets between Customer Summary tab and Deliverables tab where the cells I need to summarize are in different, unpredictible places on each tab. I have been able to almost make use xlookup since the headings I need to summarize are always in column B and the number I need to add to the sum is always in column H but the fact that there are always a differnt number of sheets to summarize is beyond me. That is what the custome function was doing for me I downloaded XL2BB and will try to upload a mini sheet.

 

[Bosquedeguate]

OH so close now! I created the add-in with my actual function. Activated it. When I open up a blank workbook, the function is there to use. YES! When I fill in the variables in the function dialog box =CustomSummary("Customer Summary","Deliverables","TOTAL MACHINERY:","b","h"), it does not find the End Tab that is plainy there . The error is

End tab 'Deliverables' not found.

I suspect there is something in the code i had in my original post that sets the End Tab that is not activating in some way. This is the closes I have been in days, though! I am not a VBA expert BY FAR and only trying to make someone elses inherited workbook work in a new way so thanks for getting me this far!

I am trying to create a summary page (Customer Summary) for a varying number of worksheets between Customer Summary tab and Deliverables tab where the cells I need to summarize are in different, unpredictible places on each tab. I have been able to almost make use xlookup since the headings I need to summarize are always in column B and the number I need to add to the sum is always in column H but the fact that there are always a differnt number of sheets to summarize is beyond me. That is what the custome function was doing for me I downloaded XL2BB and will try to upload a mini sheet.

Can you cut an paste the actual use of the function in your worksheet? The first two arguments are the names of Worksheets. Therefore, "Deliverables" should be one of those arguments, but I need to know which one so I can try to determine how/where the function is not working

 

[WTHamIdoing]

Can you cut an paste the actual use of the function in your worksheet? The first two arguments are the names of Worksheets. Therefore, "Deliverables" should be one of those arguments, but I need to know which one so I can try to determine how/where the function is not working

See below. The function is just in D15 right now and should sum the 2 sheets between Customer Summery and Delierables. It should look for "Total Machinery:" in column B of each sheet and return the value in column H on the same row. The sum should be 9. ANd it is when I use the Macro enabled workbook version. So something breaks when I created the add-in. I must have missed something.