vba: i want function convert from xy to cells-CodePudding

How do I return a result from a function?

For example:vba: i want function

Function xy2cell(i, f)
   xy2cell = "=" & "?????????????????????????????"
End Function
Sub aaa_main()
    ActiveSheet.Cells.Clear
    f = "5^4*x-2^4*y-1"
    For i = 1 To 2
        Cells(i, 3) = xy2cell(i, f)
    Next
End Sub

'I want
'Cells(1, 3) = "=5^4*" & Cells(1, 1).Address & "-2^4*" & Cells(1, 2).Address & "-1"
'Cells(2, 3) = "=5^4*" & Cells(2, 1).Address & "-2^4*" & Cells(2, 2).Address & "-1"

CodePudding user response：

A simpler approach is to use the formual2R1C1 property of a range. This allows you to specify a formula using notation that refers to cells as offsets from the destination cell. This way, a single expression can be used to create different formulas in each target cell of the range.

Sub aaa_main_2()
    Dim f As String
    f = "=5^4*x-2^4*y-1"
    f = Replace(f, "x", "RC[-2]")
    f = Replace(f, "y", "RC[-1]")
   ActiveSheet.Cells.Clear
   Range("C1:C2").Formula2R1C1 = f
End Sub

or, more directly

Sub aaa_main_3()
   ActiveSheet.Cells.Clear
   Range("C1:C2").Formula2R1C1 = "=5^4*RC[-2]-2^4*RC[-1]-1"
End Sub

CodePudding user response：

It seems unusual to clear all the cells from the active sheet, as this would remove any inputs on which the function would operate. Nonetheless, here is your code converted to do as you ask. I've added Dim statements to declare the variables your code uses.

Function xy2cell(i As Long, f As String)
   Dim formula As String
   formula = Replace(f, "x", Cells(i, 1).Address(False, False))
   formula = Replace(formula, "y", Cells(i, 2).Address(False, False))
   xy2cell = "=" & formula
End Function

Sub aaa_main()
    Dim f As String
    Dim i As Long
    ActiveSheet.Cells.Clear
    f = "5^4*x-2^4*y-1"
    For i = 1 To 2
        Cells(i, 3).Formula = xy2cell(i, f)
    Next
End Sub

This code uses the "replace" function to find "x" in your formula string (f) and replace it with the appropriate cell reference. The result is stored in a variable named "formula" which is then used as the input to replace y with the appropriate cell reference.

However, there is a much simpler approach using the formula2R1C1 property. I'll post a separate solution on that technique.