[AccessD] A2003 - Build a List/Array from Tokens in a blurb

[Darren - Active Billing]

Hi Gustav,
So sorry for the delay in getting back to this.
because I have limited skills in VBA - I’m not sure I know how to implement your cool functions.
If I add a MsgBox or Debug.print statement I get a “Compile Error: Type Mismatch” error message after the button click.
This happens if I add the MsgBox or Debug.Print lines to the very last lines in your cool function.

EG.
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Function SplitTokens(ByVal Message As String) As String()
   
……your cool Function here…..

   SplitTokens = Tokens

    'Debug.Print SplitTokens
    ‘Debug.print Tokens
    'Msgbox SplitTokens
    'Msgbox Tokens
    
End Function
~~~~~~~~~~~~~~~~~~~~~~~~~~

not sure what I’m doing wrong.
Apologies and Many thanks in advance

D

> On 21 Aug 2015, at 6:52 pm, Gustav Brock <gustav at cactus.dk> wrote:
> 
> Hi Darren
> 
> Split is your friend:
> 
> <code>
> Public Function SplitTokens(ByVal Message As String) As String()
> 
>    Dim Parts       As Variant
>    Dim Tokens()    As String
>    Dim Token       As Integer
> 
>    ' Split into first parts.
>    Parts = Split(Message, "[")
> 
>    If UBound(Parts) <= 0 Then
>        ' Return array with LBound = 0.
>        ReDim Tokens(0)
>    Else
>        ' Return array with LBound = 1.
>        ' Clean parts and fill array.
>        ReDim Tokens(1 To UBound(Parts))
>        For Token = 1 + LBound(Parts) To UBound(Parts)
>            Tokens(Token) = "[" & Split(Parts(Token), "]", 2)(0) & "]"
>        Next
> 
>        ' Split into second parts.
>        Parts = Split(Message, "{")
> 
>        If UBound(Parts) > 0 Then
>            ' Clean parts and append to array.
>            ReDim Preserve Tokens(1 To UBound(Tokens) + UBound(Parts))
>            For Token = 1 + LBound(Parts) To UBound(Parts)
>                Tokens(UBound(Tokens) - 1 + Token) = "{" & Split(Parts(Token), "}", 2)(0) & "}"
>            Next
>        End If
>        ' Return array with LBound = 1.
>    End If
> 
>    SplitTokens = Tokens
> 
> End Function
> </code>
> 
> /gustav
> 
> -----Oprindelig meddelelse-----
> Fra: AccessD [mailto:accessd-bounces at databaseadvisors.com] På vegne af Darren - Active Billing
> Sendt: 21. august 2015 02:35
> Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
> Emne: [AccessD] A2003 - Build a List/Array from Tokens in a blurb
> 
> Hi Team
> 
> if I had a field with the following blurb...
> 
> Dear [FirstName],
> Today is [NameOfDay] and the date is the [DateOfMonth] of [NameOfMonth].
> Kind Regards,
> {SignatoryName}
> 
> I can see in that blurb there are 5 tokens surrounded by Square and or Curley brackets.
> How could I run though that text and build a list of the 5 tokens such that I can produce a list/array like...
> 
> [FirstName]
> [NameOfDay] 
> [DateOfMonth]
> [NameOfMonth]
> {SignatoryName}
> 
> Many thanks in advance.
> 
> Darren
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com