Codatron® from
Oil 4 Less® LLC

High Voltage Zener

--Home Page

Excel rant

    This is a part of what may be a continuing series.

     Excel is a rather powerful spreadsheet program that mostly works as expected.

     However, there are some operations that have never been fixed and others that have been removed (IE: crippled).

  "Copy" #1

     Charts are in fact not copied, what pretends to be a copy is actually smaller.

  So if you need a number of charts for presentation, it would be nice if they all were the same size.
Therefore, do NOT "copy" from #1 to #2, then "copy" from #2 to #3, etc; you will get a shrinking set of charts.
  First make a master chart suitably larger than needed and use "copy" to generate viewing chart #1 and add in specifics for #1. Then use "copy" from the master to generate viewing chart #2 and add in specifics for #2.
Bingo, ALL viewing charts are exactly the same size.

  "Copy" #2

    Say you want to make a tic-tac-toe or similar checkerboard-like display.
The code might look something like:

    ActiveSheet.Shapes("Picture 6").Select
    Selection.Copy      'vertical jumper
    Selection.ShapeRange.IncrementLeft -50
    Selection.ShapeRange.IncrementTop 235

    A few comments are in order here: as far as I can tell, the "Selection.Copy" does not copy.
It is the "ActiveSheet.Paste" that actually creates a "copy" of the current object, and it is NOT a true copy.
The new object is offset horizontally and vertically from the original.
It seems this offset is related to the size of the object.
  Worse yet, the movement scale factor looks different between negative increments and positive increments.
Just try to "reset" the position of an object thus:

'  This code snippet is from a working program that visually places jumpers
' by selecting an object, copying it and the placing it at a desired position.
    ActiveSheet.Shapes("Picture 6").Select     ' see object #1 selected
    Selection.Copy                             ' see NO change
    ActiveSheet.Paste                          ' see OFFSET copy of object
    Selection.ShapeRange.IncrementLeft 163.1   ' (relative) move it to right
    Selection.ShapeRange.IncrementTop 559.1    ' then move it down
'  Now try to move it back..
    Selection.ShapeRange.IncrementLeft -163.1
    Selection.ShapeRange.IncrementTop -559.1
'  Visually note the object is NOT back where original began; it is OFFSET
' Technically, this moved object is exactly where the offset "copy" was found.

    Be advised that the movements required are determined by repetitive trial and error.
One cannot calculate the movements due to the offset which seems to be related to the size of the object.
So in effect one is faced with unpredictable movement amounts.
    Ain't Excel wonderful?

  [not]Visible, Deleted object(s)

    Say you want/need to start with an object and later need to remove it:

   ' delete J7 and attempt to find it (Excel 2003)
    shState = ActiveSheet.Shapes(6).Select  ' shState after execution is "empty"
    Selection.ShapeRange.Delete             ' visibly gone
    shState = ActiveSheet.Shapes(6).Select  ' see no change.;

    Well that is fine and is gone and cannot be restored. Not useful.
So, try making it invisible:

    shState = ActiveSheet.Shapes(6).Select ' shState after execution is "empty"
    Selection.ShapeRange.Visible = False   ' visibly gone
    shState = ActiveSheet.Shapes(6).Select ' Run time error.
'                                   Method 'Select' of object 'Shape' failed

    Well this is worse! So try handy dandy object remover:

 num = 1
 For Each iobj In ActiveSheet.Shapes    'DrawingObjects
  If num > 3 Then       ' want to keep construction objects
  End If
  num = num + 1
 Next iobj

    Well this is worse yet! Get the same error (object #6, the NOT visible one).
That means it is so NOT visible that it cannot be selected. However, if one ignores that ugly fact, it CAN be deleted.
    Ain't Excel wonderful?

  Possible work-around to [not]Visible, Deleted object(s)

    Again, from working code (Excel 2003):

'Initially by hand, put jumpers AKA Shapes(5,,12) in proper places
'  to allow jumper removal by making them not visible but STILL accessible.
  Dim posJUMP(12)
  ' save positions of jumpers
   For iJUMP = 5 To 12
     posJUMP(iJUMP) = Selection.ShapeRange.Left
   Next iJUMP
'sample of trick..
   If (Not J1) Then
     Selection.ShapeRange.Left = -400 'off screen, therefore not visible but accessible
   End If
    'Restore jumper positions for next try
 For iJUMP = 5 To 12
   Selection.ShapeRange.Left = posJUMP(iJUMP)
 Next iJUMP

    This seems a perfect solution. Hand copy and placement can be as accurate as one wants.
Note vertical position is left untouched.

  Printing to non-default printer

    Printing to a printer other than the default may be, in effect impossible.
That all depends on the version of Excel one is using. Excel 2003 works in this regard.
But Excel in Office 2010 has been crippled.
In fact, for Excel in Office 2010, printing seems impossible unless one can open the printer as a file,
  which may be a problem (untested).
Again working code, assuming Excel 2003 or similar:

    vNam = Cells(2, 7).Text + "V"   'board voltage
    pPath = Cells(2, 10)
    DLC = Cells(4, 6)    'Date Lot Code
    SerNum = Cells(5, 6) 'Board serial number
    DaTm = Date$ + " " + Time$  'Present date and time
    Open pPath + "Log.TXT" For Append As #1
    eRv = Cells(3, 7)   'programming error voltage
    dp = InStr(eRv, "."): If (dp > 0) Then eRv = Left$(eRv, dp + 2)
    Print #1, DLC + " " + SerNum + " " + DaTm + " to " + vNam; _
    " error =";
    Print #1, eRv
' In Excel 2010, all of the following is moot due to faults in Excel.
' Therefore, quit this while ahead.
             'Now is the time to view results, then press F5 key
GoTo barf
    ActivePrinter = "Acrobat PDFWriter on FILE:"
' Above sets printer in Excel 2003; will crash in Excel 2010:
'      Run-time error '1004':
'      Method 'ActivePrinter' of object '_Global' failed
' In "modern" defective Excel apps, following attempts to print to
'   default printer as a file; NOT in PDF format. Fails.
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, _
    ActivePrinter:="Acrobat PDFWriter on FILE:", PrintToFile:=True, _
    Collate:=False, PrToFilename:=pPath + vNam
' Since it fails in Excel 2010, the files do not exist and the Kill fails.
' The PrToFilename assignment is not recognized, and there is no error message.
    Kill pPath + vNam   'only PDF left


    Ain't the new, crippled Excel wonderful?