Comment une variable de VBA peut aller dans une formule Excel? Résolu

Question

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!

Patrice33740 · Answer

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

Patrice33740 · Answer

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

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

2 réponses

Newsletters