Search code examples
excelvba

Can't Debug When Calling Function From Cell

I'm trying to call a function from a formula in a cell. In this case:

=CompareColors(test1, test2)

Where test1 and test2 are named ranges. The function returns the value as expected, but I'm never able to step through the code, even though I put a breakpoint on the debug statement.

Here's my function:

Public Function CompareColors(answerRange As Range, compareRange As Range) As Long

  Dim rng1 As Range: rng1 = answerRange
  Dim rng2 As Range: rng2 = compareRange
  Dim answer as Long

  answer = 2

  debug.print "heh" 'breakpoint is here
  
  CompareColors = answer


End Function

I want to build my function further, but can't figure out why I'm not able to step through it. Thanks!

Solution

  • Breakpoints perfectly work in functions.

    VBA Image

    The problem is in the code error:

      Dim rng1 As Range: Set rng1 = answerRange
  Dim rng2 As Range: Set rng2 = compareRange

    You missed the Set keyword which is required for object assigning.

    You should notice your code returns the #VALUE! error which tells that something wrong inside the function body.

    To debug a function better, just call it from any subroutine.

    Sub TestMyFunction
  CompareColors [A1], [B1]
End Sub