Convert Formulas to Code

jstiene

Board Regular
Joined
Aug 5, 2005
Messages
222
I made a routine for converting formulas to code, mainly to replace quotes in the formula.
The problem is when it is too enormous there seems to be a need for carriage return as it looks like it's maxing the immediate window.
I started a carriage return solution (commented out) but it's still not right. It basically spits out code into the immediate window that is a representation of formula of the cursors cell.
So if you put a question mark in front of the immediate window and hit return it should rebuild the formula
Any ideas:

Sub ConvertFormulaToCode()
Dim i As Integer, L1 As Integer
Dim Val1 As String, Chr1 As String, St2 As String, St1 As String, St3 As String
Val1 = Selection.value
St2 = Chr(34) '""
L1 = Len(Val1)
For i = 1 To L1
Chr1 = Mid(Val1, i, 1)
If Chr1 <> Chr(34) Then
St2 = St2 & Chr1
'St3 = St3 & Chr1
Else
St1 = Chr(34) & " & Chr(34) & " & Chr(34)
St2 = St2 & St1 'St2 = St2 & "" & Chr(34) & ""
' St3 = St3 & St1
' If Len(St3) > 140 Then 'add line feed
' St2 = St2 & " &_" & vbCr '& Chr(34)
' St3 = ""
' End If
End If
Next i
St2 = St2 & Chr(34)
Me.Hide
Debug.Print St2
' MsgBox St2
End Sub
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

jstiene

Board Regular
Joined
Aug 5, 2005
Messages
222
A basic formula example to build xml tags:
=CONCATENATE("<record product-id=""",T30,"""> <allocation>1</allocation><allocation-timestamp>2020-07-01T00:00:00.000Z</allocation-timestamp><perpetual>false</perpetual><preorder-backorder-handling>none</preorder-backorder-handling><ats>1</ats></record>")

A really large formula sample:

=CONCATENATE("<product product-id=""",T30,"""><upc>",F30,"</upc><min-order-quantity>1</min-order-quantity><step-quantity>1</step-quantity><display-name xml:lang=""x-default"">",TRIM(S30),"</display-name><online-flag>true</online-flag><available-flag>true</available-flag><searchable-flag>true</searchable-flag><custom-attributes><custom-attribute attribute-id=""color"">",Q30,"</custom-attribute><custom-attribute attribute-id=""SIZE1"">",J30,"</custom-attribute><custom-attribute attribute-id=""SIZE2"">",K30,"</custom-attribute><custom-attribute attribute-id=""SIZE1_FACET"">",M30,"</custom-attribute><custom-attribute attribute-id=""SIZE2_FACET"">",N30,"</custom-attribute></custom-attributes></product>")
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top