CODEWITHSUNDEEP
vbaexcelvbscript
DoubleUB
DoubleUB

Reputation: 21

Trouble with VBS InStr - using a nested InStr as the optional start argument

Good morn all...I need some help porting a function from VBA to VBS

Here is an example of the data set my question is based on. 

USA_66544_File.doc
RUS_3465_File.txt

I used the function to extract the string of numbers between the two underscores. In case it matters, i started off by breaking the string into segments, based on the underscore, then patched this together to isolate just the string i wanted.

=MID(A5,FIND("_",A5)+1,FIND("_",A5,FIND("_",A5)+1)-FIND("_",A5)-1) '##original working in Excel

The output from the above would be 66544 and 3465 respectively.

I then needed to port this function to VBS. MID is ok, but FIND changes to InStr. Also, my cell reference (A5) changes to a 2D array of size array(1,7) 

MID(array(0,x),InStr(array(0,x),"_")+1,InStr(InStr(array(0,x),"_")+1),array(0,x),"_")'-(Instr(array(0,x),"_")-1)

I am getting the error "Wrong number of arguments or invalid property assignment".

Here is what i think i am doing. MID accepts three arguments, with the last being optional. 

MID(string, start, [# of char to return]
  1. string = array(0,x)
  2. start = InStr(array(0,x),"_")+1 (this should find an underscore, then return the next number)
  3. length = Instr(Instr(array(0,x),""),array(0,x),"")-Instr(array(0,x),"_")-1

So to bring it all together in case it matters, in my 2D array, the first dimension will contain the full string

USA_66544_File.doc
RUS_3465_File.txt

And the second, the output from the function

66544
3465

I anyone can help me make sense of this, I'd greatly appreciate it. Thanks, Herb

Upvotes: 2

Views: 727

Answers (1)

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38745

The easy solution is using Split():

>> Dim array(0,0)
>> array(0,0) = "USA_66544_File.doc"
>> WScript.Echo array(0,0), Split(array(0,0), "_")(1)
>>
USA_66544_File.doc 66544

I used the variable name array in this demo, because I want to make sure that re/mis-using the name of the Array() function does not throw an error. Still choosing a better name is advisable.

If you want to use Mid() and Instr(), do it step by step:

>> s = "USA_66544_File.doc"
>> f = InStr(1, s, "_") + 1
>> t = InStr(f, s, "_")
>> l = t - f
>> WScript.Echo Mid(s, f, l)
>>
66544

After having done it correctly, optimizing is easy:

>> s = "USA_66544_File.doc"
>> f = InStr(1, s, "_") + 1
>> WScript.Echo Mid(s, f, InStr(f, s, "_") - f)
>>
66544

Inlining f (i.e. repeating the expression) would surely not be an optimization, especially if you consider the need to add some error checks.

Upvotes: 1

Related Questions