Traduction Excel / VBA inopérante

**Henri1830** · 04/12/2014, 08h58

Bonjour,

Je suis toujours débutant et je me heurte à un souci. En voici la description:

J'utilise avec succès en Excel une formule qui me permet de trouver l'emplacement du dernier point dans un nom de fichier:

Code :

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

=CHERCHE("µ";SUBSTITUE(A3;".";"µ";NBCAR(A3)-NBCAR(SUBSTITUE(A3;".";""))))

Je l'ai fait traduire par 2 traducteurs (VBA et http://fr.excel-translator.de) ils sont d'accord et cela donne ceci:

Code :

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

=Search("µ", Substitute(A3, ".", "µ", Len(A3) - Len(Substitute(A3, ".", ""))))

Je l'ai aménagé pour les besoins du programme en :

Code :

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

nPoint = Search("µ", Substitute(A3, ".", "µ", Len(A3) - Len(Substitute(A3, ".", ""))))

Mais lorsque je lance VBA, il met le second "Substitute" en surbrillance et affiche ce message d'erreur :

Erreur de compilation: Sub ou Fonction non définie

J'ai l'impression que le seul obstacle est mon inculture, mais je ne trouve pas la solution. Quelqu'un peut m'aider ?

Merci d'avance.

Henri

**Nako_lito** · 04/12/2014, 10h15

Salut,
simplement par ce que subtitute ne doit pas être une méthode VBA.

utilise la méthode StrReverse qui va retourner la chaine, par exemple si tu as azerty.exe, il va te donner exe.ytreza
A partir de là, tu utilise la méthode InStr pour trouver le premier point de ta chaine, et tu finis par une soustraction de la longueur de la chaine originale - la position + 1 pour obtenir l'index du dernier caractere "."

Ce qui peut donner :

Code :

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

Len("maChaine.txt") - InStr(StrReverse("maChaine.txt"), ".") + 1

**vanhoa** · 04/12/2014, 10h25

Le substitute en VBA est "replace"

**Nako_lito** · 04/12/2014, 10h37

également, je viens de remarquer, mais ton convertisseur a convertie ta formule francaise en formule anglaise.
Le subtitute n'existe pas en vba, le search non plus.

**vanhoa** · 04/12/2014, 10h49

Pour ma part, dans vba, "replace" traduit la meme chose que substitute (en anglais) ou substitue (en francais) dans excel.

si je fais le code suivant:

Code :

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

1
2
3
4
5
6
7
8
9
Sub testtxt()
Dim vartxt As String
 
vartxt = "asd"
vartxt = Replace(vartxt, "s", "w")
 
Debug.Print vartxt
 
End Sub

cela transforme bien "asd" en "awd"

**Henri1830** · 04/12/2014, 10h52

Génial ! Ça fonctionne très bien !
Tous mes remerciements pour votre aide.
Donc, si je comprends bien toutes les fonctions disponibles en Excel ne le sont pas en VBA ?

Une sous-question:
Quelle est l'instruction qui permet de faire appel depuis une macro à une autre macro ?
Exemple, la macro me permet de savoir si je suis dans un fichier XLS ou XLSX et donc de varier la hauteur de mes colonnes en fonction des cas.
C'est typiquement le genre de fonctionnalité qui se réutilise.
Plutôt que de la copier/coller à chaque fois, comment fais-je pour l'exécuter, tout en modifiant la variable cCol, depuis la macro en cours ?

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
 
Sub LongueurColonne()
Dim cNomFichier As String
Dim cCol As String
Dim cFond As String
Dim nPoint As Long
 
cNomFichier = ActiveWorkbook.Name
nPoint = Len(cNomFichier) - InStr(StrReverse(cNomFichier), ".") + 1
 
cCol = "C"
If Len(cNomFichier) - Point = 4 Then
cFond = cCol & "1048576"
Else
cFond = cCol & "16384"
End If
 
End Sub

**vanhoa** · 04/12/2014, 11h00

non pas toutes, il n'y a pas longtemps j'ai cherche un equivalent de indirect, sans succes :-(
Mais pour tes fonctions, CHERCHE c'est worksheetfunction.search et SUBSTITUE c'est replace.
Pour les utiliser, utilise l'aide excel qui te dit quels sont les arguments. cela traduit la meme chose que ce que tu cherchais.

exemple:

Code :

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

1
2
3
4
5
6
7
8
9
10
Sub testtxt()
Dim vartxt As String
 
vartxt = "asd"
vartxt = Replace(vartxt, "s", "w")
 
msgbox vartxt     'ma variable vartxt est bien devenue "awd"
msgbox WorksheetFunction.Search("w", vartxt, 1)  'cela me donne 2, soit l'emplacement du "w" dans "awd"
 
End Sub

Pour ta sous question, tu peux directement inscrire le nom de ta procedure (donc de ton autre macro) dans la premiere macro.

Sinon d'apres ton exemple, tu veux simplement pouvoir modifier cCol sans devoir reecrire la macro n fois si tu a n valeurs differentes de cCol c'est bien ca?
si c'est ca, tu peux mettre en debut de ta procedure l'instruction "inputbox" qui va te demander d'ecrire une valeur.
exemple essaie:

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
Sub LongueurColonne()
Dim cNomFichier As String
Dim cCol As String
Dim cFond As String
Dim nPoint As Long
 
inputbox("valeur cCol","cCol = ",2)
 
cNomFichier = ActiveWorkbook.Name
nPoint = Len(cNomFichier) - InStr(StrReverse(cNomFichier), ".") + 1
 
cCol = "C"
If Len(cNomFichier) - Point = 4 Then
cFond = cCol & "1048576"
Else
cFond = cCol & "16384"
End If
 
End Sub

**Henri1830** · 04/12/2014, 13h53

Merci pour la réponse.

Pour ce qui concerne la sous-question, si j'ai bien compris, si le nom de la macro récurrente est LongueurColonnes(), il suffit de taper LongueurColonnes() dans ma macro d'origine pour que LongueurColonnes() s'exécute et qu'ensuite la suite de la procédure de la macro d'origine continue son exécution ?

Pour ce qui est de la colonne, c'est plus compliqué.
Je souhaite créer une macro réutilisable dans toutes les petites macro que je développe.
Entre autres, si je veux définir un nom, il me faut connaître la plage de cellules concernées. Le seul moyen que j'ai trouvé pour être sûr de connaître la dernière cellule utilisée de la colonne, est de descendre à la dernière ligne de la colonne et de remonter. En code, ça donne:

Code :

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

1
2
3
 
    Range("C1", Range(cFond).End(xlUp)).Select
    Names.Add Name:=Range("$C$1"), RefersTo:="='" & ActiveSheet.Name & "'!" & Selection.Address

Ça fonctionne très bien, pas de souci.
Mais mon cFond peut tantôt être valorisé à 16384 ou à 1048576 en fonction du type de fichier dans lequel je suis.
Raison d'être de la petite macro ci-dessous.

Par ailleurs, cette procédure est susceptible d'être reprise plusieurs fois dans la même macro, il me faut donc pouvoir changer de colonne (d'où ma question de tout à l'heure).
La solution de l'inputbox est intéressante, mais elle ne convient pas dans ce cas-ci où tout se passe en automatique, sans intervention de l'utilisateur.

Je dois donc valoriser ma variable dans ma macro d'origine et l'envoyer en paramètres vers LongueurColonnes().

Comprends-tu ma douleur ?

As-tu une solution ?

Encore merci pour ton aide...

Henri

**Nako_lito** · 04/12/2014, 15h35

Effectivement, quand on traite des fichiers excel, on est souvent confronté a trouver la derniere ligne d'une colonne.

Dans ce cas, je te propose de créer une function qui sera appelé d'ou tu veux et qui prends deux paramètres : la feuille sur laquelle il faut regarder et la colonne, ensuite tu créé simplement une function a part dans ton module comme ceci:

Code :

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

1
2
3
Public Function derniereLigne(ByVal pWS As Worksheet, pColumn As Integer) as integer
    derniereLigne = pWS.Cells(65534, pColumn).End(xlUp).Row
End Function

dans ta méthode main tu peux l'appeler comme suit :

Code :

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

1
2
3
4
5
6
 
public sub laMethodePrincipale()
 
   MsgBox "La dernière ligne utilisée de la feuille 1 en colonne 1 est : " & derniereLigne(Sheets(1), 1), vbOKOnly
 
end sub

Si tu veux être plus générique, tu peux carrement renvoyer la cellule, avec toute les propriete que ca implique (address, value, text, size etc.etc.)

Au quel cas il faut adapter le type de retour de la function derniereLigne et son usage dans la méthode main.

**Henri1830** · 04/12/2014, 16h31

C'est très intéressant, mais comme je l'ai annoncé, je suis débutant, ce qui fait que là, je suis complètement largué

Mes questions:
Dans ce code:

Code :

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

1
2
3
Public Function derniereLigne(ByVal pWS As Worksheet, pColumn As Integer) as integer
    derniereLigne = pWS.Cells(65534, pColumn).End(xlUp).Row
End Function

J'imagine que je peux remplacer 65536 par la variable cFond qui me renvoie la dernière ligne ? Mais comment lui passer le paramètre cFond depuis la méthode principale ?
Comment valoriser pColumn ? C'est-à-dire, lui faire comprendre de me trouver la dernière ligne utilisée par la colonne X ou Y ou Z en fonction des besoins du moment ?
J'ai tenté de faire tourner laMéthodePrincipale, il me donne le message d'erreur : Erreur de compilation, argument non facultatif.

Merci pour ton aide.

**Nako_lito** · 04/12/2014, 16h37

Hum ok, mais là, c'est pas une explication sur ce que je viens de te donner qu'il faut, c'est un cours de base.

Regarde ce tuto, tu comprendra sans doute bcp plus de chose que ce que je pourrais t'expliquer : Cours Excel.

Comprends bien que ce n'est pas un refus de ma part, juste que ton niveau n'est pas assez haut pour que tu puisse interpréter ce qu'on donnera comme info ici.
Lis bien ce tuto, tu apprendra bcp de chose. Tu auras même certainement bcp de réponses a des questions que tu te pose, et mon explication précédente prendra tout son sens quand tu atteindra le chapitre 6 sur les procédures et fonctions.

Bonne chance et bon apprentissage.

**Henri1830** · 04/12/2014, 21h58

Ben oui, débutant, ça dit bien ce que ça veut dire...
Merci pour le tuyau, je vais étudier.

**AlainTech** · 08/12/2014, 05h06

Bonjour,

Pas besoin d'une fonction pour déterminer le nombre max de lignes d'une feuille.

Code :

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

1
2
3
Sub Test()
  MsgBox ActiveSheet.Rows.Count
End Sub

Ce code fonctionnera quelle que soit la version du classeur.
J'ai utilisé ActiveSheet pour la démo. A remplacer par l'objet Worksheet concerné.

**Nako_lito** · 08/12/2014, 10h01

Non, si tu fais ça, tu auras 1048576 comme nombre de lignes. Or ce n'est pas ce nombre qu'on souhaite obtenir.
Le 1048576 correspond au nombre de ligne disponible dans la feuille en question.

1048576 pour les fichiers excel 2007 et ultérieur
65536 pour les fichiers excel jusqu'a 2003.

L'autre alternative à ma fonction décrite plus haut serait d'utiliser

Code :

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

activesheet.UsedRange.Row

**AlainTech** · 08/12/2014, 10h23

Nako_lito,

As-tu bien compris le but d'Henri1830?

Sa fonction cherche à déterminer la longueur max d'une colonne en fonction du type de classeur.
C'est précisément à cette question que je réponds.

**Nako_lito** · 08/12/2014, 10h54

au temps pour moi.

**Henri1830** · 09/12/2014, 09h58

Merci à Alain et à Nako_lito pour les informations.

Alain : génial, c'est exactement ce que je cherchais et j'ai trouvé encore autre chose de pas mal non plus :

Code :

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

1
2
3
4
5
6
 
MsgBox Cells.SpecialCells(xlCellTypeLastCell).Row ' donne le n° de la dernière ligne occupée de la colonne la plus longue
MsgBox Cells.Find("*", , , , , xlPrevious).Row ' donne le n° de la dernière ligne de la dernière colonne occupée
MsgBox Cells.Find("*", , , , xlByColumns, xlPrevious).Column ' donne le n° de la dernière colonne occupée
MsgBox Columns(3).Find("*", , , , , xlPrevious).Row ' N° de la dernière ligne occupée de la colonne C
MsgBox Rows(2).Find("*", , , , , xlPrevious).Column ' N° de la dernière colonne occupée sur la ligne 2

Nako_lito : merci pour le cours, j'ai appris beaucoup, mais il me manque la structure de base : qu'est-ce qu'une méthode, une fonction, etc... les différences entre tous ces outils, comment s'organise la syntaxe, etc... ça m'aiderait beaucoup.
Mais bon, Rome ne s'est pas faite en un jour et la méthode empirique a ses charmes aussi...

Encore merci à vous deux pour votre aide.

Henri

Invité · 09/12/2014, 10h26

Salut,

Envoyé par Henri1830

...
Je l'ai aménagé pour les besoins du programme en :

Code :

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

nPoint = Search("µ", Substitute(A3, ".", "µ", Len(A3) - Len(Substitute(A3, ".", ""))))

Mais lorsque je lance VBA, il met le second "Substitute" en surbrillance et affiche ce message d'erreur :

J'ai l'impression que le seul obstacle est mon inculture, mais je ne trouve pas la solution. Quelqu'un peut m'aider ?
...

Envoyé par Henri1830

Donc, si je comprends bien toutes les fonctions disponibles en Excel ne le sont pas en VBA ?

Essai comme ceci:

Code :

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

1
2
3
4
 
Sub EvalFunction()
    nPoint = [= Search("µ", Substitute(A3, ".", "µ", Len(A3) - Len(Substitute(A3, ".", ""))))]
End Sub

Cela fonctionne, par contre, je n'ai pas vérifié ce que fait la fonction elle-même. Elle fait ici la même chose que ta 1ère fonction Excel:

Code :

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

=CHERCHE("µ";SUBSTITUE(A3;".";"µ";NBCAR(A3)-NBCAR(SUBSTITUE(A3;".";""))))

**Henri1830** · 09/12/2014, 10h54

Merci pour la variante, c'est très intéressant.
A la manière dont elle est présentée, cela peut servir pour tous les cas du genre où VBA n'a pas d'équivalent, je me trompe ?

Bien à vous

Henri

**Nako_lito** · 09/12/2014, 11h15

Tutoriel Excel : http://excel.developpez.com/cours/

Traduction Excel / VBA inopérante [XL-2013]

Macros et VBA Excel

Discussions similaires

Partager

Partager