Help with arrays

[Mcgrco]

I'm trying to learn how to use arrays and I having some difficulty.

What I'm trying to do is split a line of text in to individual elements separated by a space.

eg the function call would be strSplitField("the man is walking down the" ,3) As String. this would return "is"

The first part of the function counts the numbers of spaces, in this case 5

Using this im trying to dim an array = to the number of spaces +1. this will act as a container for each word.

It works if I dim the array with a static number but not with the redim function.

Can anyone spot what Im doing wrong. thanks

See my code attached


Option Compare Database
Option Explicit
Global strContainer()
Options Base 1
Public Function strSplitField(strField As String, iSplitno As Integer) As String
'*******************************************
'Name: tester (Function)
'Purpose:
'Author: Mcgrco
'Date: October 14, 2002, 11:55:16 AM
'Called by:
'Calls:
'Inputs:
'Output:
'*******************************************
On Error GoTo ErrHandler

Dim iStart As Integer
Dim iNext As Integer
Dim DelimCount As Integer
Dim i As Integer
Dim NextField As String
Dim x

iStart = InStr(strField, " ")
If iStart = 0 Then
DelimCount = 1
Else
DelimCount = 0

Do
iNext = InStr(iStart + 1, strField, " ")
If iNext <> 0 Then iStart = iNext
DelimCount = Trim(DelimCount) + 1
Loop Until iNext = 0
End If
Debug.Print DelimCount 'Count the number of delimeters in the record

ReDim strContainer(1, DelimCount + 1)

i = 1
Do While i <= DelimCount + 1
If InStr(1, strField, " ") = 0 Then
strContainer(i) = strField
Else
x = Mid(strField, 1, InStr(1, strField, " ") - 1)

strContainer(i) = x ' Load the array

NextField = Trim$(Mid(strField, Len(strContainer(i)) + 1))
If NextField <> " " Then strField = NextField
End If

i = i + 1
Loop

Select Case iSplitno

Case 1 To DelimCount
strSplitField = IIf(IsNull(strContainer(iSplitno)), " ", strContainer(iSplitno))
End Select


ExitHere:

Exit Function
ErrHandler:
Dim strErrString As String
strErrString = "Error Information..." & vbCrLf
strErrString = strErrString & "Error#: " & Err.Number
strErrString = strErrString & "Description: " & Err.Description
MsgBox strErrString, vbCritical + vbOKOnly, "Function: tester"
Resume ExitHere
End Function

 

[pdx_man]

Don't make the task more complicated than you have to ...

More will follow ...

 

[Drevlin]

You have three problems. The first problem is you have your declaration of your array before you set the Base:

Global strContainer()
Options Base 1

This won't work because you've already set an array and VBA won't allow you to ReDim it in this fashion.

The second fix is: you need to drop the s off Options:
Option Base 1
Global strContainer()

The third fix is the ReDim, the way you have it now you are ReDimming the array to 2 Dimensions when you just want to redim the array from 1 to your number:

ReDim strContainer(1 to DelimCount + 1)

With these fixes the function seems to work fine.

Peace

 

[Mcgrco]

Thanks

I was copying some other persons methods and I incorrectly assumed that ReDim strContainer(1,DelimCount + 1) told the array to begin at 1.
The problem with the Options Base 1 was something I added to the web code just to highlight that I had tryed it also. Leaving the placement of the Options Base 1. This was something I would have never spotted so thanks. It now works

Im would be interested in any advice pdx_man might offer. Its my first time using an array.

 

[pdx_man]

Unless this is a class assignment where you must use arrays ...

Public Function GetWord(MyIn As String, WhichOne As Integer) As String
Dim i As Integer
Dim HoldStr As String
On Error GoTo HandleIt

    Do While i <> (WhichOne - 1) And InStr(MyIn, " ")
        MyIn = Mid(MyIn, InStr(MyIn, " ") + 1)
        i = i + 1
    Loop
   GetWord = Left(MyIn, InStr(MyIn, " ") - 1)
Exit Function

HandleIt:
    If Err.Number = 5 Then
        If i = WhichOne - 1 Then
            GetWord = MyIn
        Else
            MsgBox "Not enough words to return the requested position"
            GetWord = -1
        End If
    Else
        GetWord = -1
    End If
    
End Function


Public Sub testmyin()

    MsgBox GetWord("the man is walking down the", 2)
    
End Sub

Uses less memory and processing

 

[Mcgrco]

Easy when you know how. eh

Thanks, that looks like a lot less hassel

 

[pdx_man]

Yeah, there's no sense in processing 50 words when you are looking for the 5th word ... Grab the 5th word and then end it.