+ Reply to Thread
Results 1 to 8 of 8

Stepping through Code

  1. #1
    ben
    Guest

    Stepping through Code

    Have a code that upon a worksheet_change event creates an AutoShape object
    and inserts onto the Worksheet at a specific Location. The problem is the
    code ONLY works in Step-Mode, IF I break before the object is created and
    then step through it. If I let the code run without breaking it always
    returns an 1004 - application or object defined error,
    when it attempts to create the AutoShape.
    Why would it work in Step-Mode and not in Normal RunTime?
    Nothing else changes, no code, no procedures, no events, no workbooks.

    Ben
    --
    When you lose your mind, you free your life.

  2. #2
    Tom Ogilvy
    Guest

    Re: Stepping through Code

    Sounds like you are doing something on a sheet other than the sheet where
    the change event occured.

    If this is the case, unqualified references such as

    Range("B9").Select
    would refer to the worksheet that contains the code, not the active sheet if
    that were a different sheet. In that type of situation, you would be
    intending to select on the activesheet, but the code thinks you want to
    select on the worksheet that contains the code. You can't select on a sheet
    unless it is the activesheet.

    So for example

    In the Sheet2 code module code like

    Private Sub Worksheet_Change(ByVal Target As Range)

    worksheets("Sheet1").Activate
    Range("B9").Select '<== 1004 error here


    End Sub

    better (don't select, but)

    Private Sub Worksheet_Change(ByVal Target As Range)
    with worksheets("Sheet1")
    .Activate
    .Range("B9").Select
    End with
    End Sub

    If this isn't exactly what you are doing, I will bet it is close and the
    source of your problem.

    --
    Regards,
    Tom Ogilvy


    "ben" <[email protected](remove this if mailing direct)> wrote in message
    news:[email protected]...
    > Have a code that upon a worksheet_change event creates an AutoShape object
    > and inserts onto the Worksheet at a specific Location. The problem is the
    > code ONLY works in Step-Mode, IF I break before the object is created and
    > then step through it. If I let the code run without breaking it always
    > returns an 1004 - application or object defined error,
    > when it attempts to create the AutoShape.
    > Why would it work in Step-Mode and not in Normal RunTime?
    > Nothing else changes, no code, no procedures, no events, no workbooks.
    >
    > Ben
    > --
    > When you lose your mind, you free your life.




  3. #3
    ben
    Guest

    Re: Stepping through Code

    there is only one sheet in the workbook being referenced, and this code is
    running from an add-on where ALL references are qualified

    the actuall line that errors out is

    Set sh = wks.Shapes.AddShape(msoShapeDownArrow, 354.75, 162 + (RowN * 21),
    24, 30)


    sh is a public shared variable referring to a Shape
    and
    wks is a worksheet object that is passed into the sub and is the same
    whether stepping through or at Run Time

    Sub AddShape(RowN As Integer, wks As Worksheet)



    --
    When you lose your mind, you free your life.


    "Tom Ogilvy" wrote:

    > Sounds like you are doing something on a sheet other than the sheet where
    > the change event occured.
    >
    > If this is the case, unqualified references such as
    >
    > Range("B9").Select
    > would refer to the worksheet that contains the code, not the active sheet if
    > that were a different sheet. In that type of situation, you would be
    > intending to select on the activesheet, but the code thinks you want to
    > select on the worksheet that contains the code. You can't select on a sheet
    > unless it is the activesheet.
    >
    > So for example
    >
    > In the Sheet2 code module code like
    >
    > Private Sub Worksheet_Change(ByVal Target As Range)
    >
    > worksheets("Sheet1").Activate
    > Range("B9").Select '<== 1004 error here
    >
    >
    > End Sub
    >
    > better (don't select, but)
    >
    > Private Sub Worksheet_Change(ByVal Target As Range)
    > with worksheets("Sheet1")
    > .Activate
    > .Range("B9").Select
    > End with
    > End Sub
    >
    > If this isn't exactly what you are doing, I will bet it is close and the
    > source of your problem.
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "ben" <[email protected](remove this if mailing direct)> wrote in message
    > news:[email protected]...
    > > Have a code that upon a worksheet_change event creates an AutoShape object
    > > and inserts onto the Worksheet at a specific Location. The problem is the
    > > code ONLY works in Step-Mode, IF I break before the object is created and
    > > then step through it. If I let the code run without breaking it always
    > > returns an 1004 - application or object defined error,
    > > when it attempts to create the AutoShape.
    > > Why would it work in Step-Mode and not in Normal RunTime?
    > > Nothing else changes, no code, no procedures, no events, no workbooks.
    > >
    > > Ben
    > > --
    > > When you lose your mind, you free your life.

    >
    >
    >


  4. #4
    Tom Ogilvy
    Guest

    Re: Stepping through Code

    I can get that error if the sheet is protected. Otherwise, it worked for
    me.

    --
    Regards,
    Tom Ogilvy


    "ben" <[email protected](remove this if mailing direct)> wrote in message
    news:[email protected]...
    > there is only one sheet in the workbook being referenced, and this code is
    > running from an add-on where ALL references are qualified
    >
    > the actuall line that errors out is
    >
    > Set sh = wks.Shapes.AddShape(msoShapeDownArrow, 354.75, 162 + (RowN * 21),
    > 24, 30)
    >
    >
    > sh is a public shared variable referring to a Shape
    > and
    > wks is a worksheet object that is passed into the sub and is the same
    > whether stepping through or at Run Time
    >
    > Sub AddShape(RowN As Integer, wks As Worksheet)
    >
    >
    >
    > --
    > When you lose your mind, you free your life.
    >
    >
    > "Tom Ogilvy" wrote:
    >
    > > Sounds like you are doing something on a sheet other than the sheet

    where
    > > the change event occured.
    > >
    > > If this is the case, unqualified references such as
    > >
    > > Range("B9").Select
    > > would refer to the worksheet that contains the code, not the active

    sheet if
    > > that were a different sheet. In that type of situation, you would be
    > > intending to select on the activesheet, but the code thinks you want to
    > > select on the worksheet that contains the code. You can't select on a

    sheet
    > > unless it is the activesheet.
    > >
    > > So for example
    > >
    > > In the Sheet2 code module code like
    > >
    > > Private Sub Worksheet_Change(ByVal Target As Range)
    > >
    > > worksheets("Sheet1").Activate
    > > Range("B9").Select '<== 1004 error here
    > >
    > >
    > > End Sub
    > >
    > > better (don't select, but)
    > >
    > > Private Sub Worksheet_Change(ByVal Target As Range)
    > > with worksheets("Sheet1")
    > > .Activate
    > > .Range("B9").Select
    > > End with
    > > End Sub
    > >
    > > If this isn't exactly what you are doing, I will bet it is close and the
    > > source of your problem.
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > >
    > > "ben" <[email protected](remove this if mailing direct)> wrote in

    message
    > > news:[email protected]...
    > > > Have a code that upon a worksheet_change event creates an AutoShape

    object
    > > > and inserts onto the Worksheet at a specific Location. The problem is

    the
    > > > code ONLY works in Step-Mode, IF I break before the object is created

    and
    > > > then step through it. If I let the code run without breaking it always
    > > > returns an 1004 - application or object defined error,
    > > > when it attempts to create the AutoShape.
    > > > Why would it work in Step-Mode and not in Normal RunTime?
    > > > Nothing else changes, no code, no procedures, no events, no workbooks.
    > > >
    > > > Ben
    > > > --
    > > > When you lose your mind, you free your life.

    > >
    > >
    > >




  5. #5
    ben
    Guest

    Re: Stepping through Code

    that's the wierd thing, i don't get it on a fresh worksheet, only running on
    this worksheet and only if i try to do it through the worksheet change event,
    there's an object reference somewhere screwing up but now when it goes
    through step mode.
    Totally Lost and confused.
    well i'll just try different stuff till it works

    --
    When you lose your mind, you free your life.


    "Tom Ogilvy" wrote:

    > I can get that error if the sheet is protected. Otherwise, it worked for
    > me.
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "ben" <[email protected](remove this if mailing direct)> wrote in message
    > news:[email protected]...
    > > there is only one sheet in the workbook being referenced, and this code is
    > > running from an add-on where ALL references are qualified
    > >
    > > the actuall line that errors out is
    > >
    > > Set sh = wks.Shapes.AddShape(msoShapeDownArrow, 354.75, 162 + (RowN * 21),
    > > 24, 30)
    > >
    > >
    > > sh is a public shared variable referring to a Shape
    > > and
    > > wks is a worksheet object that is passed into the sub and is the same
    > > whether stepping through or at Run Time
    > >
    > > Sub AddShape(RowN As Integer, wks As Worksheet)
    > >
    > >
    > >
    > > --
    > > When you lose your mind, you free your life.
    > >
    > >
    > > "Tom Ogilvy" wrote:
    > >
    > > > Sounds like you are doing something on a sheet other than the sheet

    > where
    > > > the change event occured.
    > > >
    > > > If this is the case, unqualified references such as
    > > >
    > > > Range("B9").Select
    > > > would refer to the worksheet that contains the code, not the active

    > sheet if
    > > > that were a different sheet. In that type of situation, you would be
    > > > intending to select on the activesheet, but the code thinks you want to
    > > > select on the worksheet that contains the code. You can't select on a

    > sheet
    > > > unless it is the activesheet.
    > > >
    > > > So for example
    > > >
    > > > In the Sheet2 code module code like
    > > >
    > > > Private Sub Worksheet_Change(ByVal Target As Range)
    > > >
    > > > worksheets("Sheet1").Activate
    > > > Range("B9").Select '<== 1004 error here
    > > >
    > > >
    > > > End Sub
    > > >
    > > > better (don't select, but)
    > > >
    > > > Private Sub Worksheet_Change(ByVal Target As Range)
    > > > with worksheets("Sheet1")
    > > > .Activate
    > > > .Range("B9").Select
    > > > End with
    > > > End Sub
    > > >
    > > > If this isn't exactly what you are doing, I will bet it is close and the
    > > > source of your problem.
    > > >
    > > > --
    > > > Regards,
    > > > Tom Ogilvy
    > > >
    > > >
    > > > "ben" <[email protected](remove this if mailing direct)> wrote in

    > message
    > > > news:[email protected]...
    > > > > Have a code that upon a worksheet_change event creates an AutoShape

    > object
    > > > > and inserts onto the Worksheet at a specific Location. The problem is

    > the
    > > > > code ONLY works in Step-Mode, IF I break before the object is created

    > and
    > > > > then step through it. If I let the code run without breaking it always
    > > > > returns an 1004 - application or object defined error,
    > > > > when it attempts to create the AutoShape.
    > > > > Why would it work in Step-Mode and not in Normal RunTime?
    > > > > Nothing else changes, no code, no procedures, no events, no workbooks.
    > > > >
    > > > > Ben
    > > > > --
    > > > > When you lose your mind, you free your life.
    > > >
    > > >
    > > >

    >
    >
    >


  6. #6
    Nick Hebb
    Guest

    Re: Stepping through Code

    I've had this type of problem a number of times when working with
    AutoShapes - works in step-through, but throws an error when running
    normally.

    The tricks I've resorted to are:

    1. Prior to the statement, add a line:

    If wks Is Nothing Then
    MsgBox "blah blah blah"
    Exit Sub
    End If

    2. Add an "On Error Resume Next" prior to the statement, then check the
    error number after:

    If Err.Number <> 0 Then
    MsgBox "blah blah blah"
    Exit Sub
    Else
    On Error Goto Err_Handler
    End If

    3. Check the existence of the shape after the AddShape method:

    If sh Is Nothing Then
    MsgBox "blah blah blah"
    Exit Sub
    End If


    4. Select something before or after the operation - a range or shape -
    depending upon what you're doing.

    Strangely, adding these items often makes the errors go away, although
    you really haven't done anything other than check for the existence of
    objects or errors. It's as though it gives Excel a "chance to catch
    up".

    HTH,

    Nick Hebb


  7. #7
    ben
    Guest

    Re: Stepping through Code

    that is exceedingly unusual, adding a Range.select statement cleared up the
    problem, sounds like this may be a BUG. Thanks for the workaround Nick

    --
    When you lose your mind, you free your life.


    "Nick Hebb" wrote:

    > I've had this type of problem a number of times when working with
    > AutoShapes - works in step-through, but throws an error when running
    > normally.
    >
    > The tricks I've resorted to are:
    >
    > 1. Prior to the statement, add a line:
    >
    > If wks Is Nothing Then
    > MsgBox "blah blah blah"
    > Exit Sub
    > End If
    >
    > 2. Add an "On Error Resume Next" prior to the statement, then check the
    > error number after:
    >
    > If Err.Number <> 0 Then
    > MsgBox "blah blah blah"
    > Exit Sub
    > Else
    > On Error Goto Err_Handler
    > End If
    >
    > 3. Check the existence of the shape after the AddShape method:
    >
    > If sh Is Nothing Then
    > MsgBox "blah blah blah"
    > Exit Sub
    > End If
    >
    >
    > 4. Select something before or after the operation - a range or shape -
    > depending upon what you're doing.
    >
    > Strangely, adding these items often makes the errors go away, although
    > you really haven't done anything other than check for the existence of
    > objects or errors. It's as though it gives Excel a "chance to catch
    > up".
    >
    > HTH,
    >
    > Nick Hebb
    >
    >


  8. #8
    Nick Hebb
    Guest

    Re: Stepping through Code

    Yeah, I tried that trick on a lark after after reading about a similar
    situation when copying from Excel to Word. In that case, you needed to
    change code from Range.PasteSpecial to Range.Select and
    Selection.PasteSpecial.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1