The SUM function in VBA using NAMED RANGES only shows errors

Question

I defined two named ranges; one (myplace) for the location to write the result to and one (myrange) for the range that needs to be summed. Compiler returns an error

Tried the formula method for sum, but this returns "#NAAM" error

This is the simple code I used:

Sub optelsom()

myplace = Range("plaats") 
myrange = Range("deelsom")

Range(myplace).Value = Application.WorksheetFunction.Sum(myrange)

End Sub

SJR · Accepted Answer · 2019-07-11 09:22:06Z

1

(1) Get in the habit of declaring your variables (as per PEH's suggestion, use Option Explicit: in the VBA editor go to Tools › Options › Require Variable Declaration)

(2) You need Set when assigning object variables such as ranges

(3) myplace is already a range so no need for a 'wrapper'

Sub optelsom()

Dim myplace as range, myrange as range

Set myplace = Range("plaats") 
Set myrange = Range("deelsom")

myplace.Value = Application.WorksheetFunction.Sum(myrange)

End Sub

edited Jul 11, 2019 at 9:22

answered Jul 10, 2019 at 17:21

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pᴇʜ Over a year ago

I recommend always to activate Option Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration.

Collectives™ on Stack Overflow

The SUM function in VBA using NAMED RANGES only shows errors

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related