Execute With...End With Statement in Immediate window, Excel VBA

Question

I'm trying to execute With...End With Statement in the Immediate Window,

but I'm not getting it to work.

So my question is how do you execute multiple line of Code concerning With...End With Statement in the Immediate window?

My code I'm trying to execute in the Immediate window:

With Date_Per_Month.Range("A2:H32")
.Offset(1).Resize(.rows.Count - 1, .Columns.Count - 1).select
end with

Ofcourse it works to write a single line of code like this in the immediate window (but that doesn't answer the question).:

Date_Per_Month.Range("A2:H32").Offset(1).Resize _(Date_Per_Month.Range("A2:H32").Rows.Count-1,Date_Per_Month.Range("A2:H32").Columns.Count-1).Select

Tried to concatenate each code line with ":" at the end of lines but it didn't work.

Any help is highly appreciated.

Answer 1

The immediate toolwindow is essentially a console. You can use it to evaluate expressions without an execution context, or in break mode in the context of the current procedure.

Each "line" can be [almost] any executable instruction, but

You can assign to a variable that doesn't exist...

foo = 42

...and then this identifier exists in the "immediate" context and you can use it in subsequent statements:

?TypeName(foo)
Integer

...until you explicitly reset that context:

End
?TypeName(foo)
Empty

But you can't declare a variable:

Dim foo

A With statement is special: it witholds an object reference, but syntactically it's a block statement that needs to be terminated with an End With token: if you try to type With Sheet1 in the immediate pane, you'll get a compile error saying End With is missing - again because each statement in the immediate pane is a standalone instruction.

We could try to inline it:

With Sheet1 : .Cells(1, 1).Value = 42 : End With

But then we get a weird "invalid watch expression" error:

Regardless of the reason, like declaring variables with a Dim statement, defining a With variable makes no sense in the context of the immediate pane, where the next instruction to run is whatever instruction you happen to hit ENTER on: there's no scope, no sequence of operations - an instruction runs immediately, and that's all: whatever the contents of the immediate pane is, no other instruction will run until you hit ENTER on it.

how do you execute multiple lines of code [...] in the Immediate window?

The answer is, you don't - because the immediate toolwindow has no concept of "lines of code".