Comment une variable de VBA peut aller dans une formule Excel?

Résolu
Paascal Messages postés 27 Statut Membre -  
Patrice33740 Messages postés 8930 Statut Membre -
Bonjour,

Comment une variable de VBA peut aller dans une formule Excel?
voici les codes pour ces variables:

rmx1 = YCOS * ZCOS
Range("k2").Select
ActiveCell = rmx1
rmx2 = ZCOS * -YSIN * -XSIN + ZSIN * XCOS
Range("L2").Select
ActiveCell = rmx2
rmy1 = -ZSIN * YCOS
Range("k3").Select
ActiveCell = rmy1
rmy2 = -ZSIN * -YSIN * -XSIN + ZCOS * XCOS
Range("L3").Select
ActiveCell = rmy2
rmz1 = YSIN
Range("k4").Select
ActiveCell = rmz1
rmz2 = YCOS * XCOS
Range("l4").Select
ActiveCell = rmz2

et voici les formules:

Range("G3").Select
ActiveCell.FormulaR1C1 = "=(RC[-3]*_RMX1)+(RC[-2]*_RMY1)+(RC[-1]*_RMZ1)"
Range("H3").Select
ActiveCell.FormulaR1C1 = "=(RC[-4]*_RMX2)+(RC[-3]*_RMY2)+(RC[-2]*_RMZ2)"

Merci de votre aide!
A voir également:

2 réponses

Réponse 1 / 2
Patrice33740 Messages postés 8930 Statut Membre 1 781
 
Re,

Tous les "select" sont inutiles !

J'écrirais ceci : 
Option Explicit
Sub paascal()
' Déclaration des variables
Dim anglex As Byte, angley As Byte, anglez As Byte
Dim xsin As Double, xcos As Double
Dim ysin As Double, ycos As Double
Dim zsin As Double, zcos As Double
Dim rmx1 As Double, rmx2 As Double
Dim rmy1 As Double, rmy2 As Double
Dim rmz1 As Double, rmz2 As Double
Dim a As Long, b As Long, c As Long, i As Long
  
  ' Définition des angles
  anglex = 30
  angley = 30
  anglez = 0
  ' Calculs
  xsin = Sin(anglex)
  xcos = Cos(anglex)
  ysin = Sin(angley)
  ycos = Cos(angley)
  zsin = Sin(anglez)
  zcos = Cos(anglez)
  rmx1 = ycos * zcos
  rmx2 = zcos * -ysin * -xsin + zsin * xcos
  rmy1 = -zsin * ycos
  rmy2 = -zsin * -ysin * -xsin + zcos * xcos
  rmz1 = ysin
  rmz2 = ycos * xcos
  ' Dans la feuille active
  With ActiveSheet
    ' Effacer les anciennes valeurs
    .Columns("D:L").Clear
    ' Constantes dans cellules
    .Range("A1").Value = "ordonnés original"
    .Range("D1").Value = "ordonnés modifiés"
    .Range("G1").Value = "valeurs dans le graphique"
    .Range("A2").Value = "X"
    .Range("B2").Value = "Y"
    .Range("C2").Value = "Z"
    .Range("D2").Value = "X"
    .Range("E2").Value = "Y"
    .Range("F2").Value = "Z"
    .Range("G2").Value = "X"
    .Range("H2").Value = "Y"
    ' Format des cellules
    With .Range("A2:H2")
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlCenter
    End With
    With Range("A1:C1")
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlBottom
      .Merge
    End With
    With Range("D1:F1")
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlBottom
      .Merge
    End With
    With Range("G1:H1")
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlBottom
      .Merge
    End With
    ' Valeurs dans cellules
    .Range("j2").Value = anglex
    .Range("j3").Value = angley
    .Range("j4").Value = anglez
    .Range("k2").Value = rmx1
    .Range("L2").Value = rmx2
    .Range("k3").Value = rmy1
    .Range("L3").Value = rmy2
    .Range("k4").Value = rmz1
    .Range("l4").Value = rmz2
    ' Formules dans cellules
    .Range("D3").Formula = "=A3"
    .Range("E3").Formula = "=B3"
    .Range("F3").Formula = "=C3"
    .Range("G3").FormulaLocal = "=(D3*" & rmx1 & ")+(E3*" & rmy1 _
                                & ")+(F3*" & rmz1 & ")"
    .Range("H3").FormulaLocal = "=(D3*" & rmx2 & ")+(E3*" & rmy2 _
                                & ")+(F3*" & rmz2 & ")"
    ' Recopie des formules
    a = .Cells(Rows.Count, "A").End(xlUp).Row
    b = .Cells(Rows.Count, "B").End(xlUp).Row
    c = .Cells(Rows.Count, "C").End(xlUp).Row
    If a >= b And a >= c Then i = a
    If b >= a And b >= c Then i = b
    If c >= b And c >= a Then i = c
    If i > 3 Then .Range("D3:H3").Copy .Range("D3:H" & i)
  End With
  ActiveWindow.Zoom = 75

End Sub


Cordialement
Patrice
1
Paascal Messages postés 27 Statut Membre
 
Merci beaucoup,
tout fonctionne super bien!
0
Patrice33740 Messages postés 8930 Statut Membre 1 781 > Paascal Messages postés 27 Statut Membre
 
De rien, si ça te convient, passe le post en résolu.

Cdlt
Patrice
0
Réponse 2 / 2
Patrice33740 Messages postés 8930 Statut Membre 1 781
 
Hein ????

1. Tu devrais commencer ton module par Option Explicit
2. Avec la totalité du code c'est plus facile à comprendre.

Peut-être: 
ActiveCell.FormulaR1C1 = "=(RC[-3]*" & RMX1 & ")+(RC[-2]*" & _
                          RMY1 & ")+(RC[-1]*" & RMZ1 & ")"


Cordialement
Patrice
0
Paascal Messages postés 27 Statut Membre
 
Voici le code complet

Sub paascal()
'sélection des angles
Range("j2").Select
ActiveCell = 30
anglex = ActiveCell
Range("j3").Select
ActiveCell = 30
angley = ActiveCell
Range("j4").Select
ActiveCell = 0
anglez = ActiveCell
'calcul des angles
XSIN = Sin(anglex)
XCOS = Cos(anglex)
YSIN = Sin(angley)
YCOS = Cos(angley)
ZSIN = Sin(anglez)
ZCOS = Cos(anglez)
'formules dans cellules
Range("D3").Select
ActiveCell.FormulaR1C1 = "=RC[-3]"
Range("E3").Select
ActiveCell.FormulaR1C1 = "=RC[-3]"
Range("F3").Select
ActiveCell.FormulaR1C1 = "=RC[-3]"
RMX1 = YCOS * ZCOS
Range("k2").Select
ActiveCell = RMX1
rmx2 = ZCOS * -YSIN * -XSIN + ZSIN * XCOS
Range("L2").Select
ActiveCell = rmx2
RMY1 = -ZSIN * YCOS
Range("k3").Select
ActiveCell = RMY1
rmy2 = -ZSIN * -YSIN * -XSIN + ZCOS * XCOS
Range("L3").Select
ActiveCell = rmy2
RMZ1 = YSIN
Range("k4").Select
ActiveCell = RMZ1
rmz2 = YCOS * XCOS
Range("l4").Select
ActiveCell = rmz2
Range("G3").Select
ActiveCell.FormulaR1C1 = "=(RC[-3]*_RMX1)+(RC[-2]*_RMY1)+(RC[-1]*_RMZ1)"
Range("H3").Select
ActiveCell.FormulaR1C1 = "=(RC[-4]*_RMX2)+(RC[-3]*_RMY2)+(RC[-2]*_RMZ2)"
'copier les formules
A = Cells(Rows.Count, "A").End(xlUp).Row
B = Cells(Rows.Count, "B").End(xlUp).Row
C = Cells(Rows.Count, "C").End(xlUp).Row
If A >= B And A >= C Then i = A
If B >= A And B >= C Then i = B
If C >= B And C >= A Then i = C
Range("D3:H3").Select
Selection.Copy
Range("D" & 3 & ":H" & i).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Ajout des axes
Range("A2").Select
ActiveCell.FormulaR1C1 = "X"
Range("B2").Select
ActiveCell.FormulaR1C1 = "Y"
Range("C2").Select
ActiveCell.FormulaR1C1 = "Z"
Range("D2").Select
ActiveCell.FormulaR1C1 = "X"
Range("E2").Select
ActiveCell.FormulaR1C1 = "Y"
Range("F2").Select
ActiveCell.FormulaR1C1 = "Z"
Range("G2").Select
ActiveCell.FormulaR1C1 = "X"
Range("H2").Select
ActiveCell.FormulaR1C1 = "Y"
Range("A2:H2").Select
Range("H2").Activate
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1:C1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("D1:F1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("G1:H1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A1:C1").Select
ActiveCell.FormulaR1C1 = "ordonnés original"
Range("D1:F1").Select
ActiveCell.FormulaR1C1 = "ordonnés modifiés"
Range("G1:H1").Select
ActiveCell.FormulaR1C1 = "valeurs dans le graphique"
'recentrer l'écran
Range("J5").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
ActiveWindow.Zoom = 75

End Sub

Sinon, pourriez-vous m'écrire comment définir des noms avec les macros?
0