Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Debugging VBA in Excel.

  1. Aug 25, 2012 #1
    Hello all. I am trying to get my code to color cells if a referenced cell is populated. That part works but when I tried to populate another cell a different color I encountered a problem. You can see where the mistake comes in on the code. It is when I try to populate a variable 'strMyVar' with an offset property. I have underlined where I am having the problem. Any suggestions?

    Sub colorcells()
    Dim x, n, y, i As Double
    Dim strMyVal As String
    For n = 1 To 3000
    Cells(n, 1).Activate
    If Cells(n, 1) <> ("") Then
    For x = 1 To 7
    If x < 7 Then
    Cells(n, x).Activate
    ActiveCell.Interior.Color = RGB(252, 213, 180)
    End If
    If x >= 7 Then
    Cells(n, x).Activate
    strMyVal = ActiveCell.Offset(-3, 0).Value
    If strMyVal <> ("") Then
    ActiveCell.Interior.Color = RGB(255, 255, 153)
    End If
    End If
    Next x
    x = 1
    End If
    Next n
    End Sub
     
  2. jcsd
  3. Aug 25, 2012 #2

    Borg

    User Avatar
    Gold Member

    I wrapped CODE tags around your code so that it would stay formatted. It's been a long time since I've coded anything in VBA but the first thing that jumps out at me is the question of whether ActiveCell.Offset(-3, 0) is ever null. If so, attempting to retrieve value from a null object will break.
     
  4. Aug 25, 2012 #3

    chiro

    User Avatar
    Science Advisor

    Following on from what Borg mentioned above, can you first check that your object being referenced exists?

    What is the ActiveCell object structure and what are the valid values for the offset index parameter?
     
  5. Aug 25, 2012 #4

    hotvette

    User Avatar
    Homework Helper

    The problem is you are trying to address a cell with a negative row number. If you put a debug print statement in your code like the below, you'll see that the active cell is $G$1 just before the error. The offset operation tries to address a negative row value, which is illegal.

    Code (Text):
    Cells(n, x).Activate
    Debug.Print ActiveCell.Address
    strMyVal = ActiveCell.Offset(-3, 0).Value
     
  6. Aug 28, 2012 #5
    Thanks for all the input. Very helpful. I am not a programmer just an engineer trying to make my job a little easier.

    Hotvette, how should I reference a cell to the left of an active cell??
     
  7. Aug 29, 2012 #6

    hotvette

    User Avatar
    Homework Helper

    For the offset method (should be documented in VBA help), the first parameter is row offset and the 2nd is column offset:

    Code (Text):
    strMyVal = ActiveCell.Offset(0, -1).Value   'cell on immediate left of active cell
    strMyVal = ActiveCell.Offset(0, 1).Value    'cell on immediate right of active cell
    strMyVal = ActiveCell.Offset(-1, 0).Value   'cell immediately above the active cell
    strMyVal = ActiveCell.Offset(1, 0).Value    'cell immediately below the active cell
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: Debugging VBA in Excel.
  1. Excel macro's and VBA (Replies: 18)

Loading...