Solved

add dimension to array in vba

Posted on 2014-07-25
19
419 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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

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.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

823 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