1

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.

SweDentan
  • 67
  • 8

1 Answers1

3

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

Compile error: invalid in immediate pane

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:

Compile error: invalid watch expression

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".

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • Mathieu, Thank you for your well explained and comprehensive answer. Always good to have a deeper understanding of how the tool works and its purpose. – SweDentan Aug 22 '19 at 14:53
  • I find this crashes Excel: a.) create a sub, b.) enter "Set foo = new collection" (or presumably any object), c.) start debugging it, d.) execute "Set foo = nothing" in the immediate window. Not sure if that has anything to do with the concepts described here. – Mark E. Sep 05 '21 at 19:46