?
Solved

add dimension to array in vba

Posted on 2014-07-25
19
Medium Priority
?
524 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 2000 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
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
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
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
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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.
Suggested Courses

771 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