VBA Excel Custom Function def

[Shahzadt]

Hi I am working with Ron's file zip VBA code. the code is using following function and i don't understand what it is doing. I am an intermediate VBA user and never have had the pleasure of working with VBAs evaluate and application.substitute functions. any clarification will be greatly appreciated

Function Split97(sStr As Variant, sdelim As String) As Variant
'Tom Ogilvy
    Split97 = Evaluate("{""" & Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

 

[Sthrncali]

Evaluate - There are a few things this function can do, but the most useful in my opinion, and in the context of your provided example, it is "processing" the given formula the same way a cell would in a worksheet.

Meaning without putting the forumula in the cell, and allowing excel to "calculate" all formulas in the workbook, it virtually processes the formula and returns the result.

Substitue is very similiar to a FIND and REPLACE, except in the scope of a text string.

For example:

Substitute("Apple", "p", "u") would = "Auule"

 

[Shahzadt]

thank you