Array overflow problem

YasserKhalil · Post by **YasserKhalil** » 28 Feb 2019, 20:05

Hello everyone
I am trying to store a range A3 to ZZ to last row in an array
lr variable equals to 500

arr=Sheets("data").Range("A3:ZZ" & lr).Value

This array is from another workbook not in ThisWorkbook if this make any sense

But I got overflow ...
Why did I get this error and how to fix that ??

Post by **HansV** » 28 Feb 2019, 20:15

I can assign Range("A3:ZZ500").Value to an array without error.
Does it make a difference if you use Value2 instead of Value?

YasserKhalil · Post by **YasserKhalil** » 28 Feb 2019, 20:23

What is the difference between using .Value and .Value2?

This is the sample file
https://we.tl/t-UGyzgxIi7L" onclick="window.open(this.href);return false;

Post by **HansV** » 28 Feb 2019, 20:26

Have you tried to Google it?

YasserKhalil · Post by **YasserKhalil** » 28 Feb 2019, 20:28

I already googles that before you ask but I couldn't get it

--------------------------------
.Value2 gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean)

.Value gives you the same as .Value2 except if the cell was formatted as currency or date it gives you a VBA currency (which may truncate decimal places) or VBA date.

Post by **HansV** » 28 Feb 2019, 20:33

For example, let's say that the active cell contains today's date (28-Feb-2019).

Debug.Print ActiveCell.Value will return that date in your default date format, for example 28/02/2019.

Debug.Print ActiveCell.Value2 will return the numeric equivalent of the date: 43524.

YasserKhalil · Post by **YasserKhalil** » 28 Feb 2019, 20:34

Thanks a lot Mr. Hans
I have read this before but I can't get why the .Value caused overflow error and .Value2 didn't

Post by **HansV** » 28 Feb 2019, 20:43

The Value2 property is less complex because it doesn't have to support the Date and Currency data types. So presumably it uses less memory.

YasserKhalil · Post by **YasserKhalil** » 01 Mar 2019, 05:19

Thanks a lot Mr. Hans for great help
Regards

Doc.AElstein · Post by **Doc.AElstein** » 03 Mar 2019, 20:49

Hi Yasser…
With .Value. I get overflow also...
but in this way I don't....

Sometimes, if it works… an object called the DataObject provides support for working with text strings on the Windows clipboard. You know this also… http://www.eileenslounge.com/viewtopic. ... 95#p242941" onclick="window.open(this.href);return false;
Excel range is like a string when help in Clipboard:
"A1" & vbTab & "B1" & vbTab & "C1"
& vbCr & vbLf & "A2" & vbTab & "B2" & vbTab & "C2"
& vbCr & vbLf & "A3" & vbTab & "B3" & vbTab & "C3"
& vbCr & vbLf

.Copy will make all copies in Clipboards, also Windows: http://www.eileenslounge.com/viewtopic. ... 20#p246887" onclick="window.open(this.href);return false;
I can GetFromClipboard
strGet = "A1" & vbTab & "B1" & vbTab & "C1"& vbCr & vbLf & "A2" & vbTab & "B2" .......
Split by vbCr & vbLf is
"A1" & vbTab & "B1" & vbTab & "C1"
"A2" & vbTab & "B2" & vbTab & "C2"
"A3" & vbTab & "B3" & vbTab & "C3"
“ “ ( -- Last “ “ I do not want !! )

Split by vbTab is
CntRws = 1
"A1" "B1" "C1" …. CntClms = 1 2 3 …….
CntRws = 2
"A2" "B2" "C2" …. CntClms = 1 2 3 …….
CntRws = 3
"A3" "B3" "C3" ……. CntClms = 1 2 3 …….
CntRws = 4 ……..

Do you see ?….. see code below…

Rem 1 Data object, get long string of all your range
Rem 2 New array for you for all range arrStr()
Rem 3 Fill your array with string values … CntRws = 1 - CntClms = 1 2 3 ……. CntRws = 2 - CntClms = 1 2 3 ……
arrStr() is approximately = WS.Sheets("data").Range("A3:ZZ" & lr).Value

Arr()= WS.Sheets("data").Range("A3:ZZ" & lr).Value2
Arr.JPG : https://imgur.com/dXahB08" onclick="window.open(this.href);return false;

Arr.JPG

arrStr() is approximately = WS.Sheets("data").Range("A3:ZZ" & lr).Value
arrStr.JPG : https://imgur.com/kaSet5w" onclick="window.open(this.href);return false;

arrStr.JPG

Code: Select all

 Arr() = WS.Sheets("data").Range("A3:ZZ" & lr).Value2
WS.Sheets("data").Range("A3:ZZ" & lr).Copy ' This puts data in Excel, Windows, and office Clipboard
Stop
Rem 1  '  an object called the DataObject  provides support for working with text strings on the Windows clipboard.
'Dim DtaObj As MSForms.DataObject      ' Early Binding:  RefMSFORMS.JPG : https://imgur.com/8zKpyr2
' Set DtaObj = New MSForms.DataObject
Dim LaterDtaObj As Object
 Set LaterDtaObj = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
  LaterDtaObj.GetFromClipboard          '   We should have data in Windows clipboard
Dim strGet As String: Let strGet = LaterDtaObj.GetText() '  This is  "A1" & vbTab & "B1" & vbTab & "C1"& vbCr & vbLf & "A2" & vbTab & "B2" .......
Rem 2 determine array size
Dim Rws() As String: Let Rws() = Split(strGet, vbCr & vbLf, -1, vbBinaryCompare) ' Split by   vbCr & vbLf
Dim LBndRws As Long, UBndRws As Long: Let LBndRws = LBound(Rws()): Let UBndRws = UBound(Rws())
Dim Clms() As String: Let Clms() = Split(Rws(0), vbTab, -1, vbBinaryCompare)     ' Split by  & vbTab
Dim LBndClms As Long, UBndClms As Long: Let LBndClms = LBound(Clms()): Let UBndClms = UBound(Clms())
Dim arrStr() As String
 ReDim arrStr(1 To UBndRws + 1, 1 To UBndClms + 1)
Rem 3 Fill Array
Dim CntClms As Long, CntRws As Long
    For CntRws = 1 To UBndRws + 1 - 1    ' CntRws 1 2 3 .....       ' (  -1 -- Last " " I do not want  !!  )
     Let Clms() = Split(Rws(CntRws - 1), vbTab, -1, vbBinaryCompare) ' Split by  & vbTab
        For CntClms = 1 To UBndClms + 1  ' CntClms  1 2 3 .....
         Let arrStr(CntRws, CntClms) = Clms(CntClms - 1)
        Next CntClms
    Next CntRws

End Sub

Alan
File : Main.xlsm : https://app.box.com/s/5u40ggwbsbh1nv5oeluw376zowzda4pz" onclick="window.open(this.href);return false;
_.__________________

Ref
http://www.eileenslounge.com/viewtopic. ... 95#p242941" onclick="window.open(this.href);return false;
http://www.eileenslounge.com/viewtopic. ... 20#p246887" onclick="window.open(this.href);return false;
http://www.excelfox.com/forum/showthrea ... ibraries-1)-Early-1-5)-Laterly-Early-and-2)-Late-Binding-Techniques?p=11018&viewfull=1#post11018

YasserKhalil · Post by **YasserKhalil** » 03 Mar 2019, 22:00

Thanks a lot Mr. Alan for your interest

rory · Post by **rory** » 05 Mar 2019, 10:30

Cell E130 is formatted as a Date and has a number that is too large for a date value in it, which is why Value fails with an overflow, but Value2 works.

Doc.AElstein · Post by **Doc.AElstein** » 05 Mar 2019, 12:06

That’s a very enlightening observation, Mr Rory.
I am very glad you pointed that out: I think we were mistakenly thinking that the attempted array was too big. ( I think at the time I was slightly puzzled, as I thought I had captured larger arrays than that in the past).

Hovering over the cell E130 in Yasser’s file “data_saved.xlsx”, it tells me words to the effect… date values and times that are negative or too big will be placed as #####################.............s
I do not actually see that in the spreadsheet: In the cell I see nothing, but in the formula bar I see the number:
DateNumber is too Big.JPG : https://imgur.com/MlHViw8" onclick="window.open(this.href);return false;

DateNumber is too Big.JPG

_.___________________##################

The array that my coding makes , attempts to obtain the text held in a clipboard for the Excel range, …( and that is likely to be what is somewhere in something referring to something to do with the Windows Clipboard. )
If I try to examine what I have in the appropriate array element, in the array that my coding produces, then I see a lot of ###############################’s
###########################################################################.JPG : https://imgur.com/5lqoeAE" onclick="window.open(this.href);return false;

###################################################################################.JPG

In Immedite window :

Code: Select all

? arrStr(128,5)
###############################################################################################################################################################################################################################################################
? len(arrStr(128,5))
 255

I am guessing that there are probably a lot more ############’s somewhere? Or possibly not?
I am not sure what is limiting or truncating me to 255 characters, if indeed that is the case…
Is that a limit to a sting in an array, ( Note that my array is an array of string type elements , it is not an array of Variant elements. )
Or is this a limit of something to do with the clipboard…
Or is that string of ################s always 255
I guess I will find out sometime, but if anyone knows and can enlighten me it will save me a bit of time, so I would be grateful of any enlightenment there..

Alan

_.____________
P.s. @ Yasser:
To solve your original problem you just need to change the cell format to Standard
ChangeToStandard.JPG : https://imgur.com/0PyKQZN" onclick="window.open(this.href);return false;

ChangeToStandard.JPG

Your original coding, using Arr() = WS.Sheets("data").Range("A3:ZZ" & lr).Value , will then no longer error.
You will then not need .Value2

rory · Post by **rory** » 05 Mar 2019, 12:33

That's a longstanding issue with copying excel data to the windows clipboard.

Doc.AElstein · Post by **Doc.AElstein** » 05 Mar 2019, 12:36

you mean that the limitation is caused by the Windows clipboard?

rory · Post by **rory** » 05 Mar 2019, 13:13

No, it's the interaction between Excel and the clipboard.

Doc.AElstein · Post by **Doc.AElstein** » 05 Mar 2019, 13:25

Ah, that is further interesting. It may answer some other things I was puzzled about… I have sometimes wondered why seeming complicated codings were used to pass things into the windows clipboard from Excel, in preference to more simple codings. I guess to overcome this excel range issue might have been part of the reason sometimes .
I guess the issue with excel range is yet another example of how clipboards in general are a mixed up spaghetti of dependencies that any sane person has long since given up trying to unravel and understand. I recently started looking at the various Microsoft Clipboards and their interaction. It is quite interesting in a crazy sort of a way.

rory · Post by **rory** » 05 Mar 2019, 13:44

There are some serious issues with the DataObject in Windows 8 and later if you have an Explorer window open. That's why I prefer to use API calls if just putting text into the clipboard.

Doc.AElstein · Post by **Doc.AElstein** » 05 Mar 2019, 15:32

rory wrote:..serious issues with DataObject .. Windows 8 and later if you have an Explorer window open. That's why I prefer to use API calls if just putting text into the clipboard.

A few days ago I stumbled on these things https://social.msdn.microsoft.com/Forum ... rum=isvvba" onclick="window.open(this.href);return false;
https://docs.microsoft.com/de-de/office ... -clipboard" onclick="window.open(this.href);return false; ,
https://www.spreadsheet1.com/how-to-cop ... l-vba.html" onclick="window.open(this.href);return false; .. I remember thinking at the time … that ugly monster somehow slipped into my computer a few years back.… ClipboardMonster.JPG : https://imgur.com/CscR2Di" onclick="window.open(this.href);return false;

YasserKhalil · Post by **YasserKhalil** » 05 Mar 2019, 15:43

Thanks a lot Mr. Rory for this observation. That's awesome

Eileen's Lounge

Array overflow problem

Array overflow problem

Re: Array overflow problem

Re: Array overflow problem

Re: Array overflow problem

Re: Array overflow problem

Re: Array overflow problem

Re: Array overflow problem

Re: Array overflow problem

Re: Array overflow problem

Re: Array overflow problem

Re: Array overflow problem

Re: Array overflow problem

######Array overflow problem################################

Re: Array overflow problem

Re: Array overflow problem

Re: Array overflow problem

Re: Array overflow problem

Re: Array overflow problem

Array overflow problem / Clipboard Monster

Re: Array overflow problem