Discussion :

[charlesverhelst]

Bonjour à tous,

Je rencontre un problème sur mon script VBA qui fonctionné jusqu'à présent sur mon ancien Excel 2010 et depuis la mise à jour en version XL 365 cela ne fonctionne plus.

Dès que je valide ma saisie il me marque :

Erreur d'exécution ......... La méthode '_Default' de l'objet 'Range' a échoué

J'ai essayé avec ma version Cac Excel 365 et la ça marche mais pas sur Windows...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
'Procédure permettant d'ajouter un nouvelle enregistrement dans la base de donnée
'********************************************************************************
Private Sub btnAjout_Click()
 
    'Activation de la feuille dans laquelle seront ecrites les informations
    Sheets("AR Distributeur").Activate
 
    'sélection de la case dans la quelle sera saisie l'information
    Range("A1").Select
 
    'recherche de la premiere case vide
    While Selection <> ""
        Selection.Offset(1, 0).Select
    Wend
 
    'Remplissage des cases avec ce qui a été saisi dans les cases
    ActiveCell = txtNArc.Value
    ActiveCell.Offset(0, 1) = cboExercice.Value
    ActiveCell.Offset(0, 2) = txtDateARC.Value
    ActiveCell.Offset(0, 3) = txtDateModif.Value
    ActiveCell.Offset(0, 4) = cboDept.Value
    ActiveCell.Offset(0, 5) = cboGroupement.Value
    ActiveCell.Offset(0, 6) = cboDistributeur.Value
    ActiveCell.Offset(0, 7) = txtVendeur.Value
    ActiveCell.Offset(0, 8) = cboRubrique.Value
    ActiveCell.Offset(0, 9) = cboFamille.Value
    ActiveCell.Offset(0, 10) = cboModele.Value
    ActiveCell.Offset(0, 11) = txtProduit.Value
    ActiveCell.Offset(0, 12) = txtCA.Value
    ActiveCell.Offset(0, 13) = txtActionCom.Value
    ActiveCell.Offset(0, 14) = cboCommande.Value
    ActiveCell.Offset(0, 15) = txtRefDistri.Value
    ActiveCell.Offset(0, 16) = cboSAndG.Value
    ActiveCell.Offset(0, 17) = txtDateLivraison.Value
 
    MsgBox "AR à bien été ajouter dans la base de donnée", vbOKOnly + vbInformation, "CONFIRMATION"
 
End Sub

Merci à vous pour votre aide.

[mfoxy]

Bonsoir Charles,

Ce serait déjà bien de donner ligne sur laquelle bug le code.

Il faut aussi au maximum éviter les Select et activate dans le code, ainsi que d'utiliser un/des table(s) structurée.

Bat,

[charlesverhelst]

Voici ce que j'ai lorsque je valide le formulaire et après le logiciel beug et redémarre.

Comment faire pour éviter les Select et Activate du code. Je suis preneur pour une structuration de mon code.

Je suis débutant dans ce type de code. Je l'ai monté à l'aide de tuto.

Merci à vous par avance.

[mfoxy]

Bonjour,

Tu appuies sur débogage, ça va ouvrir ton code et surligné la ligne posant souci en jaune.
Cette ligne nous permettra de mieux cibler les possibles sources du souci.

Pour éviter les select et activate, il faut soit travailler avec des object listobject, soit indiqué la parentalité de tes ranges.
Très rare sont les cas où select et activate sont indispensables, souvent sources d'erreurs et ralentissement.

Je te conseillerais aussi de ne pas te fier, dans ton apprentissage,a de pseudos tuto You Tube qui n'ont (pour la plupart) absolument rien de pro, comme dirais un de mes anciens prof : "La culture,c'est comme la confiture,moins tu en as plus tu l'étales".

Si tu souhaites, un bon tuto au niveau de l'interaction Usf et tableaux, je te conseil de faire un petit tour sur le blog de Pierre Fauconnier, une vraie mine d'or, dont un billet très pros et facilement exploitable : interaction usf et tableaux structuré.

Tout les "Tutos" de ce site sont validés par des experts avant leur publication, tu pourrais donc tout à fait te servir de ceux-ci pour tes futurs développements.

Bat,

[charlesverhelst]

Merci beaucoup pour tes conseils.

Je n'arrive pas à appuyer sur débogage Excel beug directement et se ferme. Ce qui est bizarre c'est que sur mon Mac le fichier fonctionne parfaitement et par sur mon Pc Windows. Est-ce normal?

[Alex020181]

Bonjour,

Comme dit précédemment il y a peu de chances qu'on trouve l'erreur si on ne sait déjà pas sur quelle ligne précisément Excel plante.
De ce que je comprends tu n'arrives pas à la fenêtre de code en cliquant sur le bouton "débogage". Peux-tu joindre un fichier exemple ici et nous dire précisément comment reproduire cette erreur ?

[Pierre Fauconnier]

Salut.

Le message d'erreur juste sur la ligne qui pose problème, c'est quand même très moyen


La kyrielle d'Offset qui couple très fortement le code à la structure de ta feuille, c'est moyen aussi. Si tu ajoutes, déplaces, permutes ou supprimes des colonnes, tu vas avoir un paquet de lignes à modifier...

Il faut à mon sens travailler avec des tableaux structurés et utiliser les références structurées et les listobjects dans ton code. Tu gagneras en écriture de code et en maintenance. J'ai écrit un billet qui explique justement comment passer des valeurs à une nouvelle ligne d'un tableau structuré... Tu as également cette contribution qui illustre les interactions entre un userform et un tableau structuré


Merci Michaël pour la pub

[Alex020181]

La fonction Val() permet de convertir un nombre au format texte en nombre au format nombre.

Es-tu sûr que txtarc a un nombre comme valeur ?

Essaie de placer cette ligne
Msgbox txtarc
Juste au dessus de la ligne qui plante. Cela affichera la valeur de txtarc à l'écran et tu pourras contrôler.

[Pierre Fauconnier]

Deux autres façons de vérifier la valeur d'une variable sur une ligne en jaune:

• Placer le pointeur de la souris sans cliquer sur la variable ou l'objet à inspecter, et une infobulle donnera la valeur de ce sur quoi on pointe;
• Utiliser la fenêtre d'exécution (CTRL+G) et y saisir ? MaVariable puis ENTER pour afficher la valeur à la ligne suivante.

Cela évite le msgbox ... On peut aussi placer un simple Stop pour arrêter le code de façon inconditionnelle à un endroit précis du code, ou placer la ligne Debug.Assert MaCondition qui stoppera le code de façon conditionnelle lorsque MaCondition renverra False.

Je rappelle qu'en 2020 et avec Excel 365, on travaille avec des tableaux structurés et que le transfert USF-TS peut être doit être rendu générique. C'est pour moi une ineptie de pondre plein de lignes de codes à chaque projet alors que l'on peut, avec un tout petit peu de construction, utiliser des procédures génériques qui permettent de gagner du temps et de minimiser les erreurs (tant lors de l'élaboration du code que lors de la maintenance et de l'évolution du projet).

Normalement, la vérification des valeurs saisies dans le userform devraient être réalisées en amont de leur transfert dans un tableau structuré, par deux types de vérification, l'une dans le userform pour vérifier que ce qui a été saisi, par nature de type String pour les textbox, contient bien des valeurs du bon type (c-a-d qui pourront être transformées en valeur du bon type par la suite), et une vérification métier (a priori par appel d'une fonction hors userform) pour la cohérence des données avec les règles de gestion. Mais ces vérifications doivent être effectués AVANT le transfert en DB.

Si on bugue au moment du transfert, on a peut-être déjà ajouté une ligne, transféré des valeurs et il va falloir tout détricoter. L'ordre d'exécution est le suivant:

• Saisie des données;
• Vérification que les données sont du type attendu;
• Vérification que les données respectent les règles de gestion;
• Transfert en DB.

[Alex020181]

Je ne connaissais pas debug.assert.
Concernant les tableaux structurés cela demande une mise en place côté utilisateur qui n'est pas faite ici d'après ce que je vois.
En plus, sans critiquer leur utilité, j'ai déjà utiliser ces tableaux avec du Power Query sur un fichier de suivi de budget et j'ai vu la taille du fichier 3Mo, son délai d'ouverture et son temps de traitement augmenter considérablement.
La boite m'a alors demandé de faire en sorte que le fichier soit plus léger et rapide. J'ai tout remis en cellule standard avec des formules et le fichier est passé à quelques Ko et bien plus rapide (sans lignes de code).

Alors les tableaux structurés je dis oui mais seulement si réellement ils apportent un plus sans être pénalisant par ailleurs.
Ici pas besoin (du moins pour la demande précise mais peut-être que l'ensemble du projet y serait gagnant).

[Alex020181]

Je suis par contre pleinement d'accord avec toi concernant la vérification des valeurs des variables.
Un autre type de vérification également: son typage lors de sa déclaration.

[Pierre Fauconnier]

Même sans tableaux structurés, on peut généraliser l'approche et travailler avec une boucle plutôt que des lignes d'offset

après, houlàlà, deux clics pour créer un tableau structuré et un fichier Excel de 3 Mo... Quelle horreur

[Alex020181]

Oui. Je n'utilise moi-même jamais les offsets et pas non plus les fourmules r1c1

[Pierre Fauconnier]

Quant au délai d'ouverture, ça me fait un peu rire lorsque je vois le temps que les gens passent à la machine à café... Et ça permet aux gens de vite regarder si on a liké leur dernière intervention débile sur FB...

Que dans certaines situations, il faille optimiser au point de ne pas utiliser les tableaux structurés, je veux bien l'admettre (quoique). Mais de là à en faire une généralité et se priver, tant du côté Excel que côté VBA, de la souplesse d'utilisation de cet outil, il y a un pas que perso, je ne franchis pas.

Quant au fait que "cela demande une mise en place"... C'est vrai que ça demande tout de même 3 clics de souris... Probablement un argument suffisant pour ne pas y passer

[Alex020181]

Je ne dis pas que je ne les utilises pas du tout ni que je ne me suis pas rendu compte de leur utilité. Je dis que, dans le cas présent pour répondre à la question ils ne servent pas.

Comme j'ai mis plus haut je dis bien qu'ils seraient peut-être profitable pour le projet de l'utilisateur dans son ensemble. Mais là la question posée n'est pas sur ce point.
Je n'en suis pas réfractaire. Par contre ils doivent être utilisés correctement et à bon escient.

[Pierre Fauconnier]

Je dis que, dans le cas présent pour répondre à la question ils ne servent pas.[...]

Je suis d'accord avec toi, mais si une architecture correcte de code (et de process) était respectée, on ne devrait pas tester qu'une donnée est valide au moment où on la transfère, mais en amont.

Quant au fait que txtarc contienne une valeur ou non, je ne vois pas trop le lien avec le message d'erreur qui parle de déconnexion. Surtout qu'une cellule Excel peut recevoir sans broncher plusieurs types de valeurs.

Il sera peut-être intéressant que Charles se manifeste

[Alex020181]

Si l'erreur interviens sur cette ligne je cherche à en connaitre toutes les composantes.
Je tatonne.
Le mieux serait d'avoir un exemple de fichier.

[Pierre Fauconnier]

Alex,

Je suis d'accord avec toi. Dans le cas présent et sans retour de Charles, on ne sait pas dire grand-chose. Nous lui avons donné des pistes, et à part attendre le résultat de ses tests, nous ne savons rien faire.

Raison de plus pour profiter de la discussion et échanger des arguments techniques sur les solutions proposées. Dans la mesure où la présente discussion sera lue par un grand nombre de personnes, il y a toujours bien quelqu'un à qui cela profitera

[Pierre Fauconnier]

Si l'on veut se passer des tableaux structurés, on peut quand même rendre la procédure générique, soit en liant un tout petit peu le code à l'emplacement des données, soit en passant par une plage nommée.

Voici un code de transfert possible, parmi d'autres. On remarque qu'ici on ne parle pas de la position des données, ni de la feuille sur laquelle on va les placer. On reçoit un userform, une plage qui correspond à la plage d'intitulés de la plage de stockage, et un tableau de valeurs qui contient les paires de valeurs Colonne/Contrôle... Les valeurs sont placées "à la queue leu leu" et la procédure boucle dessus par pas de 2.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub AddDataFromUserform(usf As msforms.UserForm, Header As Range, map)
  Dim r As Range
  Dim Col
  Dim i As Long, j As Long
 
  If Header.CurrentRegion.Rows.Count = 1 Then
    Set r = Header.Offset(1)
  Else
    Set r = Header.Offset(Header.CurrentRegion.Rows.Count)
  End If
 
  For i = LBound(map) To UBound(map) Step 2
    Col = Application.Match(map(i), Header, 0)
    If Not IsError(Col) Then r(Col).Value = usf.Controls(map(i + 1)).Value
  Next i
End Sub

Dans un userform doté de deux contrôles tboFirstname et tboLastname, on utilise une procédure qui appelle la procédure ci-dessus en lui passant les paramètres.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Private Sub btnValidate_Click()
  TransferData
End Sub
 
Sub TransferData()
  AddDataFromUserform Me, Feuil1.Range("d3:e3"), Array("nom", "tbolastname", "prénom", "tbofirstname")
End Sub

On voit que TransferData fait ici référence à une plage précise d'une feuille, mais on pourrait côté Excel nommer cette plage, par exemple ContactHeader pour pouvoir la déplacer sans devoir modifier le code. On retrouve alors la souplesse de l'utilisation du tableau structuré et les boucles qui évitent les offset et le couplage fort code/Feuille

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Sub TransferData()
  AddDataFromUserform Me, Range("ContactHeader"), Array("nom", "tbolastname", "prénom", "tbofirstname")
End Sub

Il fonctionne dans une configuration "correcte" (rien ne touche la plage des intitulés à gauche, en haut ou à droite) et , a priori, la plage de données ne contient pas une ligne de total. Bien sûr, on pourrait écrire du code pour tenir compte de ces cas, mais l'important n'est pas là. Le code exposé ci-dessus illustre qu'avec ou sans tableaux structurés, on a souvent intérêt, par économie de code et donc de risques de bugs, de rendre son code générique en utilisant le moins possible de codage "métier" en dur.