Prev: for..in..do
Next: Change Desktop Wallpaper
From: Csaba Gabor on 8 Nov 2007 06:54
Hi Jay! Thanks for your ideas on conditional NumberFormatting. I
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


On Nov 7, 1:38 am, Jay <J...(a)discussions.microsoft.com> wrote:
....
> 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" wrote:
....
> > 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

From: Andrew Thompson on 8 Nov 2007 22:46
On Nov 8, 10:54 pm, Csaba Gabor <dans...(a)gmail.com> wrote:
....
> 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

From: Csaba Gabor on 10 Nov 2007 20:43
This is a second conditional formatting example. In this case I
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


 | 
Pages: 1
Prev: for..in..do
Next: Change Desktop Wallpaper