I have a VBA Sub that accepts a Range from a table that is connected to a data connection to Sharepoint. The Sub sums all values in a column whose cell height <> 0. (unfiltered rows) When I open the file, or do a manual data connection refresh, the Sub returns #VALUE!. If I double click the formula cell (to edit it), then hit Return, the cell computes correctly. (Clicking Calculate Now does not have an effect).
When I hover my mouse over the cell, the error hint is, "A value used in the formula is the wrong data type"
Debugging ...
The Sub simply loops through the cell range and sums the cells with a height greater than zero. I believe the range that is passed in is invalid for some reason or has no rows immediately after a data connection refresh. The Sub is called automatically after the data connection refresh. I break and watch the range and Cell.row values at each iteration. The range shows 100 rows, but the cell.row value does not change and the loop exits after three iterations. it looks like the cell values are invalid: inspecting cell.Height in the loop shows, "<Unable to the the Height property from the Range class>".
If I perform the same debugging steps after the previously mentioned edit to invoke the Sub calculation, everything works fine, including the cell.row value incrementing at each iteration.
Interestingly, replacing the Sub call with the internal SUM() function works fine after a refresh, except w/o the needed functionality .
Can anyone suggest a fix, or explain why this is happening? The cell value and Sub are included below....
Cell Value: =SumVis(A1:A100)
Function SumVis(r As Range)
Dim cell As Excel.Range
Dim total As Variant
For Each cell In r.Cells
If cell.Height <> 0 Then
total = total + cell.Value
End If
Next
SumVis = total
End Function
When I hover my mouse over the cell, the error hint is, "A value used in the formula is the wrong data type"
Debugging ...
The Sub simply loops through the cell range and sums the cells with a height greater than zero. I believe the range that is passed in is invalid for some reason or has no rows immediately after a data connection refresh. The Sub is called automatically after the data connection refresh. I break and watch the range and Cell.row values at each iteration. The range shows 100 rows, but the cell.row value does not change and the loop exits after three iterations. it looks like the cell values are invalid: inspecting cell.Height in the loop shows, "<Unable to the the Height property from the Range class>".
If I perform the same debugging steps after the previously mentioned edit to invoke the Sub calculation, everything works fine, including the cell.row value incrementing at each iteration.
Interestingly, replacing the Sub call with the internal SUM() function works fine after a refresh, except w/o the needed functionality .
Can anyone suggest a fix, or explain why this is happening? The cell value and Sub are included below....
Cell Value: =SumVis(A1:A100)
Function SumVis(r As Range)
Dim cell As Excel.Range
Dim total As Variant
For Each cell In r.Cells
If cell.Height <> 0 Then
total = total + cell.Value
End If
Next
SumVis = total
End Function