If you are just rewriting your existing formula, pretty much "as-is" within the UDF, then there really isn't much point in writing a UDF (just use the formula). UDFs are more helpful useful when you have formulas that tend to be very long or messy, or you just cannot envision a way to write it without the use of VBA, where you can take advantage to things like having the ability to use loops.
Let me show you how I would write one of the functions in your example as a UDF.
In your original example [SW1|GARS2::GARS1], you wanted to return GARS2 in your second column (the value between the "|" and the first ":").
Granted, this can be done fairly easily with a function and probably doesn't require a UDF, but I will just use it to show you a few different ways that this could be done using a UDF, so you can begin to see how using UDFs and VBA may be advantageous in certain situations.
The first one is pretty straightforward. Just locate the "|" and ":" and take everything in between. That code might look something like this:
Code:
Function GetSecondWord(myString As String) As String
Dim startChar As Long
Dim endChar As Long
' Check to see if length of entry is greater than zero
If Len(myString) > 0 Then
' Find location of first "|"
startChar = InStr(myString, "|") + 1
' Find location of first ":"
endChar = InStr(myString, ":") - 1
' Extract characters between the start and end
GetSecondWord = Mid(myString, startChar, endChar - startChar + 1)
End If
End Function
Now, for more complex functions, you may actually need or want to loop through all the characters of the string, one-by-one. This example wouldn't really require it, but I will show you how to do it that way for illustration purposes. Essentially, what we are going to do is loop through it, and when we find the "|", we will indicate that the string has started, and we will keep adding to it one character at a time until we find the ending character (":"). That code may look something like this:
Code:
Function GetSecondWord2(myString As String) As String
Dim i As Long
Dim myLen As Long
Dim curChar As String
Dim stringStart As Boolean
Dim workingString As String
' Default setting to start
stringStart = False
' Check to see if length of entry is greater than zero
myLen = Len(myString)
If myLen > 0 Then
' Loop through each character in string
For i = 1 To myLen
' Capture current character
curChar = Mid(myString, i, 1)
' If current character is "|", mark the string is about to start
If curChar = "|" Then
stringStart = True
Else
' If the current character is ":", string is done so exit loop
If curChar = ":" Then
Exit For
Else
' If string is started and not at end, add current character to working string
If stringStart = True Then
workingString = workingString + curChar
End If
End If
End If
Next i
' Set final working string to value to return
GetSecondWord2 = workingString
End If
End Function
Obviously, the first one is preferable and more efficient, as it doesn't involve loops. But if you run in to some complex situations, you may need to do something like the second example.
Also note that these functions are "scaled" down, and you might need/want to add more checks or error handling (like what if a string does have a starting or ending character, etc), as your situation dictates. But hopefully this helps give you a "blueprint" of how you can write UDFs to handle some complex or messy function writing.
To me, I find UDFs a lot easier to follow, maintain, and update than long, complex messy formulas. And if it is a function you will be re-using over and over again, if you ever need to change it, you only need to change it in one place, as opposed to formulas that you have written throughout your project (where you would need to update every single instance of that formula).
Hope this helps!
Windows 8 
Access 2010 32bit
Reply With Quote
