Replace (VBA) errors

[awsumchillicrab]

The replace function doesn't seem to work in this format:

Range("F2:F" & LastRow).Replace("#N/A", "")

But works in this:

Range("F2:F" & LastRow).Replace what:="#N/A", replacement:=""

where LastRow is already declared and found.
I was reading the intellisense and help, and don't understand why the first format isn't allowed, since only the first 2 arguments are required.

The error is that an = is expected.
If I use the below code, the bolded part gets highlighted with error "assignment to constant not allowed"

Range("F2:F" & LastRow).Replace("#N/A", "") = TRUE


What's up with this?

 

[Tom Urtis]

I for one do not understand your question. The syntax is the syntax and you are trying to use a syntax that violates VBA rules so an error is raised. It's no different than if you want to enter the word Hello into cell A1 like this which cannot be done:

Range("A1").Value ("Hello")

VBA is what it is, like any language it has its rules. Maybe you mean something else with the question, if so, please post back with what it is if I missed the point.

 

[Rick Rothstein]

The replace function doesn't seem to work in this format:

Range("F2:F" & LastRow).Replace("#N/A", "")

But works in this:

Range("F2:F" & LastRow).Replace what:="#N/A", replacement:=""

where LastRow is already declared and found.

The problem is the parentheses and the fact that there are more than one argument enclosed within them. And the problem is not exclusive to the Replace method. Try the following...

MsgBox "Hello"

and then this...

MsgBox ("Hello")

Both work fine, right? Okay, now try this...

MsgBox "Hello", vbExclamation

and then this...

MsgBox ("Hello", vbExclamation)

Only the first one worked this time. This is the same problem as you saw for the Replace method. The problem is the parentheses... you cannot just use them to "pretty" things up. Whenever you you parentheses to enclose something that are not required by the syntax to be enclosed in parentheses, VB treats those parentheses as marking an expression to be evaluated. When there is only one item inside the parentheses, VB evaluates that item as itself and there is no problem; however, if there are two (or more) comma separated items inside the parentheses, then VB does not know how to evaluate them as there is no expression operators separating the items, so VB throws an error.

 

[awsumchillicrab]

Isn't the parenthesis needed?

Intellisense only showed up when I keyed in the first parenthesis "(". I figured it must mean I'm doing the right thing. Just like how intellisense shows up after a period "." in the case of

Range("A1").

 

[Rick Rothstein]

Isn't the parenthesis needed?

Based on your own statement...

But works in this:

Range("F2:F" & LastRow).Replace what:="#N/A", replacement:=""

I'd say obviously not.

Intellisense only showed up when I keyed in the first parenthesis "(". I figured it must mean I'm doing the right thing.

Intellisense would have shown up if you had typed a space instead of "(" as well. Let me add a little to this example that I posted...

MsgBox ("Hello", vbExclamation)

That didn't work because we used MsgBox as if it were a subroutine, but MsgBox is actually a function. and if we used it as such, then the parentheses would have been required (by syntax).

ReturnValue = MsgBox("Hello", vbExclamation)

The problem stems, I think, from the looseness with which VB lets the programmer handle functions... any function can be called as if it were a subroutine... the return value is simply "lost" because it isn't assigned or used anywhere. Now, back to the syntax of a subroutine. Let's say you have a subroutine declared like this...

Sub MySubroutine(Argument1, Argument2)

There are two syntactically correct ways to call this subroutine. Either this way...

MySubroutine Variable1, Variable2

or this way...

Call MySubroutine(Variable1, Variable2)

The first method does not use parentheses (for the reasons I mentioned in my first posting) whereas the second method absolutely requires them. Now these examples are a little bit different than for Replace because Replace is a method as opposed to a "true" function; but, in actuallity a method is a function wearing different clothing (it has to do with Class definitions I believe), so all of what I discussed should apply.

 

[mikerickson]

Both of these lines of code execute an instruction

MsgBox "Hello"
MsgBox "hello", vbOKOnly

However,

 userButton = MsgBox("hello", vbYesNo)

returns a value, hence the functional notation.
Similarly

Call MsgBox("hello"): Rem works
Call MsgBox "hello": Rem errors

That's why one needs parenthesis for a multi-button message box.

 

[Rick Rothstein]

I think we are saying the same thing using different words... my words were use parentheses when required by the syntax and don't be thrown by the tolerated parentheses when only a single argument is passed.

Both of these lines of code execute an instruction

MsgBox "Hello"
MsgBox "hello", vbOKOnly

Don't forget...

MsgBox ("Hello")