VBA function returns to calling routine on an assignment statement

I have a mid-level function that is called and calls other functions. Inside the function a dynamic arrary is "Dim myarrary() As Variant" is loaded from called function. The arrary "myarrary" is loaded with the proper data. When I attempt to assign the arrary to range

Dim datarng as Range

datarng = WorkSheets("DATA").Range("A1:H3700").Value   

The debugger executes the statement and immediately returns to the top level routine that initiated the process. I'm thinking I crashed the stack som开发者_开发百科ehow but I've place strings around myarray and they are intact. if I comment out the assignment statement the routine executes the remaining code in the function.

My guess is that you have an error handler in the top-level routine but not in the lower-level functions. In that type of situation, when the code hits a run-time error it steps immediately to the routine that had an error handler.

Also, Dim myarrary() As Variant creates an array of variants, which I don't think is what you want. It looks like you just want a variant, like:

Dim myarrary As Variant

You can then assign a range to it, e.g.:

myarrary = WorkSheets("DATA").Range("A1:H3700").Value

Your example code will not compile for two reasons:

Dim datarng as Range
datarng = WorkSheets("DATA").Range("A1:H3700").Value

1. You can't assign a "value" to a Range. Right hand side should be WorkSheets("DATA").Range("A1:H3700"), and not WorkSheets("DATA").Range("A1:H3700").Value.
2. datarng is an object (of type Range), so you need the Set keyword to make the assignment. Left hand side should be Set datarng.

The correct syntax is therefore:

Set datarng = WorkSheets("DATA").Range("A1:H3700")

Of course you can also dump the value content of datarng into a Variant like this:

Dim myArray as Variant
myArray = datarng ' or datarng.Value

You are missing the Set in front of datarng = WorkSheets("DATA").Range("A1:H3700").Value

should be Set datarng = WorkSheets("DATA").Range("A1:H3700")