`Nothing` in macro crashes Excel 2013

Question

I'm trying to use RegEx in an Excel 2015 macro. I don't know if I'm doing something wrong, but every time I run it, my Excel crashes. Here's the macro:

Sub MakeExplicit()

    Dim whitespace As RegExp
    Set whitespace = New RegExp
    whitespace.Pattern = "\s+"
    whitespace.MultiLine = True
    whitespace.Global = True

    Dim implicit As RegExp
    Set implicit = New RegExp
    implicit.Pattern = "^\d+-\d+$"

    Dim row As range

    For Each row In ActiveSheet.UsedRange.Rows

        Dim first As range
        Set first = row.Cells(1, 1)

        Dim str As String
        str = first.Text
        str = whitespace.Replace(str, Nothing)

        If implicit.Test(str) Then 'FIXME here it crashes

            Dim FromTo As Variant
            FromTo = Split(str, "-")

            Dim sFrom, sTo As 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 As Integer
            iFrom = CInt(sFrom)
            iTo = CInt(sTo)

            If iFrom > iTo Then _
                Err.Raise 42, first.Address, _
                "Wrong order of numbers!"


            Dim i As Integer
            For i = iFrom To iTo
                ' some more code
            Next i

        End If

    Next row

End Sub

By using the Debugger I found out it crashes when the code reaches "If implicit.Test(str) Then" implying there's something wrong with RegEx. These are the project's references:

The obvious question is how do I get it to work? VBA is a very ugly language by itself, so I have no preference about how, just making it work is enough.

jsotola · Answer 1 · 2017-08-18T16:37:34.453

3

this is the line that crashes

str = whitespace.Replace(str, Nothing)

Nothing is used for destroying objects ... set object = nothing

use instead

str = whitespace.Replace(str, "")

or, as per Mat's Mug

str = whitespace.Replace(str, vbNullString)    ' uses less memory and is more readable

edited Aug 18 '17 at 16:37

answered Aug 18 '17 at 00:30

jsotola

2,238
1
10
22

That. Except I'd use `vbNullString` instead, for a zero-byte footprint (vs 2 bytes for the empty string literal). And because I just love using built-in constants over magic literals :-) – Mathieu Guindon Aug 18 '17 at 02:39
`Nothing` is also (perhaps even more often) used for *testing* if an expression refers to an actual object reference: `If Not Range.Find("Foo", Sheet1.UsedRange) Is Nothing Then`. – Mathieu Guindon Aug 18 '17 at 02:42
Ouch, that's why I shouldn't trust uncited sources. This [SO answer](https://stackoverflow.com/a/34069187/1137334) mislead me into thinking that `vbNullString` and `Nothing` are the same. And as a VBA newbie, I liked the sound of `Nothing` more ;) – m93a Aug 18 '17 at 11:03

Domenic · Accepted Answer · 2017-08-18T03:38:11.540

2

A few things...

1) The line If implicit.Test(str) Then should not cause an error.

2) To replace one or more spaces with no spaces, use "" instead of Nothing...

str = whitespace.Replace(str, "")

3) Since the Split function returns a 0-based array, use...

sFrom = FromTo(0)
sTo = FromTo(1)

4) To concatenate, use the ampersand (&) instead of the plus sign (+)...

    sTo = Left( _
        sFrom, _
        Len(sFrom) - Len(sTo) _
    ) & sTo

Hope this helps!

edited Aug 18 '17 at 03:38

answered Aug 18 '17 at 00:31

Domenic

7,844
2
9
17

2

i tried it before my answer. trying to insert `Nothing` into a string crashes Excel 2016 – jsotola Aug 18 '17 at 00:37
Upvoted for all but the first point... `Nothing` is a null *object reference*, not a `String`. VBA strings aren't objects, passing `Nothing` to a method that works with *values* (not *references*), is *certifiably* going up in flames. – Mathieu Guindon Aug 18 '17 at 02:49
Actually, my first point referred to the line `If implicit.Test(str) Then` since the OP said that it caused an error. I've edited my post to clarify. Thanks! – Domenic Aug 18 '17 at 03:33

`Nothing` in macro crashes Excel 2013

2 Answers2