XL 2013 Alléger une feuille Excel comprenant (beaucoup) des formules

[Surcouf]

Bonjour a tous,
Je suis nouveau sur ce Forum et d'ailleurs peu habitué à les consulter, donc je risque peut-être de ne pas respecter scrupuleusement la charte d'utilisation. je m'en excuse d'avance.

J'ai créé un programme d'offre (Excel VBA) pour ma société et les filiales belges que je remets à jour chaque année.
Ce programme contient une base de donnée de +/- 3000 lignes d'articles avec 10 colonnes d'informations par article que nos partenaires ont voulu intégrée dans un onglet de la feuille Excel.
La réalisation d'une offre nécessite, lors du choix d'un article, une multitude d'information puisée d'une part dans la base de donnée et sur d'autres onglets comprenant des résultats de calculs. Soit en tout 62 colonnes par ligne d'article et une offre peut comprendre 3000 articles.

la plupart des 62 cellules comprennent ce genre de formule
=SI(B7="";"";INDEX(INDIRECT(RECHERCHEH(C7;entry1!$P$6:$R$11;2;FAUX));BI7;BJ7))
ou
=SIERREUR(RECHERCHEV(AH20;FDO!$C$26:$M$420;10;0);"code fdo!")
etc

Le problème :
Au delà de 2000 lignes pour une offre, la feuille devient très lente. Le résultat,après l'encodage d'un article, apparaît endéans les 2 secondes. c'est beaucoup trop long.

Les questions :
Le fait de supprimer l'onglet de la base de donnée du fichier excel et utiliser une base SQL pourrait-il alléger le processus ?
Pourrait-on également utiliser une autre syntaxe dans les formules reprise ci-dessus ?

Remarques:
On ne peut désactiver le calcul automatique car le contrôle d'erreur serait inactif.

Software:
Excel 2013 et 2016 64 bit
Windows 10 et Windows 7

J'espère ne pas avoir été trop nébuleux dans mes explications.
Toute réponse est la bienvenue.

Merci.

 

[Dranreb]

Bonjour.
Vous auriez intérêt à écrire une macro utilisant un Dictionary

 

[Dranreb]

Bonjour.
Il serait même possible d'écrire une fonction qui utilise un Dictionary :

Option Explicit
Private Dico As Dictionary

Function ValDico(ByVal Clé) As Variant
Dim TD(), TV(), L As Long, C As Long
If Dico Is Nothing Then
   On Error Resume Next
   TD = [PlageDico].Value
   If Err Then MsgBox "Le nom ""PlageDico"" n'est pas défini", vbCritical, "ValDico": Exit Function
   On Error GoTo 0
   Set Dico = New Dictionary
   If UBound(TD, 2) > 2 Then
      ReDim TV(1 To 1, 1 To UBound(TD, 2) - 1)
      For L = 1 To UBound(TD, 1)
         For C = 1 To UBound(TD, 2) - 1: TV(1, C) = TD(L, C + 1): Next C
         Dico(TD(L, 1)) = TV: Next L
   Else
      For L = 1 To UBound(TD, 1)
         Dico(TD(L, 1)) = TD(L, 2): Next L
      End If: End If
If Dico.Exists(Clé) Then ValDico = Dico(Clé)
End Function

Nécessite la référence Microsoft Scripting Runtime

 

[merinos-BernardEtang]

Salut,

Tu peux aussi travailler en recherchant d'abord la ligne du produit (fonction MATCH) et garder ce résultat dans une colonne (par exemple E)
puis travailler par OFFSET depuis le debut du tableau.

Tu n'aurais qu'une fonction MATCH par article et un OFFSET par donnée qui doit être reprise.

 

[mictian]

Bonjour Dranreb,

Bonjour.
Il serait même possible d'écrire une fonction qui utilise un Dictionary :

Je suis dans le même cas que Surcouf. J'ai des tableaux bardés de formules du type RECHERCHEV, INDEX, EQUIV... et le problème c'est que dans l'idée, je dois réaliser un fichier avec ce genre de tableau à la maille hebdomadaire sur 30mois, soit 130 onlgets... autant dire qu'excel n'arrive même pas à me les créer.

J'ai commencé à alléger toutes mes mise en forme conditionnelles et limiter les lignes et colonne inutiles, les ajout de colonne entières, à n'effectuer mes calcul qu'en cas de besoin avec des Si(A1<>0, RECHERCHEV,"") pour éviter de faire ces recherches inutilement, mais c'est malheureusement insuffisant.

Je viens de tomber sur ton poste qui m'interesse donc grandement. L'ennui, c'est qu'autant je connais pas mal VBA, les formules... mais j'ai jamais entendu parlé de Dictionary. Pourrais tu expliciter qu'elle serait le principe, appliqué à l'allègement de fichier ?

Merci pour ton aide,

 

[Dranreb]

Bonjour.
Voir poste #3: une fonction qui utilise un Dictionary du fichier scrrun.dll (bibliothèque Scripting, mise en ligne par référence Microsoft Scripting Runtime cochée)