Macro automatique toutes les minutes avec une donnée (qui varie) à C/L dans une nouvelle cellule

**fabien32200** · 18/01/2018, 17h14

Bonjour à toutes et à tous,

Je bloque sur ce code depuis quelques jours, c'est trop complexe pour mon niveau, voici ce que je souhaite faire :

Que ma macro tourne automatiquement de 9h à 18h (ce qui marche dans le code ci-dessous) et que toutes les minutes (soit 540 minutes durant cette intervalle de temps), que la donnée présente en « F8 » (donnée d’une action en direct, et donc le prix change constamment sur ma feuille Excel) soit C/L dans la colonne E (à partir de E1).

Ex :

9h00, F8 = 15,22 donc E1=15,22
9h01, F8 = 15,15, E2 = 15.15
9h02, F8= 14,01, E3 = 14,01
etc…
Jusqu’à 18h
18h, F8=22,45 (le cours a explosé

), E540 =22,45

cf. mon « code » ci-dessous (qui correspond pas au but recherché mais je n'y arrive pas)

Dans “ThisWorkbook” :

Code :

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

1
2
3
Private Sub Workbook_Open()
  Call MaMacro
End Sub

Dans "Module 1" :

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
Option Explicit
Option Private Module
Private Const heure_déb As Date = #9:00:00 AM#
Private Const heure_fin As Date = #18:00:00 PM#
 
Public Sub MaMacro()
 
  If Time < heure_déb Then
    Application.OnTime heure_déb, "MaMacro", heure_fin
    Exit Sub
  ElseIf Time < heure_fin Then
    Application.OnTime Now + TimeValue("00:01:00"), "MaMacro", heure_fin
  End If
 
Sheets("Feuil1").Range("E1").FormulaLocal = "=$F$8"
Sheets("Feuil1").Range("E1").Copy Destination:=Sheets("Feuil1").Range("E1:E540")
 
 
End Sub

Je vous remercie d’avance de votre aide...

Fabien

**patricktoulon** · 18/01/2018, 17h51

re
Bonjour j'espere que tu te rend compte que tes if sont contradictiores avec ce que tu voudrais faire
car des que tu va passer cette loigne a 09.00.01 sa ne marchera plus
et dans le if suivant c'est < que l'heure de fin

ensuite c'est quoi cette copie de une cellule dans 540 autres dans un sheets different ????

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
Option Explicit
Option Private Module
Private Const heure_déb As Date = #9:00:00 AM#
Private Const heure_fin As Date = #18:00:00 PM#
 
Public Sub MaMacro()
 
  If Time < heure_déb Then
    Application.OnTime heure_déb, "MaMacro", heure_fin
    Exit Sub
  ElseIf Time < heure_fin Then
    Application.OnTime Now + TimeValue("00:01:00"), "MaMacro", heure_fin
  End If
 
Sheets("Feuil1").Range("E1").FormulaLocal = "=$F$8"
Sheets("Feuil1").Range("E1").Copy Destination:=Sheets("Feuil1").Range("E1:E540")
 
 
End Sub

je verais plutot les chose comme cela

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Option Explicit
Option Private Module
Private Const heure_déb As Date = #9:00:00 AM#
Private Const heure_fin As Date = #6:00:00 PM#
Sub MaMacro()
If Time < heure_déb Then Application.OnTime Date + heure_déb, "MaMacro" ' si tu demarre ton fichier avant 9 heures du mat on programme le premier toutrs de garde a 09:00:00 du jours
 
If Time >= heure_déb Or Time <= heure_fin Then ' l'heure actuelle est plus grande que l'eure_deb et plus petite que l'heure fin on programe la prochaine minute 
    'blablabla copie
    '....etc....
    Application.OnTime Now + TimeValue("00:01:00"), "MaMacro"
 
End If
End Sub

ainsi si tu demarre a 8 heure ton fichier le fichier les verifs auto demarreont qu'a 09:00:00

si il est 18:00:01 et plus le tour ne sera pas reprogramé
pas plus compliqué

**fabien32200** · 18/01/2018, 18h04

Merci Patrick de ton aide,

Je sais que cette partie de mon code (ci-dessous) n'est pas bonne, mais je ne sais pas comment faire avoir ce que je souhaite:

Code :

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

1
2
Sheets("Feuil1").Range("E1").FormulaLocal = "=$F$8"
Sheets("Feuil1").Range("E1").Copy Destination:=Sheets("Feuil1").Range("E1:E540")

Il me faudrait un code qui me permettrait de C/L la valeur de F8 dans la colonne E toutes les minutes.

Exemple de ce que je souhaite faire :

La donnée de F8 de 9h00 la mettre en E1
La donnée de F8 de 9h01 la mettre en E2
La donnée de F8 de 9h02 la mettre en E3
etc...

(PS : la donnée présente en F8 varie constamment)

C'est cette partie de code à rajouter ici si je me trompe pas ("Blablabla....")?

Code :

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

1
2
3
4
    'blablabla copie
    '....etc....
    Application.OnTime Now + TimeValue("00:01:00"), "MaMacro"

**patricktoulon** · 18/01/2018, 18h16

re
il ne te sert a rien de copier, inserer directement la valeur de F8 dans la premiere ligne dispo de la colonne "E"si j'ai bien compris ce que tu cherche a faire

Code :

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

With Sheets("Feuil1"):.Range("E" & .Cells(Rows.Count, "E").End(xlUp).Row + 1) = .cells(8,"F").Value:end with

et c'est bouclé

**fabien32200** · 18/01/2018, 18h25

Merci beaucoup, je suis toujours autant surpris de la rapidité et de l'efficacité de certains... chapeau & merci !

Du coup j'ai inséré ce code :

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
Option Explicit
Option Private Module
Private Const heure_déb As Date = #6:24:00 PM#
Private Const heure_fin As Date = #7:00:00 PM#
Sub MaMacro()
 
If Time < heure_déb Then Application.OnTime Date + heure_déb, "MaMacro" ' si tu demarre ton fichier avant 9 heures du mat on programme le premier toutrs de garde a 09:00:00 du jours
 
If Time >= heure_déb Or Time <= heure_fin Then ' l'heure actuelle est plus grande que l'eure_deb et plus petite que l'heure fin on programe la prochaine minute
 
   With Sheets("Feuil1"): .Range("E" & .Cells(Rows.Count, "E").End(xlUp).Row + 1) = .Cells(8, "F").Value: End With
 
    Application.OnTime Now + TimeValue("00:00:10"), "MaMacro"
 
End If
End Sub

J'ai modifié quelques données (exécution toutes les 10sec, et l'heure de début) afin de voir si tout marche, mais rien ne se fait sur ma feuille ? J'ai loupé quelque chose ?

**Phil Free** · 19/01/2018, 09h32

Bonjour,

ce qui est intéressant c'est que quand tu écris le code en français :

' l'heure actuelle est plus grande que l'eure_deb et plus petite que l'heure fin on programme la prochaine minute

tu utilises un opérateur(conjonction de coordination en français) mais quand tu l’écris en code VBA tu en utilises un autre !!!

Code :

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

If Time >= heure_déb Or Time <= heure_fin Then

il faut comprendre que :

avec l’opérateur "Or" si l'une ou l'autre des conditions est vraie alors le code continu

avec l’opérateur "And" si et seulement si les 2 conditions sont réunies le code continu

**fabien32200** · 19/01/2018, 09h46

Mais le problème du code vient de cette partie à mon sens ...

Code :

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

1
2
3
With Sheets("Feuil1"): .Range("E" & .Cells(Rows.Count, "E").End(xlUp).Row + 1) = .Cells(8, "F").Value: End With
 
    Application.OnTime Now + TimeValue("00:00:10"), "MaMacro"

**unparia** · 19/01/2018, 10h07

Bonjour
- premier réflexe : réfléchir au mécanisme à developper. On ne développe bien que ce que l'on conçoit d'abord bien
Ainsi :
-- tu dois relancer (ontime) si pas encore heure de début, mais sans rien faire d'autre (puisque tu ne veux intervenir su'au delà de ce début)
-- tu dois traiter tes cellules et relancer si entre heure de début et heure de fin
-- tu dois cesser de relancer (ontime) si heure de fin dépassée

Tout cela est facile.
Tu aurais à ce propos intérêt à utiliser l'instruction Select Case (Rubrique Select Case, instruction de ton aide interne VBA) plutôt que des instruction If ... Then ...
Ton écriture sera ainsi facilitée et plus lisible.
Je te laisse bien évidemment faire cela d'abord seul (c'est d'un niveau à la portée de tout débutant).

**fabien32200** · 19/01/2018, 10h46

Bonjour unparia, merci de ta réponse.
Mais je ne trouve pas de problème au début du code, je ne pense pas que Patrick se soit trompé dans ces précédentes réponses...
Ce que je n'arrive pas à faire c'est le "blablabla copie" :

Il me faudrait un code qui me permettrait de C/L la valeur de F8 dans la colonne E toutes les minutes.

Exemple de ce que je souhaite faire :

La donnée de F8 de 9h00 la mettre en E1
La donnée de F8 de 9h01 la mettre en E2
La donnée de F8 de 9h02 la mettre en E3
etc...

(PS : la donnée présente en F8 varie constamment)

C'est cette partie de code à rajouter ici si je me trompe pas ("Blablabla....")?

Code :

Code :

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

1
2
3
'blablabla copie
    '....etc....
    Application.OnTime Now + TimeValue("00:01:00"), "MaMacro"

**unparia** · 19/01/2018, 10h56

Ah ...
le ''blablabla copie ....etc...." ne sont rien d'autre que tes instruction d'attribution de valeurs à des cellules !
Et n'a plus rien à voir avec le fait que ces instructions interviennent lors du déclenchement de ta macro Ontime !
Elles seraient exactement les mêmes que celles que tu exécuterais aux mêmes fins à n'importe quel moment de ton choix.
Et si telle est finalement ta véritable difficulté, elle est à isoler et traiter dans une discussion relative à cette SEULE difficulté (sans même parler de Ontime).
Il faut apprendre à sérier, isoler, traiter séparément les choses.

**Phil Free** · 19/01/2018, 11h15

Alors pour info ce qu'en dit le msdn à propos de la methode OnTime

Il n'y a rien dans cette page d'aide qui dit que Ontime produira une boucle. OnTime permet de définir une heure de départ et/ou de fin de lancement de macro ou encore de retarder le lancement de la macro !

~~Bref, pour moi, tel que tu utilises cette méthode ta macro n'est lancée qu'une fois !~~

~~Donc ma question est : elle est ou la boucle ?~~

arf ! désolé !

je n'ai pas vu que la macro se relançai elle même !

Mais du coup tu l’arrête comment mise à part via l'heure de fin ?

**unparia** · 19/01/2018, 11h20

Bonjour Phil Free
"Boucle" n'est pas le terme approprié.
La macro appelée par ontime se rappelle elle-même par l'instruction qui figure en son sein de ... s'exécuter elle-même à nouveau à l'heure précisée dans cette instruction.

**Phil Free** · 19/01/2018, 11h27

Bonjour unparia,

oui je l'ai vu mais trop tard mais ai mis mon message à jour avec une autre question !

Cela me parait plutôt malsains comme méthode. cela doit produire des boucles dans des boucles dans des boucles... comme un miroir an face d'un autre miroir.

enfin... je sais pas !

**patricktoulon** · 19/01/2018, 11h33

phil free

autant pour moi tu a peut etre raison c'est pas "or" c'est bien "and"

c'est moi qui l'induit en erreur une féssée pour moi

bon mle principe est tellement simple que je n'arrive pas a deviner ou est son soucis

peut etre la lance t il pas la premiere fois

sur la condition de demarrage a heure_deb dans le workbook open par exemple

je ne l'ai pas preciser pensant que c'etait evident pour tout le monde

**Phil Free** · 19/01/2018, 12h10

patricktoulon,

non non mais il n'y a pas de mal

d'autant que mon explication à propos de And/Or ne t’était pas destinée.

c’était juste une occasion pour rappeler à Fabien l'importance de comprendre la différence.

bon mle principe est tellement simple que je n'arrive pas a deviner ou est son soucis

pareil

d'autant que une fois le Or remplacé par le And cela fonctionne chez moi !

Fabien,

Par contre lancer cette boucle sans prévoir une condition d’arrêt autre que l'heure de fin... pffff... je ferais bien un truc du genre !

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
Sub MaMacro()
 
If Time < heure_déb Then Application.OnTime Date + heure_déb, "MaMacro" ' si tu demarre ton fichier avant 9 heures du mat on programme le premier toutrs de garde a 09:00:00 du jours
 
If Time >= heure_déb Or Time <= heure_fin Then ' l'heure actuelle est plus grande que l'eure_deb et plus petite que l'heure fin on programe la prochaine minute
 
    With Sheets("Feuil1")
        .Range("E" & .Cells(Rows.Count, "E").End(xlUp).Row + 1) = .Cells(8, "F").Value
        'une cellule dédiée à l'arret ici "A1" pour l'exemple
        If lCase$(.Range("A1").Value) = "fin" Then 'on ecrit fin dans la cellule... le code s'arrete
            .Range("A1").Value = "" 'ici on efface le contenu avant de sortir pour ne pas oublier de l'enlever avant de relancer
            Exit Sub
        End If
    End With
 
     DoEvents 'nous assures d'avoir la main pour pouvoir ecrire "fin" dans la cellule dédiée à l'arret
    Application.OnTime Now + TimeValue("00:00:10"), "MaMacro"
 
End If
End Sub

ah et je me pose une autre question : comment est alimentée la cellule F8 ?

**fabien32200** · 23/01/2018, 16h25

Merci Phil & Patrick de votre aide
Désolé de vous répondre aussi tardivement...

Voici le code complet donc :

Dans Workbook :

Code :

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

1
2
3
Private Sub Workbook_Open()
  Call MaMacro
End Sub

Dans mon Module 1:

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
Option Explicit
Option Private Module
Private Const heure_déb As Date = #9:00:00 AM#
Private Const heure_fin As Date = #6:45:00 PM#
 
Sub MaMacro()
 
If Time < heure_déb Then Application.OnTime Date + heure_déb, "MaMacro" ' si tu demarre ton fichier avant 9 heures du mat on programme le premier toutrs de garde a 09:00:00 du jours
 
If Time >= heure_déb AND Time <= heure_fin Then ' l'heure actuelle est plus grande que l'eure_deb et plus petite que l'heure fin on programe la prochaine minute
 
    With Sheets("Feuil1")
        .Range("E" & .Cells(Rows.Count, "E").End(xlUp).Row + 1) = .Cells(8, "F").Value
        'une cellule dédiée à l'arret ici "A1" pour l'exemple
        If LCase$(.Range("A1").Value) = "fin" Then 'on ecrit fin dans la cellule... le code s'arrete
            .Range("A1").Value = "" 'ici on efface le contenu avant de sortir pour ne pas oublier de l'enlever avant de relancer
            Exit Sub
        End If
    End With
 
     DoEvents 'nous assures d'avoir la main pour pouvoir ecrire "fin" dans la cellule dédiée à l'arret
    Application.OnTime Now + TimeValue("00:01:00"), "MaMacro"
 
End If
End Sub

Par contre rien ne se colle dans la colonne E (je suis bête certainement mais je ne comprends pas ...)

ah et je me pose une autre question : comment est alimentée la cellule F8 ?

Je récupère les cours de bourse de mes titres via une application reliée à ma feuille excel (c'est un lien)

**Phil Free** · 24/01/2018, 09h18

Bonjour,

Envoyé par fabien32200

Je récupère les cours de bourse de mes titres via une application reliée à ma feuille excel (c'est un lien)

ah oui du coup c'est peut être le .value manquant .
Une cellule étant un objet portant plusieurs propriétés il ne faut jamais oublier de préciser la propriété que l'on veut lire ou écrire.

donc :

Code :

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

1
2
.Range("E" & .Cells(Rows.Count, "E").End(xlUp).Row + 1).Value = .Cells(8, "F").Value

**fabien32200** · 24/01/2018, 11h28

J'ai rajouté le .value, le code à l'air de marcher (car pas de déblocage à faire) mais rien ne se met dans la colonne E...

**patricktoulon** · 24/01/2018, 12h50

re
Ma fois chez moi ca fonctionne
j'ai meme fait un double "ontime pour modifier F8n'ayant pas ton app qui modifie ce F8 (un coup la modif un coup le transfert en "E" )
pil poil

apres cela dit un timer avec l'api window est beaucoup plus facile a gérer puisqu'il se relance tou seul et pour l'arréter a tous moment un killTimer avec la meme api suffit

**fabien32200** · 24/01/2018, 13h25

re Patrick,

J'ai fait des captures d'écran, je pense que j'ai bien tout comme toi pourtant
PS : je suis mauvais, mais j'ai bien un chiffre en F8 dans ma feuille (lol)

Nom : ThisWorkbook.png
Affichages : 1596
Taille : 48,4 Ko

Nom : ThisWorkbook.png
Affichages : 1596
Taille : 48,4 Ko

Nom : Module 1.png
Affichages : 1581
Taille : 99,4 Ko

Macro automatique toutes les minutes avec une donnée (qui varie) à C/L dans une nouvelle cellule

Macros et VBA Excel

Discussions similaires

Partager

Partager