Named Range using Indirect does not work when Evaluated in VBA

  • Hi there,


    I'm trying to read the value of a named range from Excel into VBA code associated with a specific worksheet (Click Box), but it's just not working! The problem is that the named range on the worksheet (CountR), which is a row count for a table that can expand or contract, has the formula =count(indirect("D8:D500")). I need the indirect function because the table can grow or shrink according to the individual user's requirements and I don't want the count row range to change. The indirect function is causing a problem when I try to read the value of CountR into my VBA code via the line iCountR = [CountR].


    Everything works fine if I place the =count(indirect("D8:D500")) formula in a cell and name the cell CountR. But if I simply name the range in Name Manager and reference the name from VBA as per the above code a value of zero is returned.


    My work-around using a hidden helper cell works fine, but I'd like to avoid doing this if I can, since the worksheet will be used by many people and there is always the risk that fiddling with the worksheet structure may occur and the important hidden cell gets over-written or deleted!


    Any ideas on how to solve this? I'm actually thinking that volatile nature of Indirect may preclude doing what I want to do.


    Thanks,


    Frosty

  • Re: Named Range using Indirect does not work when Evaluated in VBA


    Try using this function in your VBA to calculate the range count.


    Usage : FnCountTheValues(Range("D8:D500"))


    Code
    Function FnCountTheValues(R As Range) As Integer
        For Each c In R
            If IsNumeric(c.Value) And Not IsEmpty(c.Value) Then
                iCount = iCount + 1
            End If
        Next c
        FnCountTheValues = iCount
    End Function
  • Re: Named Range using Indirect does not work when Evaluated in VBA


    "I'm trying to read the value of a named range from Excel into VBA code associated with a specific worksheet ..."


    If CountR is scoped at the Worksheet level then Evaluate will give inconsistent results with general usage.
    Try


    Code
    iCountR = Evaluate(Worksheets("Click Box").Names("CountR").RefersTo)


    You also might try modifying the definition of CountR to include the sheet.


    RefersTo: =count(indirect("'Click Box'!D8:D500"))

  • Re: Named Range using Indirect does not work when Evaluated in VBA


    Thanks Mike,


    That code doesn't work I'm afraid. It returns 0.
    I also included the worksheet name in the Name definition. So in the worksheet I have CountR defined as =Count(Indirect("Fee!D8:D500")). This correctly counts the cells containing values.


    I also tried making the Name global rather than local, but that didn't work either.


    Indirect is definitely the problem. If I remove it and have =Count($A$8:$A$500) then that works just fine and returns the same correct result with both VBA code options iCountR = [CountR] and Evaluate(Worksheets("Fee").Names("CountR").RefersTo

    I don't want my row count range to move about when rows are added or deleted, so I need the Indirect function in the range Name. Any further thoughts?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!