0

Here I want the function f1() to create en excel formula and pass this formula to f2() without returning back to f1() and the f2() replaces function with the formula, while doing so I am getting compile error: Expected in line Sub f2( Optional endAll As Boolean = False, jR1 as Long, jC1 as Long, jGetFormula as String)

Sub f2( Optional endAll As Boolean = False, jR1 as Long, jC1 as Long, jGetFormula as String)
    Cells(jR1, jC1) = Evaluate(jGetFormula)
    If endAll Then Exit Function
End Sub

Function f1()
    jGetFormula = "=A1*10^3*A2/148"
    jR1 = Selection.Row
    jC1 = Selection.Column
    Call f2(True, jR1, jC1, jGetFormula)
End Function
afroz tulip
  • 3
  • 3

1 Answers1

1

move optional parameter to the end of declaration.

Sub f2(jR1 As Long, jC1 As Long, jGetFormula As String, Optional endAll As Boolean = False)

Every parameter following an optional parameter in the procedure definition must also be optional.

Sfagnum
  • 163
  • 8
  • I changed below lines now error is gone, but now in cell I am getting #REF error where i am writing =f1() Sub f2(jR1 As Long, jC1 As Long, jGetFormula As String, Optional endAll Call f2(jR1, jC1, jGetFormula, True) – afroz tulip Nov 01 '19 at 07:06
  • try `Cells(jR1, jC1) = Evaluate(jGetFormula)` change to `Cells(jR1, jC1) = jGetFormula` – Sfagnum Nov 01 '19 at 07:09
  • Sorry I'm forget `Cells(jR1, jC1).Formula = jGetFormula` – Sfagnum Nov 01 '19 at 07:31
  • I am typing in =f1() in cell A3 and my cell A1=2 and A2= 148 my cell A3 should have formula =A1*10^3*A2/148 and value 2000. Still the code is resulting #REF! in cell A3. Any suggestion? – afroz tulip Nov 01 '19 at 09:19
  • first don't use f1 as name of function, second what you want in result? if you fill any cell - make procedure and place button on sheet (for example) As for me function must return value in the same cell, and generally function recalculate after loose focus.... – Sfagnum Nov 01 '19 at 11:30
  • If I use feee1() instead of f1() it is not working. Please also check my related post (https://stackoverflow.com/q/58602734/12289869) where f2t2() of other post is referred as f1() in this post . I am evaluating formula at various locations in my sheet through first function and storing in variable jGetFormula; Having a button may not be feasible everywhere. – afroz tulip Nov 01 '19 at 13:44
  • sorry it's not clear for me... you want show formula from other cells or result of this formula in the cell where you place feeee1()? or may be this right?: _no basically he wants to do a formula to text and replace the addresses with the actual values. – Pᴇʜ Oct 29 at 7:34_ – Sfagnum Nov 01 '19 at 14:16
  • Yes, I wants to do a formula to text and replace the addresses with the actual values. – afroz tulip Nov 02 '19 at 04:52
  • get formula from other cells with `.Formula`, then parse this string with [Regex](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) and get Cells addresses to Array, then replace this addresses in formula with `Range(Array(n))`. Try this way, of course you can find way to directly replace Cell address with Range(Cell address) in this string... and I think this discussion more suitable for new topic. – Sfagnum Nov 02 '19 at 14:34