Excel Questions
saeed sedighian
I have some questions about excel (also programming).
Thank you for your attention.
1- Suppose that cell A1 have value 4
cell B1 have formula =6/A1+A1
I want to show in cell C1 this text "6/4+4"
2-Is there a way to get cells that calculated? (In VBA)
Or cells that have formula and made error?
(without looping sheet)
3-I can change error to text using this formula :
=if(iserror(A1),"bad formula",A1)
but I want do this without using formula.
When in VBA, I do it, formula that made
error changes to text and user must completely type it.
Any idea is helpful.
Peo Sjoblom
http://www.mvps.org/dmcritchie/excel/formula.htm#GetFormula
in C1 you can then use
=""""&SUBSTITUTE(getformula(B1),"=","")&""""
assuming you wanted the quotation marks, else use
=SUBSTITUTE(getformula(B1),"=","")
--
Regards,
Peo Sjoblom
ROT13 email
"saeed sedighian" <sas...@yahoo.com> wrote in message news:##o7ZO3vBHA.2816@tkmsftngp05...
Harlan Grove
>You can use a UDF, see
>
>http://www.mvps.org/dmcritchie/excel/formula.htm#GetFormula
>
>in C1 you can then use
>=SUBSTITUTE(getformula(B1),"=","")
...
More below on getformula(), but this formula may not work as desired.
Consider what happens if B1 contains =IF(X99=5,Y99,"no"). Adding 1 as a 4th
arg would help, but =MID(getformula(B1),2,1000) would be better.
>"saeed sedighian" <sas...@yahoo.com> wrote
...
>> 1- Suppose that cell A1 have value 4
>>
>> cell B1 have formula =6/A1+A1
>>
>> I want to show in cell C1 this text "6/4+4"
David McRitchie's getformula udf won't convert the A1 reference to 4. This
would require parsing all cell references from the formula and replacing
them with their current values. The parser would be nontrivial, indeed a
PITA using plain VBA.
If the OP has Internet Explorer 5.0 or higher, s/he has VBScript 5 or higher
installed. VBScript 5 provides a regular expression object that makes the
parsing MUCH EASIER. [FWIW, the current version of VBScript is a free
download from Microsoft's site.] Assuming the OP has VBScript 5 or higher
installed, the following udf will perform the _entire_ requested task
(except that it won't process any cell containing multiple cell references -
left as an exercise).
Function gfrv(r As Range) As String
'watch for line wrapping in the next two statements
'there should be two nonblank, noncomment lines
'starting with Const followed by a blank line
Const crep As String = "(([A-Za-z0-9_]+|'[^']+')!)?\$?[A-Z]{1,2}\$?[0-9]+"
Const mrep As String =
"(([A-Za-z0-9_]+:[A-Za-z0-9_]+|'[^']+:[^']+')\!)|(\$?[A-Z]{1,2}\$?[0-9]+:\$?
[A-Z]{1,2}\$?[0-9]+)"
Dim v As Variant, n As Long
Dim regex As Object, matches As Object, m As Object
Application.Volatile 'modify as needed
gfrv = Mid(r.formula, 2)
Set regex = CreateObject("vbscript.regexp")
regex.Global = True
regex.Pattern = mrep
Set matches = regex.Execute(gfrv)
If matches.Count > 0 Then Exit Function
regex.Pattern = crep
Set matches = regex.Execute(gfrv)
n = matches.Count - 1
For n = n To 0 Step -1
Set m = matches.Item(n)
v = Evaluate(m.Value)
gfrv = Left(gfrv, m.FirstIndex) & CStr(v) & _
Mid(gfrv, m.FirstIndex + m.Length + 1)
Next n
End Function
If anyone else wants to implement this in plain VBA, feel free. It'll be
MUCH LONGER and likely slower.
The latest pcre regular expression library (very similar to VBScript regular
expressions) source code (C/C++) is available on SourceForge's site, so
anyone interested in porting it to VBA could try.
=gfrv(A1) would return 6/4+4 in the OP's example.
David McRitchie
Actually GetFormula will not show intermediate results
it shows the formula. I don't know how to show
intermediate results.
#2 would have to be a formula, to find those with
errors.
Edit, GoTo, Formulas, [x] errors
in VBA that would be 'All formulae with errors
selection.SpecialCells(xlCellTypeFormulas, 16).Select
#3 if you can do it in a worksheet function it would
be much more efficient and current.
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Peo Sjoblom" <gre...@lnubb.pbz> wrote in message news:uUDDmm4vBHA.2636@tkmsftngp07...
David McRitchie
answer posted much earlier in this thread, didn't
think anyone would have the answer for intermediate
results. Nice one Harlan.
David McRitchie
saeed sedighian
I have a new question.
I can make an event using :
CodeMudule.CreateEventProc
but this shows VBA Environment.
Is there a way to hide it.
saeed sedighian
"Harlan Grove" <Hrl...@aol.com> wrote in message
news:Xxbf8.3959$106.2...@bgtnsc05-news.ops.worldnet.att.net...
> If the OP has Internet Explorer 5.0 or higher, s/he has VBScript 5 or
higher
> installed
Very good.
but unfortunately my application must work under Office
97,2000 and XP.
I will do this.
saeed sedighian
I have a new question.
another text. is there a way?
I want to begin my excel data from row 1 but
headers must be on row 1 .
I thinked that changing column header is good. (if possible)
Thank you.
David McRitchie
You can't change the column headers of A,B,C...
But if you want to create numbers running down Column A
you can do that.
A1: 'Item
A2: =ROW()-1 and use fill-handle to copy down
HTH, (new unrelated questions belong in new threads, so that
the title matches the question)
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"saeed sedighian" <sas...@yahoo.com> wrote in message