PDA

View Full Version : Show formula and absolut reference



JJBD
04-27-2013, 05:45 AM
Dear Guys

I really hope some of you can help me with the following problem.

I have programmed a "show formula" bottom, for both values and cell references to excel.
My problem is that if i make an absolut reference of say A1 (assume value 3), such that: $A$1,
and for example multiply it with another cell (Assume value 2), and i push the " show formula" value bottom it shows :

= $A$1*2

But instead of this, I want it to show:

= 3*2

I have worked with this in weeks now but without any result.
How can I do this? a part of my code should be below.


Function PatternExtract(strF As String, strPattern As String, _
Optional boolIgnoreCase As Boolean = True, Optional lngInstance As Long = 1) As Variant
Dim RegExp As Object, RegExpMatch As Object
On Error Resume Next
Set RegExp = CreateObject("vbscript.regexp")
With RegExp
.Global = True
.IgnoreCase = boolIgnoreCase
.Pattern = strPattern
End With
Set RegExpMatch = RegExp.Execute(strF)
If lngInstance > RegExpMatch.Count Then
PatternExtract = ""
Else
If lngInstance = -1 Then
For lngInstance = 1 To RegExpMatch.Count Step 1
PatternExtract = PatternExtract & " " & RegExpMatch(lngInstance - 1)
Next lngInstance
PatternExtract = Replace(PatternExtract, " ", "", 1, 1)
Else
PatternExtract = RegExpMatch(lngInstance - 1)
End If
End If
Set RegExpMatch = Nothing

End Function

Function Udregn(Cell As Range) As String
Dim vRefs As Variant, lngRef As Long
Udregn = "" & Cell.Formula
vRefs = Split(PatternExtract(Udregn, "[A-Z]{1,2}[0-9]+", , -1))
For lngRef = LBound(vRefs) To UBound(vRefs) Step 1
Udregn = Replace(Udregn, vRefs(lngRef), Round(Evaluate(vRefs(lngRef)), 3), 1, -1, vbTextCompare)
Next lngRef
End Function

SamT
04-27-2013, 01:51 PM
Assuming you're using Option Base 0, Change
Round(Evaluate(vRefs(lngRef)), 3
To Round(Evaluate(vRefs(lngRef)), lngRef*3

JJBD
04-28-2013, 05:03 AM
Thank you for your answer.

I don't think I understand "base 0" ? :)

Unfortunatly it still shows the same result ($A$1*2) ..

Paul_Hossler
04-28-2013, 05:46 AM
JJBD -- what do you do if there's a worksheet function used in the formula, such as in D4 ...

=SUM($C$1:$C$4)*$A$1

?

Paul

JJBD
04-28-2013, 05:59 AM
JJBD -- what do you do if there's a worksheet function used in the formula, such as in D4 ...

=SUM($C$1:$C$4)*$A$1

?

Paul

I don't think I have considered that yet, to be honest.
But are there no now way to show the values instead of showing the AbsRef?

szejtan99
04-29-2013, 02:41 PM
I don't think I have considered that yet, to be honest.
But are there no now way to show the values instead of showing the AbsRef?

SamT
04-29-2013, 03:05 PM
What happens when you change the formula to =B1*$A$1