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]

That did it! I used the Add-In per bosquedeguate and your correction and I am GOLDEN! A million Thanks to you both.

Excellent!!

 

[MARK858]

That did it! I used the Add-In per bosquedeguate and your correction and I am GOLDEN! A million Thanks to you both.

You're welcome

 

[WTHamIdoing]

Try this version of the function in your add-in (keep your original code for now).
CustomSummary2

It works based on Sheet index numbers

Function CustomSummary2(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
  Dim idxStart As Integer
  Dim idxEnd As Integer
  Dim w As Integer
 
  sumResult = 0
  startFound = False
  endFound = False
 
  Dim initTabExists As Boolean
  Dim endTabExists As Boolean
  initTabExists = False
  endTabExists = False
 
  idxStart = Worksheets(init_tab).Index
  If idxStart = 0 Then
    MsgBox "Start tab " & init_tab & " not found."
    Exit Function
  End If
  idxStart = idxStart + 1
  initTabExists = True
  startFound = True
  idxEnd = Worksheets(end_tab).Index
  If idxEnd = 0 Then
    MsgBox "End tab " & end_tab & " not found."
    Exit Function
  End If
  idxEnd = idxEnd - 1
  endTabExists = True
  endFound = True
 
  For w = idxStart To idxEnd
    Set ws = Worksheets(w)
     
    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
     
  Next w

  CustomSummary2 = sumResult


End Function

This is a winner too. This forum rocks. Thank you very much.

 

[Bosquedeguate]

This is a winner too. This forum rocks. Thank you very much.

I would stick with the prior code that you modified that worked, i.e. adding "ThisWorkbook." to your loop. It gives you more flexibility in the position of your sheets (tabs).

 

[MARK858]

I would stick with the prior code that you modified that worked, i.e. adding "ThisWorkbook." to your loop. It gives you more flexibility in the position of your sheets (tabs).

It should not be ThisWorkbook if it is in the addin, it should be ActiveWorkbook.
It needs to refer to the workbook the code is working on and not the workbook that the code is in.