none
Cannot read cell contents of VBA Range variable RRS feed

  • Question

  • I've created a worksheet function in Excel 2010 that includes ranges in the list of arguments.  Initially, the range objects are properly passed into the function, but when I try to retrieve a value from a cell in the range, it shows up as empty.  Below is the code for the function.  When I call the function from a worksheet with a break point at the line Set homecell_I = rngGrayI(1,1), the watch value for homecell_X.Value2 matches the value in the worksheet and has Type Variant/Double.

    However, if I step to the next line, the watch value for homecell_X.Value2 changes to Type Variant/Empty.  The same error occurs with or with the break point.  Can anyone tell me what is causing the problem?

    Public Function GetEdgeWorksheet _
        (rngGrayX As Range, _
         rngGrayI As Range, _
         nPasses As Integer, _
         dblAlpha As Double, _
         dblTol As Double, _
         Optional bLHE As Boolean = True, _
         Optional bInRows As Boolean = True, _
         Optional dblNorm As Double = 0#) _
    As Double

        Dim i As Integer
        
        Dim dblGrayX() As Double
        Dim dblGrayI() As Double
        
        Dim homecell_X As Range
        Dim homecell_I As Range
        
        Set homecell_X = rngGrayX(1, 1)
        Set homecell_I = rngGrayI(1, 1)
        
        ReDim dblGrayX(0 To nPasses - 1)
        ReDim dblGrayI(0 To nPasses - 1)
        
        If bInRows Then
            For i = 0 To nPasses - 1
                dblGrayX(i) = homecell_X.Value2
                dblGrayI(i) = homecell_I.Value2
                
                Set homecell_X = homecell_X.Offset(0, 1)
                Set homecell_I = homecell_I.Offset(0, 1)
            Next i
        Else
            For i = 0 To nPasses - 1
                dblGrayX(i) = homecell_X.Value2
                dblGrayI(i) = homecell_I.Value2
                
                Set homecell_X = homecell_X.Offset(1, 0)
                Set homecell_I = homecell_I.Offset(1, 0)
            Next i
        End If
        
        GetEdgeWorksheet = GetEdge(dblGrayX(), dblGrayI(), dblAlpha, dblTol, bLHE, nPasses, dblNorm)
        
        Erase dblGrayX
        Erase dblGrayI

    End Function

    Tuesday, September 1, 2015 1:25 AM

Answers

  • Sorry about being slow to respond.

    To make progress, I got around the problem by getting the text value for the cell and converting that into a number using the Val() function.  Today I reverted back to the original code and threw in the Debug.Print statements.  Now I cannot repeat the problem; everything works as it should.  I know the cell contents in the ranges have not changed, so it is a mystery to me.  I'm guessing that Excel must have gotten into an odd state that cleared itself.

    Friday, September 4, 2015 9:01 PM

All replies

  • Re:  user designed function

    "GetEdge" is not defined.

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Monday, October 31, 2016 1:14 PM
    Tuesday, September 1, 2015 2:26 AM
  • I didn't bother showing it, but GetEdge is in the same module. If it didn't exist, the module would not have compiled.  If it makes any difference, here it is.  The GetNorm and GetIntensity functions are also defined.  But the problem arises well before any of these functions are called.

    Public Function GetEdge _
        (dblGrayX() As Double, _
         dblGrayI() As Double, _
         dblAlpha As Double, _
         dblTol As Double, _
         Optional bLHE As Boolean = True, _
         Optional nPasses As Integer = 0, _
         Optional dblNorm As Double = 0#) _
    As Double

        Dim dblLast_x As Double
        Dim dblI As Double
        Dim dblDi_dx As Double
        
        If nPasses = 0 Then
            nPasses = UBound(dblGrayX, 1) - LBound(dblGrayX, 1) + 1
        End If
        
        If dblNorm = 0# Then
            dblNorm = GetNorm(nPasses, dblAlpha, dblTol)
        End If
        
        GetEdge = dblGrayX(0)
        dblLast_x = GetEdge
        
        Do
            GetIntensity dblI, dblDi_dx, GetEdge, dblGrayX(), dblGrayI(), _
                    dblAlpha, dblTol, bLHE, nPasses, dblNorm
            
            GetEdge = GetEdge - (dblI - 0.5) / dblDi_dx
            
            If Abs(GetEdge - dblLast_x) < dblTol Then
                Exit Do
            End If
            
            dblLast_x = GetEdge
        Loop

    End Function

    Tuesday, September 1, 2015 5:30 PM
  • Without knowing your inputs most likely is the given cells really are empty. See if this sheds any light in your loops

    Set homecell_X = homecell_X.Offset(0, 1)
    Debug.Print homecell_X.Address, Range(homecell_X.Address).Value ' or Value2
    

    Tuesday, September 1, 2015 9:26 PM
    Moderator
  • Hi,

    Could you reproduce that issue in a new file? You may share a sample file on the OneDrive.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 4, 2015 5:11 AM
    Moderator
  • Sorry about being slow to respond.

    To make progress, I got around the problem by getting the text value for the cell and converting that into a number using the Val() function.  Today I reverted back to the original code and threw in the Debug.Print statements.  Now I cannot repeat the problem; everything works as it should.  I know the cell contents in the ranges have not changed, so it is a mystery to me.  I'm guessing that Excel must have gotten into an odd state that cleared itself.

    Friday, September 4, 2015 9:01 PM
  • Hi,

    Base on your reply, the issue is gone.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 7, 2015 9:09 AM
    Moderator