Formatting and Ignore error
Csaba Gabor
1) Removing error indicator: If I insert a long numeric value into a
cell that has been formatted as a string (or I prefix it with an
apostrophe: e.g. '1234567890123), then the cell gets a little green
triangle at its upper left. To remove this triangle, I select the
cell, then click on the dropdown to the left of the cell and select:
Ignore error. However, the macro recorder does not record anything
for this action. Question, how do I programatically remove the little
green triangle from the cell?
2) Formatting with alignment: I've got a column of numbers. If they
are not integers, I would like two decimals displayed (e.g. 5.3 should
display 5.30; presumably 5.809 would display 5.81 truncation is OK,
too). If they are integers, however, I would like them aligned with
the units digit of the values displaying decimals. (ie. displaying 6
should have the 6 should be directly below the digit 5 of 5.30, but
there should be no .00 following the 6). Is there a format which
would do this?
Thanks for any tips,
Csaba Gabor from Vienna
Excel 2003 on Win XP Pro
Jay
1) Application.ErrorCheckingOptions.NumberAsText = False
Note that the above statement turns off the NumberAsText rule for the whole
application (so error checking for "numbers as text" is totally disabled and
remains so until turned back on). This can also be done via menu option
[Tools], [Options], [ErrorChecking tab]...
2) The following procedure builds a custom format for integers by adding the
characters "_._1_1" to the existing general format of any integers in your
column. So, after running, you can examine the format (and modify if
desired) by checking the format manually (Format, Cells, Tab=Number,
Category=Custom...).
Sub Csaba02()
Set rng = Range("A2:A100") '<--- change to suit
'Initialize cell alignments to a common starting point
'(remove existing number formats and alignments)
With rng
.NumberFormat = "General"
.HorizontalAlignment = xlGeneral
End With
For Each itm In rng
If Int(itm.Value) - itm.Value < 0.005 Then
itm.Value = Int(itm.Value) 'truncates 67.001 to integer
itm.NumberFormat = itm.NumberFormat & "_._1_1"
Else
itm.NumberFormat = "0.00"
End If
Next 'itm
End Sub
---
Jay
Csaba Gabor
investigated using Conditional Formatting (found under the Format
menu), both programatically and via the menu, but that only allows one
to alter Border, Font, and Interior (eg. color) properties. Your
approach was a one pass (ie fixed formatting) deal, and I decided I'd
like to be able to alter the values in the cell, and have the
alignment adapt appropriately.
The following example is adapted from http://www.mvps.org/dmcritchie/excel/event.htm#case
and I'm including it since I see very few examples of stuffing code
into excel programmatically on the web. It is run using PHP but
anyone using VBScript should be able to adapt it directly (I've made
notes about that below). It creates a fresh excel spreadsheet and
stuffs code into it that reacts to any change on the spreadsheet. If
that change is within the first columns titled with the words in
$aCols ("Price" or "max Bid" in my example, which means columns E and
G) then its NumberFormat is adjusted automatically. If you have PHP
on your system, the code below starts from '<?php' and ends with '?>'
Plop it into a file (with .php extension) and run it directly.
<?php
print "Running excel example\n";
// initialization of excel
$excel = new COM("Excel.Application");
$excel->visible = true;
$wb = $excel->workbooks->add;
$sheet = $wb->sheets(1);
// putting some initial values into the sheet
$aStuffPre = array (E1 => Price, F1 => Whatever,
G1 => "max Bid", H1 => "max Bid", E3 => 98);
foreach ($aStuffPre as $cellAddr => $cellFormula)
$sheet->range($cellAddr)->Formula = $cellFormula;
$sheet->range("A2")->Formula =
"Make numeric entries in the colored columns";
$sheet->range("E1")->EntireColumn->Interior->ColorIndex = 6;
$sheet->range("G1")->EntireColumn->Interior->ColorIndex = 7;
// now add auto formatting code to the code module
$cm = $wb->VBProject->VBComponents(
$sheet->CodeName)->CodeModule;
$aCols = array("Total", "Price", "S/H", "Postage",
"max Bid", "Start price");
$cm->AddFromString ( $code =
'Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, formatRange As Range, _
commonRange As Range
aCols = array("' . implode('","', $aCols) . '")
Set Sheet = Target.Worksheet
Set formatRange = Nothing \'Range of interest
For i = LBound(aCols) To UBound(aCols)
Set myCol = Sheet.Cells(1,1).EntireRow.Find(aCols(i))
If Not(myCol is Nothing) Then
If (formatRange Is Nothing) _
Then Set formatRange = myCol.EntireColumn _
Else Set formatRange = _
Union (formatRange, myCol.EntireColumn)
End If
Next
\'Range Check
Set commonRange = Intersect(formatRange, Target)
If commonRange Is Nothing Then Exit Sub
For Each cell In commonRange
If IsNumeric(cell.Value) Then
If (cell.Value = Int(cell.Value)) Then
If cell.NumberFormat <> "_._1_1" _
Then cell.NumberFormat = "0_._1_1"
Else
If cell.NumberFormat <> "0.00" _
Then cell.NumberFormat = "0.00"
End If
End If
Next
End Sub'
);
// now stuff some values into cells
foreach (array(E, F, G, H) as $idx => $colLtr) {
$sheet->range($colLtr . "5")->Formula = $idx + 27;
$sheet->range($colLtr . "6")->Formula = 124.4567; }
?>
Notes: Hopefully there won't be any line wrap, but watch for it.
Most importantly, for versions of Excel 2003 and later, you may need
to allow programmatic access to the code module, described here:
http://support.microsoft.com/kb/282830 (Tools \ Macro \ Security \
Trusted Publishers \ Trust Access To Visual Basic Project)
For non PHP friends: The $ prefixes all variables and has no other
special significance. '->' in PHP is '.' in VB. A '.' in PHP is '&'
in VB (string concatenation). Both single and double quotes delimit
strings in PHP. PHP has no line continuation corresponding to '_' in
VB but lines of code are terminated with ';' instead of a CRLF. PHP's
comments start with '//' as opposed to VB's single quote. There is no
distinction necessary between 'Set' and simple assignment in PHP.
All arrays in PHP double as dictionaries where the indeces are keys.
foreach ($aray as $val) ... loops through all the values of an array/
dictionary while foreach ($aray as $key => $val) ... loops through all
the key value pairs for when you want both. Simple array assignment
is similar to VB: $foo = array(3, Bill, "Fred and Sue"); with the
additional shortcut that single word strings need not be quoted. The
indeces/keys are implied as 0, 1, 2 but may be overridden by prefixing
the value with the desired key: $bar = array(These => Values, are =>
go, keys => here);
Regards,
Csaba Gabor from Vienna
original thread at:
http://groups.google.com/group/microsoft.public.excel.programming/browse_frm/thread/14967ab235eb9cd2/e766061716f98a16
...
Andrew Thompson
...
> Notes: Hopefully there won't be anyline wrap, ...
A little tool designed to help check for possible line-wrap.
<http://www.physci.org/twc.jnlp>
I would recommend setting it to 62 char width for usenet postings.
Andrew T.
PhySci.org
Csaba Gabor
wanted a certain designated cells to exhibit a behaviour of taking on
a fixed color when they were blank and to turn clear if they had
something in them. Putting the following in a Module did the trick:
Sub setSelectedFormat()
setFormat
End Sub
Sub setFormat(Optional rng As Range)
If rng Is Nothing _
Then Set rng = Excel.Selection
With rng.FormatConditions
.Delete
.Add Type:=xlExpression, _
Formula1:="=ISBLANK(RC)"
.Item(1).Interior.ColorIndex = 27
.Add Type:=xlExpression, _
Formula1:="=NOT(ISBLANK(RC))"
.Item(2).Interior.ColorIndex = 0
End With
End Sub
I assigned the upper function a control key in Excel via Tools \ Macro
\ Macros (Alt+F8) \ Options. Evidently, Excel is unhappy running a
sub with an optional argument which is why there are two functions.
Also, note that copying the format from another cell to one of these
conditional ones will remove the conditional formatting.
Csaba Gabor from Vienna