Solved

add dimension to array in vba

Posted on 2014-07-25
19
472 Views
Last Modified: 2014-07-29
I have created an array by using the split function.

ClientWords = Split(Trim(txt))

I would like to do a redim preserve to add an extra element so I can mark a specific word in the array.

How do i do that

I tried ReDim Preserve ClientWords(UBound(ClientWords),0)

but i get subscript out of range
0
Comment
Question by:PeterBaileyUk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
  • +1
19 Comments
 
LVL 13

Expert Comment

by:duncanb7
ID: 40219046
Is it what you want for redim ?

Hope understand your question.If not, pls pt it out

Duncan
Sub Test()
Dim ClientWords() As String
ClientWords() = Split("How are you")
'Debug.Print ClientWords(1)
ReDim Preserve ClientWords(Application.CountA(ClientWords))
ClientWords(Application.CountA(ClientWords) - 1) = "Today"
Debug.Print ClientWords(3)
End Sub

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 40219060
vba gives compile error method or data member not found on counta

I have
207
s16
1.6
[120]

I want it to go through a record set and if it finds the current word clientwords(1) s16 in this case it will tag 16 so i can use that later.
0
 

Author Comment

by:PeterBaileyUk
ID: 40219061
heres my code so far its just a question of tagging i thought adding an extra element would allow that

    ClientWords = Split(Trim(txt))
   
    
    For x = LBound(ClientWords) To UBound(ClientWords) Step 1

        With rstWordExceptions
    
            .MoveFirst
    
            'cycle through the query grabbing strings
             Do Until .EOF
                    If ClientWords(x) = rstWordExceptions.Fields("sdesc").Value Then
            
            
            
                    Else
            
            
            
                    End If
        
    
    
    
                    rstWordExceptions.MoveNext
            Loop

        End With


    Next x 'loop to next word in array

Open in new window

0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 13

Expert Comment

by:duncanb7
ID: 40219063
try this with UBound
Sub Test()
Dim ClientWords() As String
ClientWords() = Split("How are you")
Debug.Print UBound(ClientWords, 1)
ReDim Preserve ClientWords(UBound(ClientWords, 1) + 1)
Debug.Print UBound(ClientWords, 1)
ClientWords(UBound(ClientWords, 1)) = "Today"
Debug.Print ClientWords(UBound(ClientWords, 1))
End Sub

Open in new window

0
 
LVL 12

Accepted Solution

by:
James Elliott earned 500 total points
ID: 40219078
See if this helps at all:

Sub SplitWithTag()

Dim arr As Variant
Dim arrTag As Variant
Dim x As Long

arr = Split(Range("A1"))

ReDim arrTag(UBound(arr), 1)

For x = LBound(arr) To UBound(arr)

    arrTag(x, 0) = arr(x)
    arrTag(x, 1) = "Your tag"

Next x

End Sub

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 40219081
I must have said it wrong the split creates the array fine I need to add another dimension so I can flag them
0
 

Author Comment

by:PeterBaileyUk
ID: 40219084
our responses are crossing just seen 40219078
0
 

Author Closing Comment

by:PeterBaileyUk
ID: 40219095
thank you
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40219097
Dear Author,

Is the code matched to your question post?

Please advise

Duncan
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40219110
I don't believe you can use redim to change the number of dimensions in an array.

You could write your own code to do this with a 2 dimensional array

    Dim xWords() As String
    Dim ClientWords() As Variant

    xWords = Split("Your text string goes here")
    ReDim ClientWords(UBound(xWords), 1)
    For x = LBound(xWords) To UBound(xWords)
        ClientWords(x, 0) = xWords(x)
    Next
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40219113
Peter,

I'm confused.  You posted to an Access topic area, yet accepted an Excel solution?
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40219121
Dear Author,
I would like to do a redim preserve to add an extra element so I can mark a specific word in the array

from your post , it seems you want add one more cell  into array.
but you want multi-dimension array,

Thanks for your reply

Duncan
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40219131
fyed,

what, aside from my testing my code in Excel and forgetting to take out the 'range' reference, makes it an "Excel" solution?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40219154
jell,

nothing.  I focused in on "Range".  After I posted the comment I realized you had basically recommended the same thing that I did, use a second array.
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40223991
Maybe my interpretation of the question would help:

The OP wanted to be able to tag certain words in his one-dimensional array, created using the split function.

I provided a solution which copied the 1-d array to a 2-d array, so that he could place a tag (in the second dimension) next to each word if he wished to.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40224166
Nope, Jell has a good working solution that works in Access VBA.
0
 

Author Comment

by:PeterBaileyUk
ID: 40224657
It seems you all answered your own things, the solution provided by Jell at that time was the best solution, I believe I am always fair when dishing out points (I think fyed would agree) if I have two that work then I usually share but in this case Jell understood perfectly the requirement. Hence the full points. In fact I had already posted the first part hence I ignored the "range" it was the addition of the second array that completed the task.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question