Overflow Error in Array Assignment

[mattreingold]

I have run this code flawlessly with smaller workbooks, I am getting an overflow error where I try to assign the stressArr(v) values with much larger workbooks.

The array shouldn't have more than ~3800 data points in it (any of the arrays) so I'm not sure why there is an overlow... Any and all help would be appreciated!

NOTE: I left out some code that isnt important to my questions (trust me it isnt necessary and my laziness in those portions of code would only be embarassing lol)




...

' Initialize Variables for Usage
Dim loadArr()
Dim areaArr()
Dim stressArr()
Dim strainArr()
Dim lengthArr()
Dim changeLengthArr()
Dim percPosArr()


loadArr = WSR.Range("AY267:AY" & TotalRows).Value
changeLengthArr = WSR.Range("BF267:BF" & TotalRows).Value
maxVal = WorksheetFunction.Max(loadArr())
Area = WSR.[AQ267].Value
AreaReal = 1000000 * Area

...

' Arrays of Values for Stress/Strain Calulations


ReDim areaArr(LBound(loadArr) To UBound(loadArr))
ReDim stressArr(LBound(loadArr) To UBound(loadArr))
ReDim strainArr(LBound(loadArr) To UBound(loadArr))
ReDim lengthArr(LBound(loadArr) To UBound(loadArr))
ReDim percPosArr(LBound(loadArr) To UBound(loadArr))


For q = LBound(loadArr) To UBound(loadArr)
areaArr(q) = AreaReal
lengthArr(q) = Length
Next q


For v = LBound(loadArr) To UBound(loadArr)
stressArr(v) = loadArr(v, 1) / areaArr(v)
strainArr(v) = (1000 * changeLengthArr(v, 1)) / lengthArr(v)
percPosArr(v) = Round((100 * strainArr(v)), 2)
WS2.Cells(v, 2).Value = percPosArr(v)
WS2.Cells(v, 1).Value = stressArr(v)

Next v

...

[snb]

What is this any different from http://www.vbaexpress.com/forum/showthread.php?62845-Array-Indexing-Please-help-at-a-loss-for-words ?
Why did you ignore: http://www.vbaexpress.com/forum/showthread.php?62845-Array-Indexing-Please-help-at-a-loss-for-words&p=380591&viewfull=1#post380591

Both

Dim loadArr()
Dim areaArr()
Dim stressArr()
Dim strainArr()
Dim lengthArr()
Dim changeLengthArr()
Dim percPosArr()


and


ReDim areaArr(LBound(loadArr) To UBound(loadArr))
ReDim stressArr(LBound(loadArr) To UBound(loadArr))
ReDim strainArr(LBound(loadArr) To UBound(loadArr))
ReDim lengthArr(LBound(loadArr) To UBound(loadArr))
ReDim percPosArr(LBound(loadArr) To UBound(loadArr))

are 100% redundant.

[mattreingold]

Same code, different question, I am getting an overflow error when working with larger workbooks and looking for guidance in regards to the overflow error. I didn't want to post under a forum topic asking a different question. This code runs great with smaller workbooks (I'm assuming the issue is larger data sets since it throws an overflow error).

[mattreingold]

I didn't ignore your solution, I am just working on getting the code to work (I posted just before your post that I solved my own problem, what I had was working and I didn't want to mess with it) I will surely be implementing your more concise solution once the code runs seamlessly otherwise.

Thank you for responding, though!

[SamT]

I'm curious why you're using a 3800 slot array to hold a constant value?

Area = WSR.[AQ267].Value
AreaReal = 1000000 * Area

areaArr(q) = AreaReal



This is always amusing to me
NOTE: I left out some code that isnt important to my questions (trust me it isnt necessary and my laziness in those portions of code would only be embarassing lol)


But, your problem may be that loadArr is a two dimensional array with one slot for the first dimension. :dunno:Try

strainArr(v) = (1000 * changeLengthArr(v, 1)) / lengthArr(1, v) Or maybe lengthArr(v, 1)

[mattreingold]

I wrote the area into an array because I couldn't figure out how to perform operations on an array with a constant value :banghead: Im not as experienced with VBA as I am other languages, so I took the 'brute force' approach, as I often do...

Also, this code runs fine with smaller workbooks... strainArr populates fine with the same statement with smaller workbooks...

[SamT]

wrote the area into an array because I couldn't figure out how to perform operations on an array with a constant value
stressArr(v) = loadArr(v, 1) / AreaReal
This also frees up quite a bit of memory.

More Memory savers...
The LBound of Array = Range.Value is by definition = 1

The UBound of loadArr = WSR.Range("AY267:AY" & TotalRows) is TotalRows - 266. (AY & TotalRows).Row - (AY267.Row - 1)


Dim UBnd as Long = TotalRows - 266

Dim loadArr(1 to UBnd) As Double 'Double takes far less memory than Variant
Dim areaArr(1 to UBnd) As Double
Dim stressArr(1 to UBnd) As Double
Dim strainArr(1 to UBnd) As Double
Dim lengthArr(1 to UBnd) As Double
Dim changeLengthArr(1 to UBnd) As Double
Dim percPosArr(1 to UBnd) As Double
Actually, Just "Dim Variable() As Double" Then, just before loading it up and using it, ReDim it 1 to UBnd. As soon as you're done with it, Redim it to 0 to free up that memory.

[mattreingold]

Doing as you suggested, SamT, I get a mismatch on this line:


loadArr = WSR.Range("AY267:AY" & TotalRows).Value

This is why I dimmed as Variant, any ideas?

[mattreingold]

I have a feeling


stressArr(v) = loadArr(v, 1) / AreaReal
Also threw me a mismatch (this was what I initially thought to do)...

D:

[Paul_Hossler]

NOTE: I left out some code that isn't important to my questions (trust me it isn't necessary and my laziness in those portions of code would only be embarrassing lol)


Since you're working with worksheet values and don't show how things get initialized, possibly the rest of the code and a sample workbook is necessary???

[SamT]

Post #8. Oops, my bad, any Variable used as an array to be assigned a Range.Value, must be a Variant. all the others can be declared as any variable Type.

Post # 9. Are you sure that WSR.Range("AY267:AY") is all numbers and no Strings?
If they all look like numbers, you can try

stressArr(v) = CDbl(loadArr(v, 1)) / AreaRealNote... That won't work on any "number" that is preceded by an (invisible) apostrophe, which forces String like behavior. You need to Ctrl+H that Range to remove the apostrophes.

Also see http://www.snb-vba.eu/inhoud_en.html >> [Arrays] for info about "converting" Arrays of Ranges to 1 dimension. Something about [I]Application.Transpose(Range...) or Application.Transpose(Application.Transpose(Range...)) One "converts" Arrays of Columns and the other of Rows.

[SamT]

Post # 10

NOTE: I left out some code that isn't important to my questions (trust me it isn't necessary and my laziness in those portions of code would only be embarrassing lol)
:funnyashe:funnyashe:funnyashe:funnyashe:funnyashe:funnyashe:funnyashe:funn yashe:funnyashe:funnyashe:funnyashe:funnyashe

[mattreingold]

So I found the issue... It was the range assignment as you had suggested. AY267 is a hardcoded start value (The beginning of a column of values - was always the same on previous workbooks, this one is of course different, starting at 282)

I didn't think this would be an issue, I guess I thought blank cells in the array would be fine/ignored, I get however that performing operations (possible division by 0) could result, however. I don't know exactly why that would be a mismatch, though.

I was taking the lazy route by hard coding the values - Lesson learned, I will write lines to recognize the beginning row and use that as a starting index.

Thank you for the help guys, I really appreciate the guidance on memory saving as well. I'm learning VBA one trick at a time XD

Thanks Again!

[mattreingold]

Actually if you guys have a moment, I could use just a bit of help with this step. I threw this together to deem the first and last rows. The last row works great, but the first row picks up the header, which I would like to skip yet don't know how...


With WSR.Columns("AY")
With .SpecialCells(xlCellTypeConstants)
firstRow = .Areas(1).Row
lastRow = .Areas(.Areas.Count).Cells(.Areas(.Areas.Count).Rows.Count).Row
End With
MsgBox "the first row is " & firstRow
MsgBox "last row is " & lastRow
End With


loadArr = WSR.Range("AY282:AY" & TotalRows).Value
...
Area = WSR.[AQ282].Value


In fact, last row is actually the same as TotalRows, thus its not necessary (or I could rid of TotalRows). Either way, could you guys suggest a way to find the first row (skipping the header [first row of workbook]).

Also, how then could I implement this new index into my range assignments?


loadArr = WSR.Range("AY282:AY" & TotalRows).Value
...
Area = WSR.[AQ282].Value

If I remember correctly doing: WSR.Range("AY" & firstRow & ":" & "AY" & TotalRows) - [Or something of the sorts], has given me errors.

Cheers!

[SamT]

Post# 13...You can try

If loadArr(v, 1) = 0 Then
stressArr(v) = 0
Else
stressArr(v) = loadArr(v, 1) / AreaReal
End If

[SamT]

The Header is a xlCellTypeConstants therefore, the first Area. Try

firstRow = .Areas(2).Row

Next Q
I would use

With .SpecialCells(xlCellTypeConstants)
Set FirstCell = .Areas(2).Cells(1)
Set LastCell = .Areas(.Areas.Count).Cells(.Areas(.Areas.Count).Count) '???
End With

loadArr = Range(FirstCell, LastCell).Value

Can't see your workbook from Missouri, but it sounds like you have a bunch of Formulas before and after the Data in Column AY.

That's too bad, if the data in AY was at the bottom of the Column, you could use the simple and fast

Set LastCell = Cells(Rows.Count, "AY").End(xlUp)

[mattreingold]

I would really love to share more, however some of the workbook contents as well as procedures I'm running are confidential - thus much of what I'm posting here has modified names but same tasks.

The firstRow value is actually coming up as zero though after using your solution, I really am not familiar with the .Areas object though, so I don't know why :/

Attached is a picture of what the column looks like, a header and then 'n' rows down the data begins. Its actually the last thing in the column, though.

You guys really are life-savers here, I appreciate all the help you continue to give tremendously.

22348
22349
22350

[mattreingold]

Also, is there a way once I get these values to begin the index of my array? * Second half post 14

[SamT]

Attached is a picture of what the column looks like, a header and then 'n' rows down the data begins.

FirstRow = WSr.Range("AY2").End(xlDown).Row
LastRow = WSR.Cells(Rows.Count, "AY").End(xlUp).Row
Set FirstCell = WSR.Range("AY2").End(xlDown)
Set LastCell = WSR.Cells(Rows.Count, "AY").End(xlUp)
With WSR
loadArr = Range(.Cells(FirstRow, "AY"), .Cells("LastRow, "AY")).Value
End With
'or
loadarr = Range(FirstCell, LastCell).Value

Or single line, no Variables

With WSR
loadarr = Range(.Cells(2, "AY").End(xlDown), .Cells(Rows.Count, "AY").End(xlUp)).Value
End With

Also, how then could I implement this new index into my range assignments?
The Lbound of all Array = Range.Value arrays is 1. The Ubounds will be (LastRow-FirstRow) + 1

[mattreingold]

Sorry for the delayed response, I had left work for the weekend - SamT that solution works without a hitch, sorry I wasnt more clear earlier, I am very grateful for your time and help! Many Thanks:bow:

[SamT]

Y.W.