regex - `Nothing` in macro crashes Excel 2013 -
i'm trying use regex in excel 2015 macro. don't know if i'm doing wrong, every time run it, excel crashes. here's macro:
sub makeexplicit() dim whitespace regexp set whitespace = new regexp whitespace.pattern = "\s+" whitespace.multiline = true whitespace.global = true dim implicit regexp set implicit = new regexp implicit.pattern = "^\d+-\d+$" dim row range each row in activesheet.usedrange.rows dim first range set first = row.cells(1, 1) dim str string str = first.text str = whitespace.replace(str, nothing) if implicit.test(str) 'fixme here crashes dim fromto variant fromto = split(str, "-") dim sfrom, sto integer sfrom = fromto(1) sto = fromto(2) ' doplň chybějící číslice ' např [2345, 78] doplní ' na [2345, 2378] sto = left( _ sfrom, _ len(sfrom) - len(sto) _ ) + sto dim ifrom, ito integer ifrom = cint(sfrom) ito = cint(sto) if ifrom > ito _ err.raise 42, first.address, _ "wrong order of numbers!" dim integer = ifrom ito ' more code next end if next row end sub
by using debugger found out crashes when code reaches "if implicit.test(str) then
" implying there's wrong regex. these project's references:
the obvious question how work? vba ugly language itself, have no preference how, making work enough.
a few things...
1) line if implicit.test(str) then
should not cause error.
2) replace 1 or more spaces no spaces, use ""
instead of nothing
...
str = whitespace.replace(str, "")
3) since split function returns 0-based array, use...
sfrom = fromto(0) sto = fromto(1)
4) concatenate, use ampersand (&) instead of plus sign (+)...
sto = left( _ sfrom, _ len(sfrom) - len(sto) _ ) & sto
hope helps!
Comments
Post a Comment