problem using with statement in VBA RRS feed

  • Question

  • Can you explain me why the following codes behave in a different way?

    In the first sub instruction  .Cells(4, 5).Select seams to have no effect

    Sub provaOggetto()
     With ActiveCell
       MsgBox ("cella attiva: " & .Address)
       MsgBox ("la cella contiene: " & .Value)
       MsgBox ("la formula nella cella è: " & .Formula)
      .BorderAround xlDouble, xlThick, Color:=RGB(255, 255, 0)
      .Cells(4, 5).Select
       MsgBox ("cella attiva: " & .Address)
       .Value = 90
     End With
    End Sub

    Sub ppp1()
       MsgBox ("cella attiva: " & ActiveCell.Address)
       MsgBox ("la cella contiene: " & ActiveCell.Value)
       MsgBox ("la formula nella cella è: " & ActiveCell.Formula)
       ActiveCell.BorderAround xlDouble, xlThick, Color:=RGB(255, 255, 0)
       ActiveCell.Cells(4, 5).Select
    MsgBox ("cella attiva: " & ActiveCell.Address)
       ActiveCell.Value = 90

    Wednesday, December 21, 2016 4:27 PM

All replies

  • In provaOggetto, the line

         .Value = 90

    refers to the cell that was the active cell when the line

      With ActiveCell

    was executed, even though you activated a different cell in the meantime.

    In ppp1, you use

      ActiveCell.Value = 90

    Here, you refer to the cell that is the active cell when this line is executed, i.e. the "new" active cell.

    Regards, Hans Vogelaar (

    Wednesday, December 21, 2016 6:27 PM
  • Yes, but if I repeat the instruction

    MsgBox ("cella attiva: " & ActiveCell.Address)

    after End With I shall obtain another value, as an effect of instruction

    .Cells(4, 5).Select

    inside with.

    So, using the object ActiveCell I have a side effect of with statement.

    Is this reported? I always read that with statement is only a way to short code,

    never that it changes the operational semantics of VBA

    Thursday, December 22, 2016 3:48 AM
  • With Block helps you to write shorter code. When you work with same object repeatedly it helps you to reduce code by using a period only.

    It is logical that from 1st statement to last statement within with block you will need to refer same object. With..End With is not suitable if you change the very object.

    And in VBA most of cases you can avoid "Activate/Select"

    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Thursday, December 22, 2016 9:07 AM