CODEWITHSUNDEEP
vbaexcel
derigible
derigible

Reputation: 964

VBA Range.value function causing unexpected end

This must seem like a terribly simple question, but I cannot figure out why my functions are ending unexpectedly on the Range.value = val call. Perhaps I am missing something very basic, but I have tested these out and each one of them are failing to resolve to anything and I don't know how to capture the error.

Here is the initial function:

Function incrementCount(upper As Range, Summed As Range, ParamArray sums() As Variant)

Dim deduct As Integer
Dim summation As Integer
Dim elem As Variant
Dim i As Long
Dim temp As Range
up = upper.Value
summation = Summed.Value
'Initialize the starting points of the increments
For i = LBound(sums) To UBound(sums)
    MsgBox IsObject(sums(i)) 'Prints out as an true
    MsgBox TypeName(sums(i)) 'Prints out as Rnage
    MsgBox sums(i).Value 'Prints out as 0
    Set temp = sums(i) 
    MsgBox temp.Value 'Prints out as 0
    Set temp = Summed
    MsgBox temp.Value 'Prints out as 1 (which is correct)
    temp.value = 3 'Errors here
    MsgBox temp.Value 'Never makes it to this line
    sums(i).Value = 1 'I have also tried this with the same result
Next i

<more code that is never reached>

End Function

I am at my wits end. I have searched MSDN, stackoverflow, and all the many excel forums and all of them show setting values to a range like this. I have even separated the setting of a range value to a different function like this:

Function testsub(thecell As Range, thevalue As Integer)
    thecell.value = thevalue
End Function

Ultimately i would like to be able to do something like discussed in this article where I loop over a random assortment of ranges and will increment them. Any help at all would be greatly appreciated.

Upvotes: 0

Views: 564

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

You have not specified how IncrementCount() is being called.

If your function is being called from a worksheet cell, then it is "bombing out" at the correct line. A UDF called from a cell cannot modify the contents of other cells, it can only return a value.

Upvotes: 2

Related Questions