Automation of Excel is actually pretty interesting task. VBA is indeed the best built-in tool for this and although there is a lot of “hate” towards it it really deserves to be taken into account seriously, at least for small daily tasks. For tougher tasks, it is probably not the best weapon of choice of anybody, but there are cases when programming with VBA indeed causes the “WOW” effect.
In this example I will present a small piece of code, which actually takes the longest possible string between two identical characters in Excel. Imagine that this is what is needed:
In A1 there is a simple text and in A2 there is a substring of this text, containing the longest string between two dots. Working with a User-Defined Function (UDF) in Excel is the way to do this really nicely:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Public Function GetTheLongest(text As String, _ Optional splitter As String = ".") As String Dim arr As Variant Dim counter As Long arr = Split(text, splitter) For counter = LBound(arr) To UBound(arr) If Len(arr(counter)) > Len(GetTheLongest) Then GetTheLongest = arr(counter) End If Next counter GetTheLongest = Trim(GetTheLongest & splitter) End Function |
In order to make the UDF work, these should be made Public or put in the Worksheet module of the worksheet, where they should be used.
Cheers!