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
im using EvaluateString on sheet "Data_Column". i use =EvaluateString(A1) to find the value of the defined name — Chewjunnie
You've put the Function() inside an existing Sub() definition. No can do — Craig
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

Dominique Dominique · Accepted Answer · 2021-05-10T07:43:26

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

converting formula into string vba

1 Answers