converting formula into string vba

Question

My excel file has 3 different type of VBA functions. In a sheet, I want to show the value from the defined name function. Initially I used this code to convert formula into string:

Function EvaluateString(strTextString As String)
    Application.Volatile
    EvaluateString = Evaluate(strTextString)
End Function

This function was placed in a individual module. The problem with this is that it runs in every module causing the running time for my other modules to be extremely slow.

I tried placing this function in a module itself, but it does not work. It gave me an error of "Only comments may appear after End Sub, End Function, or End Property".

This is my code when I received that prompt

Sub PasteAsText()

Dim lastRow As Long
lastRow = Worksheets("Data_Column").Cells(Rows.Count, 1).End(xlUp).Row

Function EvaluateString(strTextString As String)
    Application.Volatile
    EvaluateString = Evaluate(strTextString)
End Function

Worksheets("Data_Column").Range("D1:D" & lastRow).Clear

Worksheets("Data_Column").Range("C1:C" & lastRow).Copy
Worksheets("Data_Column").Range("D1:D" & lastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Worksheets("Data_Column").Range("D1:D" & lastRow).NumberFormat = "@"
Worksheets("Data_Column").Select

End Sub

Where exactly are you using EvaluateString? I would only expect it to slow things down if it was being used in thousands of cells. Also, why have you tried placing it within the code of a sub? That doesn't make sense, and as you found out won't allow the code to compile. — norie
– norie, Commented May 10, 2021 at 3:22
im using EvaluateString on sheet "Data_Column". i use =EvaluateString(A1) to find the value of the defined name — Chewjunnie
– Chewjunnie, Commented May 10, 2021 at 3:24
You've put the Function() inside an existing Sub() definition. No can do — Craig
– Craig, Commented May 10, 2021 at 3:25
I tried placing the function in its own module but it cause my other codes to run very slowly. Is there anyway i can improve on it? — Chewjunnie
– Chewjunnie, Commented May 10, 2021 at 3:27

Dominique · Accepted Answer · 2021-05-10 07:43:26Z

0

In order to see a formula as a text, you can use the FormulaText() function, as explained in this URL.

So instead of your function PasteText() you can simply do something like:

Destination = FormulaText(Source)                                           // simple Excel OR
Destination.Value = Application.WorkSheetFunction.FormulaText(Source.Value) // VBA

answered May 10, 2021 at 7:43

Dominique

17.6k20 gold badges81 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

converting formula into string vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related