excel2003 big DIM - how to determine used memomry in VBA

[nodari]

Hi

I have big DIMs in my VBA and the program often says 'out of memory'. I want to resize the vars and for it I need to get inforamtion about used memory by variables.

and here is enother question: for type string in the help is written:
<TABLE cellSpacing=4 cellPadding=4 cols=3><TBODY><TR vAlign=top><TD class=T width="26%">String
(variable-length)</TD><TD class=T width="25%">10 bytes + string length</TD><TD class=T width="49%">0 to approximately 2 billion </TD></TR><TR vAlign=top><TD class=T width="26%">String
(fixed-length)</TD><TD class=T width="25%">Length of string</TD><TD class=T width="49%">1 to approximately 65,400</TD></TR></TBODY></TABLE>
what does it mean, if I have string 'A' does it use only 1 byte (or 10+1) ?

(if so, that means no mind to concatenate few different strings into one).

 

[nodari]

and if the string type var is empty how about used memory?

 

[nodari]

noone answers...

do I have too heavy question ?

or maybe too simple?

 

[tusharm]

You have already found the answers to your questions. {grin}

10 bytes + the number of characters in the string.

If the string is empty, the number of characters is zero.

noone answers...

do I have too heavy question ?

or maybe too simple?

 

[nodari]

You have already found the answers to your questions. {grin}

10 bytes + the number of characters in the string.

If the string is empty, the number of characters is zero.

thank you for this answer, I wanted to be sure. I know now, when I write dim raw(0 to 10 000) as string, it doesn't take any place in memory.

so I need to find other way to economy memory. I try to use ReDim, if it helps.

 

[Norie]

Are you sure the 'Out of memory' error is being caused by your 'big Dim'?

What do you mean by 'big Dim' anyway?

 

[nodari]

Are you sure the 'Out of memory' error is being caused by your 'big Dim'?

What do you mean by 'big Dim' anyway?

I think so. it look like this:
Const sul_saqoneli = 15000, sul_sawyobebi = 9
Public Const mainSi = "\\Main\_2011\", sawyisi = "_20110101", bolo_TariRi_ = "2012.01.01", sawyisi_TariRi = "2011.01.01"
Private Type saqoneli
jgufi As Byte: code As String
saxeliL As String: saxeliKA As String: productID(1 To 2) As String: product_ganzom As String * 5 'calia, kg, Tu metri
mwarm(1 To 4) As String ' 1,2,3,4 - mwarmoebeliKA,mwarmoebeliL, momwodebeliKA,momwodebeliL
zoma(1 To 3) As Integer: zoma_ganzom(1 To 3) As String * 5 'sm-ia Tu metri, Tu sxva. 1,2,3 - sisqe, sigane, sigrZe
forma As String ' magaliTad profilisaTvis 18-3, 25-1 da a. S.
wona As Single: wona_ganzom As String * 7 ' kg
feriKA As String: feriL As String
mwarmoeblis_mier_gansazRvruli_jgufi As String
TviTR As Single: gayidulis_sruli_TviTRirebuleba As Single: miRebulis_sruli_TviTRirebuleba As Single: sawyisi_naSTis_sruli_TviTRirebuleba As Single 'sawyisi TviTRirebuleba
fasi As Single
sawyobi(0 To sul_sawyobebi) As Single: yvela_sawyobSi As Single ' sawyisi naSTebi

CaweraTa_raodenoba As Byte ' <<< es me mgoni amosaRebia
gavs As Integer
bolo_TviTR As Long: bolo_miReba As Date
End Type
Private Type klienti
saxeli As String
Tanxa As Single
CaweraTa_raodenoba As Byte
coment As String
TariRi As Date
End Type
Dim klient(0 To 10000) As klienti
Dim realizaciebi(0 To sul_sawyobebi, 10 To 20) As Single ' 0 - 10 sawyobi, 10 - 20 realizaciis saxeebi: realizacia, daxerxva, . . .
Dim salaroD(0 To sul_sawyobebi), salaroK(0 To sul_sawyobebi) As Double: Dim naSTi(0 To 10000) As Single ' salaroD - debeti; salaroK - krediti, anu gasavali
Public bolo_TariRi, mimdinare_TariRi As Date
Dim naWeri() As Integer '0 To sul_saqoneli, 0 To sul_sawyobebi, 0 To 0
Dim saq(0 To sul_saqoneli) As saqoneli
Private sa, carieli As saqoneli: Private kl_carieli As klienti: Private dRe, dRe1 As Date
Private raodenoba_saqonlis_siaSi As Integer: Private rowN As String: Private rangeM As Single: Private mimdinare_faili: Private mTeli As Long

I didn't make any change, only copy from my program to here.

 

[tusharm]

10,000 elements is not a lot of elements -- unless each string is *extremely* long. Even if each string was 10,000 characters long, your memory requirements are 10,000 * 10,000 or 100 million characters or 0.1 GB.

When I ran the code below, Excel's Working Set went from 167MB to 362MB.

Sub checkMemory()
    Dim I As Long
    Dim X(10000) As String
    For I = LBound(X) To UBound(X)
        X(I) = String(10000, "x")
        Next I
    Stop
    End Sub

thank you for this answer, I wanted to be sure. I know now, when I write dim raw(0 to 10 000) as string, it doesn't take any place in memory.

so I need to find other way to economy memory. I try to use ReDim, if it helps.

 

[nodari]

10,000 elements is not a lot of elements -- unless each string is *extremely* long. Even if each string was 10,000 characters long, your memory requirements are 10,000 * 10,000 or 100 million characters or 0.1 GB.

When I ran the code below, Excel's Working Set went from 167MB to 362MB.

Sub checkMemory()
    Dim I As Long
    Dim X(10000) As String
    For I = LBound(X) To UBound(X)
        X(I) = String(10000, "x")
        Next I
    Stop
    End Sub

in this case the problem is why it says 'out of memory' ?!

really, in this code i need to maximize const sul_saqoneli = 15000 as much as possible. on sul_saqoneli=20 000 it says out of memory. (I use ReDim of naWrebi(0 to sul_saqoneli, 0 to 10, 0 to 500)