VBA Sub returns #VALUE! after data connection refresh

[ggeisen]

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

 

[hiker95]

ggeisen,

Welcome to the MrExcel forum.

Your Function is not returning a value. The following works in my test environment (see the BOLD):


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Function SumVis(r As Range) As Variant
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

You did not say what the format of range A1:A100 was.

If it is dollars and cents, then change this:

Function SumVis(r As Range) As Variant

To this:

Function SumVis(r As Range) As Double

 

[ggeisen]

Your Function is not returning a value. The following works in my test environment (see the BOLD):

Function SumVis(r As Range) As Variant

Adding As Variant or As Double has no effect. I suspect it is implied, although not in best form. The code will likely still work in your test environment w/o the As clause. Note that I mention the Function works fine if it is invoked manually, or even typed in elsewhere on the sheet. It returns the summation expected, except after a Refresh.

I still suspect the Range passed in immediately after the Refresh may not be valid by indication of the data values inspected in the debugger. I do not know why this would be

The data type for A1:A100 is monitary.

 

[hiker95]

ggeisen,

So that we can get it right the next time:

You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.


If you are not able to provide your workbook/worksheet:

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[ggeisen]

@hiker95,

I can surely upload a sanitized workbook, but you won't have access to the data connection, which is where I think the problem is stemming from. The Function works fine until it is acting upon a table that has a data connection.

 

[hiker95]

ggeisen,

We could change your Function into a macro.

What is the worksheet name, and what is the cell that you want the result sum in?

You could then call the macro name from the end of the code that does the download of the information.

 

[ggeisen]

Interesting development .... and work around

I created several other simple Functions. They all return #VALUE! after a Data Connection Refresh if they operate (have parameters) on the table that has the data connection to Sharepoint. Cells that call Functions which do not operate on the data connected table work fine.

I've discovered a work-around:

Place Application.Volatile in all the Functions that are used to operate against the data connected table and press F9 after opening the file, or after any Data Connection Refresh.

Function SumVis(r As Range) As Double
Application.Volatile

My guess is the workbook recalculation is occurring too soon after the external data connection refresh completes and "regenerates" the table. That may explain why the Functions cannot operate on the table.

 

[ggeisen]

We could change your Function into a macro.

What is the worksheet name, and what is the cell that you want the result sum in?

You could then call the macro name from the end of the code that does the download of the information.

The worksheet name is 'KEY DATES', and the destination cell is H1. But I'm confused ... I don't have code that does the download. I've checked Refresh data when opening the file. from the Connection Properties dialog.

 

[hiker95]

ggeisen,

The worksheet name is 'KEY DATES', and the destination cell is H1. But I'm confused ... I don't have code that does the download. I've checked Refresh data when opening the file. from the Connection Properties dialog

I am not familiar with the above.

Sample raw data after the download (not all 100 cells are shown for brevity):

Excel 2007
	A	H
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16	16
17	17
18	18
19	19
20	20
KEY DATES

After the download, then run the below macro, and we get this:

Excel 2007
	A	H
1	1	5050
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16	16
17	17
18	18
19	19
20	20
KEY DATES

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub MySumVis()
' hiker95, 04/12/2013
' http://www.mrexcel.com/forum/excel-questions/696747-visual-basic-applications-sub-returns-value-after-data-connection-refresh.html
Dim c As Range, rng As Range
Dim total
Set rng = Sheets("KEY DATES").Range("A1:A100")
For Each c In rng
  If c.Height <> 0 Then
    total = total + c.Value
  End If
Next c
Sheets("KEY DATES").Range("H1") = total
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the MySumVis macro.

 

[dandungan]

How does one delete a post that was posted in error?