Solved: UDF For Convering a Formula To Resource Addresses

[Erdin? E. Ka]

Hi everyone,

In Turkish board we try to find a way to solve the problem but we couldn't yet.
( http://www.kod.gen.tr/forum/viewtopic.php?t=4297 )

The problem is;

For example:

A1 = 2
A2 = 6
A3 = 4
A4 = 14
A5 = 25

We have a formula in C1 as
=((A5+A4)*A2-A3)/A1
Result is = 115
It's ok until here.

We need a U.D.F. for see this formula as a string text like below:

=((25+14)*6-4)/2

This UDF should be for all kind of addresses and all arguments
{^, +, %, (, ), -, *} etc...

I tried some ways to do it but i wasn't successfull. :think:

Any ideas?

Thanks in advance.

[bigdoggit]

I think I may have your solution. What I think you wanted was a U.D.F that will take any formula, plug in the values from each cell reference, and show that string. For example,

=(A5 + A4) - (A2+A1)

and the U.D.F would make
=(25 + 14) - (6 + 2)

Is this what you want? :think: I haven't done any error checking with the U.D.F that I have. It assumes that you have correctly written in your cell references, no mistakes such as forgettting a plus sign: A5A4 instead of A5+A4

I could add error checking, I just want to make sure my U.D.F will help you. Just paste the following code into a normal Module.

The U.D.F is called FormulaToString and requires you put in a valid cell reference without quotations. This will mean if you ever change the formula in the referenced cell, the U.D.F will automatically recalculate.

Here is the code. I may be busy during your time - or sleeping, I believe you are seven hours ahead of us. So it is 12:54 am here, and 7:54 am by you. Anyway - it's late, I may be off. If you have questions, post. I'll check in later.

Here the Code. Copy and Paste in a module.



Option Explicit

Function FormulaToString(rRng As Range)

Dim sTxt As String 'This variable will store the formula in the referenced cell
Dim lCycleVariable As Long 'This will be a generic cycle variable
Dim sClRef As String 'This will store each cell reference for replacement
Dim sRng As String

sRng = rRng.Address
sTxt = UCase(Range(sRng).Formula)

For lCycleVariable = 1 To Len(sTxt)
If Mid(sTxt, lCycleVariable, 1) >= "A" And Mid(sTxt, lCycleVariable, 1) <= "Z" Then
sClRef = IsCell(sTxt, lCycleVariable)
sTxt = Replace(sTxt, sClRef, Range(sClRef).Value)
End If
Next lCycleVariable

FormulaToString = sTxt
End Function

Function IsCell(sTxt As String, lStrt As Long)

Dim sRefChk As String
Dim lCycleVariable As Long
Dim lCycleVariable2 As Long
Dim lNmbrCtr As Long

sRefChk = Mid(sTxt, lStrt, 1)

For lCycleVariable = lStrt + 1 To Len(sTxt)
If Mid(sTxt, lCycleVariable, 1) >= "A" And Mid(sTxt, lCycleVariable, 1) <= "Z" Then
sRefChk = sRefChk & Mid(sTxt, lCycleVariable, 1)
GoTo MovingOn
ElseIf Mid(sTxt, lCycleVariable, 1) >= 1 And Mid(sTxt, lCycleVariable, 1) <= 9 Then
lNmbrCtr = 1
sRefChk = sRefChk & Mid(sTxt, lCycleVariable, 1)
If lCycleVariable = Len(sTxt) Then
GoTo LastLine
End If
For lCycleVariable2 = lCycleVariable + 1 To Len(sTxt)
If Mid(sTxt, lCycleVariable2, 1) >= 1 And Mid(sTxt, lCycleVariable2, 1) <= 9 Then
lNmbrCtr = lNmbrCtr + 1
sRefChk = sRefChk & Mid(sTxt, lCycleVariable2, 1)
Else
IsCell = sRefChk
GoTo LastLine
End If
Next lCycleVariable2
End If

MovingOn:
Next lCycleVariable
LastLine:
IsCell = sRefChk
End Function


It seemed to copy and paste fine for me. Just let me know if it is close but not quite - or if it needs to do more. If I am on the right track, I should add some error checking.

Big E :friends:

[Bob Phillips]

I am intrigued Erdin?. What possible use is this UDF?

[Erdin? E. Ka]

I think I may have your solution. What I think you wanted was a U.D.F that will take any formula, plug in the values from each cell reference, and show that string. For example,

=(A5 + A4) - (A2+A1)

and the U.D.F would make
=(25 + 14) - (6 + 2)

Is this what you want? :think: I haven't done any error checking with the U.D.F that I have. It assumes that you have correctly written in your cell references, no mistakes such as forgettting a plus sign: A5A4 instead of A5+A4

I could add error checking, I just want to make sure my U.D.F will help you. Just paste the following code into a normal Module.

The U.D.F is called FormulaToString and requires you put in a valid cell reference without quotations. This will mean if you ever change the formula in the referenced cell, the U.D.F will automatically recalculate.

Here is the code. I may be busy during your time - or sleeping, I believe you are seven hours ahead of us. So it is 12:54 am here, and 7:54 am by you. Anyway - it's late, I may be off. If you have questions, post. I'll check in later.

Here the Code. Copy and Paste in a module.



Option Explicit

Function FormulaToString(rRng As Range)

Dim sTxt As String 'This variable will store the formula in the referenced cell
Dim lCycleVariable As Long 'This will be a generic cycle variable
Dim sClRef As String 'This will store each cell reference for replacement
Dim sRng As String

sRng = rRng.Address
sTxt = UCase(Range(sRng).Formula)

For lCycleVariable = 1 To Len(sTxt)
If Mid(sTxt, lCycleVariable, 1) >= "A" And Mid(sTxt, lCycleVariable, 1) <= "Z" Then
sClRef = IsCell(sTxt, lCycleVariable)
sTxt = Replace(sTxt, sClRef, Range(sClRef).Value)
End If
Next lCycleVariable

FormulaToString = sTxt
End Function

Function IsCell(sTxt As String, lStrt As Long)

Dim sRefChk As String
Dim lCycleVariable As Long
Dim lCycleVariable2 As Long
Dim lNmbrCtr As Long

sRefChk = Mid(sTxt, lStrt, 1)

For lCycleVariable = lStrt + 1 To Len(sTxt)
If Mid(sTxt, lCycleVariable, 1) >= "A" And Mid(sTxt, lCycleVariable, 1) <= "Z" Then
sRefChk = sRefChk & Mid(sTxt, lCycleVariable, 1)
GoTo MovingOn
ElseIf Mid(sTxt, lCycleVariable, 1) >= 1 And Mid(sTxt, lCycleVariable, 1) <= 9 Then
lNmbrCtr = 1
sRefChk = sRefChk & Mid(sTxt, lCycleVariable, 1)
If lCycleVariable = Len(sTxt) Then
GoTo LastLine
End If
For lCycleVariable2 = lCycleVariable + 1 To Len(sTxt)
If Mid(sTxt, lCycleVariable2, 1) >= 1 And Mid(sTxt, lCycleVariable2, 1) <= 9 Then
lNmbrCtr = lNmbrCtr + 1
sRefChk = sRefChk & Mid(sTxt, lCycleVariable2, 1)
Else
IsCell = sRefChk
GoTo LastLine
End If
Next lCycleVariable2
End If

MovingOn:
Next lCycleVariable
LastLine:
IsCell = sRefChk
End Function


It seemed to copy and paste fine for me. Just let me know if it is close but not quite - or if it needs to do more. If I am on the right track, I should add some error checking.

Big E :friends:


Hi bigdoggit, ( Or good morning !! )

You are amazing my friend.:friends:

Your solution just brilliant! :whistle:

Also i want thank you for your kindly approach and politeness. I saw your post a few minutes ago. And our local time is 15:45.

I tried some different combinations and it's perfect. :thumb

And i will share your solution in Turkish board, but i will make some changing on name of variables for easy understanding...

Thanks a lot.:hi:

[Erdin? E. Ka]

I am intrigued Erdin?. What possible use is this UDF?

Hi Bob, :hi:

Actually i don't need much this UDF. But i was intrigued about how this could be done? Then i interested about this UDF for an exercise myself but while i working on for this UDF i understood was diffucult for me. :)

Then asked here...

Take good care of yourself... :hi:

[bigdoggit]

Thank you very much for your appreciation. :cloud9: It is good to hear my solution helped, and it was my pleasure. Have a great rest of your day. Over here a lot of people work Mon-Fri, though not me. I happen to be on vacation is all. But anyway, if you are one of the lucky ones who have every weekend off, have a great rest of it, and take care. :hi:

Big E

[Bob Phillips]

Hi Bob, :hi:

Actually i don't need much this UDF. But i was intrigued about how this could be done? Then i interested about this UDF for an exercise myself but while i working on for this UDF i understood was diffucult for me. :)

Then asked here...

Take good care of yourself... :hi: Can you ask your OP what it is used for?

[Erdin? E. Ka]

Thank you very much for your appreciation. :cloud9: It is good to hear my solution helped, and it was my pleasure. Have a great rest of your day. Over here a lot of people work Mon-Fri, though not me. I happen to be on vacation is all. But anyway, if you are one of the lucky ones who have every weekend off, have a great rest of it, and take care. :hi:

Big E

Hi Big E,

Thank you for your kindly wishes and help. As you said i am one of the lucky people i think. :)

Take care too. :hi:

[Erdin? E. Ka]

Can you ask your OP what it is used for?sn't

Bob, contar is faster then me, 'cause he told me before i ask to him the reason for this UDF. :)

He said me that:

He is a civil engineer for a watering project building firm and he calculating their metric accounts for watering projects with Excel application.

Also he said that while he need to tell some accounts to their employees-workmans in working area, the table (printed on A4 paper) wasn't enaugh to tell them. Because their employees wanted to see all digits and full formuling about the results...

So, this is a little strange need for the UDF.

Take care...:hi: