Macro statement acts differently in function and sub

Question

I am having trouble accessing the value of some, but not all, cells in a spreadsheet. The same statements work fine in a sub but not in a function. See the attached picture. Accessing B15 works fine in both but accessing Y13 fails in the function. Can anyone explain why or how I avoid the problem.

Can anyone explain why or how I avoid the problem.

Answer 1

Hi,

When you try to access the value of a cell that contains a formula, you may get the result of that formula rather than the underlying value.

To work around this, you can use the .Value property along with the .Value2 property to access the raw underlying value of the cell, regardless of whether it contains a formula or not.

Here's an example of how you can modify your code to retrieve the values of cells Y13 and B15:

Function GetValueOfY13AndB15() As Variant
    Dim y13Value As Variant
    Dim b15Value As Variant
    
    ' Accessing the value of cell Y13
    y13Value = Range("Y13").Value2
    
    ' Accessing the value of cell B15
    b15Value = Range("B15").Value2
    
    ' Return the values
    GetValueOfY13AndB15 = Array(y13Value, b15Value)
End Function

By using the .Value2 property instead of .Value, you should be able to retrieve the underlying values of the cells, even when they contain formulas.

Best Regards.