1

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 somehow 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.

Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
Gerald King
  • 11
  • 1
  • It sounds to me like your code is raising an error. That will stop the execution of that function if you have no error handler. Then Excel might be calling your top level function again from a different cell. It's hard to say without more details. See these links for some possibly helpful information, though: http://stackoverflow.com/questions/3861431/vba-how-to-get-the-last-used-cell-by-vba-code-when-the-last-error-occured-in-a-w/3874340#3874340 , http://stackoverflow.com/questions/4379882/excel-vba-formula-called-from-a-cell-stops-processing-vba-or-encounters-an-app/4380316#4380316 – jtolle Sep 27 '11 at 19:36
  • Your are correct. Once I created an error handler I discovered that problem was the assignment of my myarray to my range object. – Gerald King Sep 28 '11 at 04:10

3 Answers3

3

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
Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
2

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
Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
1

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

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

Charles Williams
  • 23,121
  • 5
  • 38
  • 38