Solved

add dimension to array in vba

Posted on 2014-07-25
19
496 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 48

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 48

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 48

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 48

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

696 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