VBA Excel, plage trop grande pour calculer ? [Résolu]

Signaler
Messages postés
4
Date d'inscription
lundi 29 septembre 2008
Statut
Membre
Dernière intervention
30 septembre 2008
-
Messages postés
4
Date d'inscription
lundi 29 septembre 2008
Statut
Membre
Dernière intervention
30 septembre 2008
-
Bonjour,

J'ai rédigé une petite fonction Excel pour calculer une statistique à partir d'un échantillon, d'une plage de données dans ma feuille.
Les calculs sont simples, mais nombreux et fastidieux.
J'ai donc fait ce module ci-dessous.
 
En résumé, je definie le type des variables, je les initialise (à zéro pour la plupart) puis les calculs commencent.

Cette fonction marche bien, MAIS ! si et seulement si je lui donne une plage de moins de 210 valeurs seulement ; au delà, Excel m'assène sa "#VALUE!".

Est-ce que ce sont les premières étapes itératives qui le limitent ?
Je vois bien qu'elles sont laborieuses, mais je n'ai pas trouvé plus simple.

Merci d'avance,

************************************************************­************

Function STATAGOSTINO(rPlage As Range)

Dim n As Long
Dim A As Double
Dim B As Double
Dim C As Double
Dim D As Double
Dim E As Double
Dim F As Double
Dim G As Double
Dim H As Double
Dim J As Double
Dim K As Double
Dim L As Double
Dim Z As Double
Dim Y As Double
Dim O As Double
Dim T As Double
Dim p As Double
Dim q As Double
Dim r As Double
Dim X As Double
Dim s As Double
Dim stat As Double
Dim i As Long

n = rPlage.Cells.Count
A = 0
B = 0
C = 0
D = 0
E = 0
F = 0
G = 0
H = 0
J = 0
K = 0
L = 0
Z = 0
Y = 0
O = 0
T = 0
p = 0
q = 0
r = 0
X = 0
s = 0
stat = 0

For i = 1 To n
X = X + (rPlage.Cells(i) / n)
Next i

For i = 1 To n
s = s + (rPlage.Cells(i) - X) ^ 2
Next i
s = s / (n - 1)
s = s ^ (1 / 2)

For i = 1 To n
p = p + (((rPlage.Cells(i) - X) ^ 3) / n)
Next i

For i = 1 To n
q = q + (((rPlage.Cells(i) - X) ^ 2) / n)
Next i

For i = 1 To n
r = r + ((((rPlage.Cells(i) - X) / s) ^ 4))
Next i

O = p / (q ^ (3 / 2))
A = O * (((n + 1) * (n + 3)) / (6 * (n - 2))) ^ (1 / 2)
B = (3 * (n ^ 2 + 27 * n - 70) * (n + 1) * (n + 3)) / ((n - 2) * (n + 5) * (n + 7) * (n + 9))
C = (2 * (B - 1)) ^ (1 / 2) - 1
D = C ^ (1 / 2)
E = 1 / ((Log(D)) ^ (1 / 2))
F = A / (2 / (C - 1)) ^ (1 / 2)
Z = E * Log((F + (F ^ 2 + 1) ^ (1 / 2)))

T = r * (n * (n + 1)) / ((n - 1) * (n - 2) * (n - 3)) - (3 * (n - 1) ^ 2) / ((n - 2) * (n - 3))
G = (24 * n * (n - 2) * (n - 3)) / (((n + 1) ^ 2) * (n + 3) * (n + 5))
H = (T * (n - 2) * (n - 3)) / ((n + 1) * (n - 1) * (G ^ (1 / 2)))
J = ((6 * (n ^ 2 - 5 * n + 2)) / ((n + 7) * (n + 9))) * (((6 * (n + 3) * (n + 5)) / (n * (n - 2) * (n - 3))) ^ (1 / 2))
K = 6 + (8 / J) * ((2 / J) + ((1 + (4 / (J ^ 2))) ^ (1 / 2)))
L = (1 - (2 / K)) / (1 + H * ((2 / (K - 4)) ^ (1 / 2)))
Y = ((1 - (2 / (9 * K)) - (L ^ (1 / 3))) / ((2 / (9 * K)) ^ (1 / 2)))

stat = (Z ^ 2) + (Y ^ 2)

STATAGOSTINO = stat

End Function

************************************************************­***********

6 réponses

Messages postés
351
Date d'inscription
mercredi 21 juillet 2004
Statut
Membre
Dernière intervention
12 août 2009

Bonjour

Moi je commencerai par repérer quelle est la ligne qui pose problème. (mode Debug ligne par ligne après la dernière boucle for)
Une fois la ligne déterminée, contrôler qu'il n'y a pas un dépassement (de capacité?).

thierry la fronde
Messages postés
4
Date d'inscription
lundi 29 septembre 2008
Statut
Membre
Dernière intervention
30 septembre 2008

Merci pour l'idée, seulement, je n'ai pas trouvé comment faire étape par étape en 'Debug Mode'.
Mais pas grave, je l'ai fait "à la main" et ce sont effectivement les calculs de X, s, p, r et q (les pseudo-boucles) qui posent problème : au delà de 210 valeurs, c'est une erreur "#VALUE!" qui apparaît si j'appelle la valeur de X, s, p, r ou q comme résultat de la fonction.

C'est fou, quoi ; ça me paraît bas comme limite de calcul...
C'est matériel ou logiciel ?
Messages postés
4
Date d'inscription
lundi 29 septembre 2008
Statut
Membre
Dernière intervention
30 septembre 2008

N'importe quoi, désolé, les boucles marchent trés bien, c'est la ligne de calcul de B qui semblent poser problème.
Je vais essayer de la saucissoner un peu.
Messages postés
43
Date d'inscription
samedi 20 septembre 2008
Statut
Membre
Dernière intervention
1 octobre 2008

Salut,

dis moi as tu deja essaye a la main de faire cette operation ?

B = (3*(210^2+27*210-70)*211*213)/(208*215*217*219))

A mon avis B devrait etre egal a 3.15 etc...

Mais en fait ce n'est pas le cas, car pendant ton initialisation de B, tu as declaré B=0, et tu l'as transforme implicitement en Long..., limite a 2147483647 or
(210^2*27*210-70)*211*213 depasse cette limite !!!!

Donc je te conseille d'essayer d'initialiser B et toutes les autres variables ainsi :

B = 0#

J'ai pas teste mais ca devrait aller mieux

Quel mal de crane ton truc ! J'espere que c'est la solution...

bOnn e soiree

_____________________
No problems, only soluces
Messages postés
14694
Date d'inscription
vendredi 14 mars 2003
Statut
Modérateur
Dernière intervention
14 août 2020
144
Bonjour,

[../auteur/68MANU/1479144.aspx 68manu]> B est explicitement déclaré en Double (voir le code), donc, il ne sera jamais Long.

http://nhen0039.chez-alice.fr/index.php
Messages postés
4
Date d'inscription
lundi 29 septembre 2008
Statut
Membre
Dernière intervention
30 septembre 2008

Bonjour à tous,
Et merci pour vos propositions qui m'ont sorti de cette affaire.

Je penserai maintenant à chercher la ligne qui pose problème avant de poster.

En fait, ce qui semble l'avoir bloqué, ce sont les lignes trop longues, avec les (n-1)*(n+5)^2*etc...
Il m'a fallu réécrire la fonction en opérant une seule opération (sic) par ligne.
Donc c'est beaucoup plus long, c'est pas beau, mais ça marche avec mes 30000 lignes maintenant.

Merci à tous,
@*+