[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

how do I do string processing with a variant array?

Posted on 1999-11-15
25
Medium Priority
?
253 Views
Last Modified: 2010-05-02
sample program: "I have created a sample application to demonstrate the problem.  I create two text boxes and one command button, whatever I enter into the first text box and press the command button, that text gets printed in the second text box.  I split the words from the INPUT textbox and place them in a variant array using the split() command.

I want to know how to automatically determine how many words are split up and placed into different elements in the array so that I can automatically access those elements and assign them into a string variable dynamically.  That is, I do not want my code to be hardset to read 5 elements of the array.  If I enter 5 words and hit the command button, then it works fine. but if I enter only 3 words, then my code is still expecting the 4th and 5th elements of the array.

Here is the sample code below:
---------------------------------------


Dim PrimaryInput As String
Dim word1 As String
Dim operator1 As String
Dim word2 As String
Dim operator2 As String
Dim word3 As String
Dim sArray As Variant


Private Sub cmdProcess_Click()


PrimaryInput = Text1.Text

sArray = Split(PrimaryInput)

word1 = sArray(0)
operator1 = sArray(1)
word2 = sArray(2)
operator2 = sArray(3)
word3 = sArray(4)

Text2.Text = word1 + " " + operator1 + " " + word2 + " " + operator2 + " " + word3

End Sub
0
Comment
Question by:jawad_hussain
  • 11
  • 10
  • 3
  • +1
25 Comments
 
LVL 7

Expert Comment

by:Vbmaster
ID: 2208727
After the Split command check the Ubound and LBound values of the sArray array.. the number of words are Ubound(sArray) + 1

You can iterate thru all the items in the array with code like this..

Dim a As Integer

For a = 0 to Ubound(sArray)
  Oneword = sArray(a)
Next
0
 
LVL 32

Expert Comment

by:Erick37
ID: 2208731
UBound(sArray) will return the upper index of the array, so you then know its size.
0
 

Author Comment

by:jawad_hussain
ID: 2208741
VBMASTER!

Thanks for the info, but can you give me exact code to demonstrate this?

I would like that sample application to behave the way you suggest but without me putting word1 = sArray(0), operator1 = sArray(1), etc. (hardset, etc.)

I would like the code to automatically assign the first element of the array to word1, the second element of the array to operator1, etc.

and print it to the second text box.

note: wow, that was a really quick response man, you do a great job!
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 7

Expert Comment

by:Vbmaster
ID: 2208742
You will have to see what result is given if there is only one word. does that return a variant array or only a variant value? put one word in the textbox and put the code

Me.Caption = IsArray(sArray)

after the Split command, if this is True it's a array but if you get a False value you will have to explicitly check if it's a array or not, let me know what the result is (I run VB5 and can't use the Split command myself)
0
 
LVL 7

Expert Comment

by:Vbmaster
ID: 2208753
Try this..

Dim sResult As String

sResult = ""
For a = 0 To Ubound(sArray) Step 2
  sResult = sResult & sArray(a) & " " & sArray(a+1) & " "
Next

Text2.Text = Left$(sResult, Len(sResult) - 1)

0
 

Author Comment

by:jawad_hussain
ID: 2208764
The first code received a TRUE.

I tried your code, but with 4 words, it works fine, but with 3 words, i get a subscript out of range error.

Please let me know.

I am going on lunch, will reply to your next message in an hour, thanks for the help so far!!
0
 
LVL 7

Expert Comment

by:Vbmaster
ID: 2208801
I guess I was too fast replying on that one. This works for your expressions.

  Dim sResult As String
  Dim a As Integer

  sResult = ""
  For a = 0 To (UBound(sArray) - 1) Step 2
    sResult = sResult & sArray(a) & " " & sArray(a + 1) & " "
  Next
  sResult = sResult & sArray(a)

  Text2.Text = sResult
0
 

Author Comment

by:jawad_hussain
ID: 2208988
OK GREAT!!!

that works VbMaster!

Now I got another question in regards to this, I will either give you the 100 points for that or you can answer my next question for 200 points... up to you, let me know:

Is it possible for me to place each word in a particular string for processing later?

like individual strings hold one word from what I type into the first textbox.

like:   windows and version not level

therefore: "windows" would be put into string (word1), "and" would be put into string (operator1), "version" would be put into "word2", etc. but dynamically done so that I can use the strings for manipulation later in an sql query.

any ideas?
0
 
LVL 12

Expert Comment

by:mark2150
ID: 2209021
You want dynamic, you gotta use arrays. You can resize an array with REDIM PRESERVE. This allows you to keep the existing values as you add new ones.

You DIM the array in the (General) section but do not specify a size, only a type:

DIM MyArray() AS STRING

The array will always have a 0th element.

When you add elements:

NumElements = NumElements + 1
REDIM PRESERVE MyArray( NumElements )

You can make your loops adaptive:

FOR Ndx = 0 to UBOUND(MyArray) - 1
....
NEXT Ndx

If you insist on having names for array elements you can code:

CONST Word1 AS INTEGER = 0
CONST Operator1 AS INTEGER = 1
CONST Word2 AS INTEGER = 2

Then you can refer to an element in your array:

MyArray(Word1) = "windows"
MyArray(Operator) = "and"
MyArray(Word2) = "version"

SQL = "SELECT FROM MyTable WHERE Word1='" & MyArray(Word1) & "'"

Simple enough, no?

M
0
 

Author Comment

by:jawad_hussain
ID: 2209042
Mark2150:

hmm...

can you provide me with code that goes along with the code I provided above?

I don't need the SQL query statement, i'll do that myself because my sql query statement is very long and crazy.

If you can give me code that automatically figures out how many words i entered into the PrimaryInput string, associate each word into an element of the array, then dump elements from the array into individual strings like I mentioned, and THEN, USING the STRINGS, output to the second text box...

Is this possible?  has to work with whatever number of words are entered into PrimaryInput.
0
 
LVL 12

Expert Comment

by:mark2150
ID: 2209075
Oh is not that hard to do. Mostly you're looking for spaces.

' the following in (General) section
'
DIM MyArray() as string

CALL MakeWords("This is a phrase")

PRIVATE SUB MakeWords( Txt as String )
DIM Work as string
DIM Marker as Integer
DIM Ndx as Integer
ndx = 0
redim MyArray(ndx)
work = trim(txt)
do while instr( work, " " ) > 0
  marker = instr( work, " " )
  MyArray(ndx) = left(work, marker -1)
  ndx = ndx + 1
  REDIM PRESERVE MyArray( ndx )
  work = trim( right( work, len(work) - marker ) )
Loop
'
if len(work) > 0 then
  MyArray(ndx) = work
  ndx = ndx + 1
  REDIM PRESERVE MyArray( ndx )
endif

debug.print "There were" & ubound(myarray) & " words entered"
'
' Array bound is one more than last valid index
' To list all words use:
'
work = ""
for ndx = 0 to ubound(MyArray)
  work = work & MyArray(ndx) & " "
next ndx
'
debug.print trim(work)
'
END Sub

Anyway that's pretty close of the top of my head.

M



 
0
 

Author Comment

by:jawad_hussain
ID: 2209095
Mark2150:

Thanks for the quick reply.  but the code you just showed me above only outputs what I typed.  I do not understand how each word is associated to a unique string like word1, operator1, word2, operator2, etc.

How can I call the appropriate word for an sql query like this (just an example):

select * from windows
where problem like '%word1%' operator1 problem like '%word2%' operator2 problem like '%word3%'

do you see what I mean now?
0
 
LVL 7

Expert Comment

by:Vbmaster
ID: 2209125
I thought the problem was not to separate words in the string, instead you wanted the words and operators in separate places. Might be me misunderstanding it but here's some code that will put all the words in a array called sWords and a array called sOperators (0-based both of 'em)..

  Dim sOperators() As String
  Dim sWords() As String
  Dim sResult As String
  Dim sArray As Variant
  Dim a As Integer
 
  sResult = ""
  ReDim sWords(UBound(sArray) \ 2)
  ReDim sOperators(UBound(sArray) \ 2 - 1)
  For a = 0 To (UBound(sArray) - 1) Step 2
    sWords(a \ 2) = sArray(a)
    sOperators(a \ 2) = sArray(a + 1)
    sResult = sResult & sWords(a \ 2) & " " & sOperators(a \ 2) & " "
  Next
  sWords(a \ 2) = sArray(a)
  sResult = sResult & sWords(a \ 2)

  Text2.Text = sResult
0
 
LVL 7

Expert Comment

by:Vbmaster
ID: 2209146
Aargh you will need to test to see if the number of words in the string is zero or none...

  ReDim sWords(UBound(sArray) \ 2)

  If (Ubound(sArray) > 0) Then
    ReDim sOperators(UBound(sArray) \ 2 - 1)
    .
    Next
  End If

But this means that if you try to access the sOperators array you will get a subscript out of range, so a good idea is to store how big the sOperators array is (= Ubound(sArray)\2).
0
 

Author Comment

by:jawad_hussain
ID: 2209165
I'm sorry for my inability to understand.. but how do I call a particular string in accordance to the sql query that I want to use?

like How i explained above to Mark2150:

--------------------------------------

Mark2150:

Thanks for the quick reply.  but the code you just showed me above only outputs what I typed.  I do not understand how each word is associated to a unique string like word1, operator1, word2, operator2, etc.

How can I call the appropriate word for an sql query like this (just an example):

select * from windows
where problem like '%word1%' operator1 problem like '%word2%' operator2 problem like '%word3%'

do you see what I mean now?

--------------------------------------

I've increased the points to 200.  I don't know if want I want is possible.

:(
0
 
LVL 7

Expert Comment

by:Vbmaster
ID: 2209181
Why do you 'want' to use word1..word2..word3, if you store the words in a array you will get instant access to them using code like sWords(what_ever_index_you_want)   <- isn't this much much easier and more customizable?
0
 

Author Comment

by:jawad_hussain
ID: 2209198
umm, ok, that's fine.

so from scratch, can you show me how to do this?

you can use the two text boxes and the command button.

output should be like this then, I'm taking into account of my present method of recalling the words from the strings.

"select * from windows where problem like '%" & word1 & "%' " & operator1 & " problem like '%" & word2 & "%'"

etc....

0
 
LVL 7

Accepted Solution

by:
Vbmaster earned 800 total points
ID: 2209241
Have no clue what you are doing but if i follow that system if you use arrays you will do something like

  Dim sSQL As String
 
  sSQL = "select * from windows where"
  For a = 0 To (UBound(sWords) - 1)
    sSQL = sSQL & " problem like '%" & sWords(a) & "%' " & sOperators(a)
  Next
  sSQL = sSQL & " problem like '%" & sWords(a) & "%'"
 
How about that?
0
 

Author Comment

by:jawad_hussain
ID: 2209289
You totally rock dude!!

that works perfectly!!!!!!!!!!!!!!!
0
 

Author Comment

by:jawad_hussain
ID: 2209335
VBMASTER:

Is there anyway that I can get the above program to accept double words and if the word is an operator like (and, not, or) then dump the double word as an array in the sWords and if it's an operator, then dump the word into the sOperators string?

like:


windows 95 and display

windows 95 would be one word placed in sWords and the "and" would be an operator placed in sOperators and the "display" would be another word placed in sWords?
0
 
LVL 7

Expert Comment

by:Vbmaster
ID: 2209681
Use this code.. this will work just your earlier code but it will check for every word if it's a AND, OR, if this is the case it will take the word and put it in the operator array, otherwhise it will put it in the word array. Hopefully you do not have words that are called 'AND' or 'OR'? ;)



  Dim sOperators() As String
  Dim WordCount As Integer
  Dim OpCount As Integer
  Dim sWords() As String
  Dim sResult As String
  Dim sArray As Variant
  Dim a As Integer
 
  sResult = ""
 
  If (UBound(sArray) = 0) Then
    WordCount = 1
    ReDim sWords(WordCount - 1)
    sWords(WordCount - 1) = sArray(0)
    sResult = sArray(0)
  Else
    WordCount = 1
    ReDim sWords(WordCount - 1)
    Do Until (a > UBound(sArray))
      sResult = sResult & sArray(a)
      Select Case UCase$(sArray(a))
      Case "AND", "OR"
        OpCount = OpCount + 1
        ReDim Preserve sOperators(OpCount - 1)
        sOperators(OpCount - 1) = sArray(a)
        WordCount = WordCount + 1
        ReDim Preserve sWords(WordCount - 1)
      Case Else
        If (Len(sWords(WordCount - 1)) > 0) Then
          sWords(WordCount - 1) = sWords(WordCount - 1) & " " & sArray(a)
        Else
          sWords(WordCount - 1) = sArray(a)
        End If
      End Select
      a = a + 1
    Loop
  End If

 
  Dim sSQL As String
 
  sSQL = "select * from windows where"
  For a = 0 To (WordCount - 2)
    sSQL = sSQL & " problem like '%" & sWords(a) & "%' " & sOperators(a)
  Next
  sSQL = sSQL & " problem like '%" & sWords(a) & "%'"
 
0
 

Author Comment

by:jawad_hussain
ID: 2209730
Ok one problem with that.

It works perfectly fine the first time around, but If I hit the button again with the same input, it erases one of the words from the array or something.

Here is an example:

first time around entering:

1) windows 95 and display properties:

output: select * from windows where problem like '%windows 95%' and problem like '%display properties%'

2) windows 95 and display properties, again (without closing the program or entering anything in the input box that isn't already there, and pressing the PROCESS button):

output: select * from windows where problem like '%95%' and problem like '%display properties%'


See how it removed the first word from the first double word?


0
 
LVL 7

Expert Comment

by:Vbmaster
ID: 2209743
When i tested it I ran all the variables as local, maybe you have some private/public ones, try to reset all the variables before you do anything. Add these lines where you have the line 'sResult = ""' in the start of the procedure and it ought to work.

  Erase sOperators
  Erase sWords
  WordCount = 0
  OpCount = 0
  a = 0

0
 
LVL 12

Expert Comment

by:mark2150
ID: 2210873
jawad,

You must not have understood how my code snippet worked. It took your string, chopped it up into individual array elements, and then to confirm how it worked, reassembled them back into the original string.

M
0
 

Author Comment

by:jawad_hussain
ID: 2211446
VBMaster:  works like a charm now, I'm going to the other question and accepting your answer there.

Mark2150:  Thanks for all your help dude, VBMaster's code was a little easier for me to understand, I apologize if I did not accept your answer.
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

607 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