Porting from Excel 2003 to Excel 2010

[Walter Briscoe]

I have some context information below, which is untopical. Please bear
with me.
I've just bought a Lenovo X230 laptop to replace my X61S .
I have Windows 7 Enterprise Service Pack 1 32-bit.
[I googled to find how to get this information; a link required
Silverlight to be installed to give me the information that I needed to
do Start/Computer/Properties. I actually needed Start/Computer/System
Properties.]
That is REALLY good for me. I have some personally important software -
mail and news handler - which does not work on 64-bit.
While I do not have W7 Media, I can add and remove Windows features - I
tried with Telnet Client.
I have Microsoft Office Professional 2010. Again, lack of media does not
seem to be a problem.

Now I get to my difficulties. In 2003, I run with low security and
happily put VBA macros in .xls files. 2010 seems willing to read such
files, but, by default, creates .xlsx files, which I can't persuade
Excel to hold macros. I have many .xls files in which I write an
auto_open macro:
Option Explicit

Public Sub auto_open()
Application.Run "PERSONAL.XLS!CheckDateConsistency"
End Sub
"PERSONAL!CheckDateConsistency" fails, 2003 does not type default.
2010 prefers to have macros in .xlsm files.
I can probably cope with the default file type being .xlsm, but the
default file type is .xlsx.

If I copy my PERSONAL.XLS to
C:\Users\Lenovo\AppData\Roaming\Microsoft\Excel\XLSTART
[...AppData... as a hidden directory was an issue til I changed with
ATTRIB -H]

when I run any macro in PERSONAL.XLS, loading fails, I get "Compile
error: Can't find project or library", focus is (spuriously) put on a
call of the function Left and I am shown a list of libraries including
MISSING: Microsoft Office Runtime 1.0 Type Library
and
MISSING: Microsoft Office Control 1.0 Type Library.

Unchecking both allows PERSONAL.XLS to compile without error.

I have files copied and openable. 2010 uses 7 lines above the column
titles; 2003 uses 6. A small difference, but my new 12.5" screen is
16:9, my old one is 4:3. Ctrl+F1 toggles hiding and showing the ribbon.

I would appreciate pointers to some good porting documentation.
I want to move to 2010 with minimal pain and change.
(I also make some use of Word. I trust Excel lessons will easily port.)

 

[Claus Busch]

Hi Walter,

Am Thu, 31 Jul 2014 16:24:37 +0100 schrieb Walter Briscoe:

Public Sub auto_open()
Application.Run "PERSONAL.XLS!CheckDateConsistency"
End Sub
"PERSONAL!CheckDateConsistency" fails, 2003 does not type default.
2010 prefers to have macros in .xlsm files.
I can probably cope with the default file type being .xlsm, but the
default file type is .xlsx.

in xl2010 the personal macro workbook in named
PERSONAL.xlsb
Open Excel2010 => Record Macro => Save it in personal macro workbook =>
Cancel
Now your PERSONAL.xlsb is created.
Paste your Auto_Open macro into a module of the PERSONAL.xlsb and call
it with
Public Sub auto_open()
Application.Run "PERSONAL.XLSB!CheckDateConsistency"
End Sub


Regards
Claus B.

 

[GS]

Walter,
I use xl2010 pro as well, but xl2003 is also installed on the same
machine (this one) along with xl2007 running under Win7 x64 OS as x86.
Both xl2007/2010 open my xl2003 PERSONAL.XLS file with no problem, and
macros all work same as in xl2003.

I've made *some* macros 'version-aware' so they work in early/late
Excel. I have never needed to record macros in the later versions such
that I end up creating 'personal.xlsb'.

All my VBA projects are still developed in xl2003. They all work fine
in the later versions because I've made their code 'version-aware'.
This often requires revision in the later version so I have access to
the objects/methods/functions not included in xl2003. The VBA project
still saves in the original xl2003 format.

I put my PERSONAL.XLS file in the default location...

C:\Users\Garry\AppData\Roaming\Microsoft\Excel\XLSTART

...so Excel knows where to find it at startup. Since I share this file
over my home network with 3 other machines running versions xl8 and
later, I didn't want to have 2 versions of it. So far it works a dream!
I just copy it to a network share where the other machines can access
it at startup. One of those machines is a netbook, meaning it's
portable and so I store its copy local in the same folder as mentioned.
It and this one both are Win7 x64 OS, with the netbook running Home
Premium.

(The other 2 machines are Dell Precision series portable workstations
running XP Pro x86)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Walter Briscoe]

Thanks, Garry & Claus.

Walter,
I use xl2010 pro as well, but xl2003 is also installed on the same
machine (this one) along with xl2007 running under Win7 x64 OS as x86.

How would I add 2003 to a machine running 2010. I have media for 2003,
but not for 2010.
How do you select the version of Excel?

Both xl2007/2010 open my xl2003 PERSONAL.XLS file with no problem, and
macros all work same as in xl2003.

I found that. I did a quick run with a large macro. There seemed to be
an issue with the membership of an object. (TextFrame not containing
AutoMargins in 2010.) If that is the level of incompatibility, I am much
more comfortable.

I've made *some* macros 'version-aware' so they work in early/late
Excel. I have never needed to record macros in the later versions such

I googled and found Application.Version = 11.0 in 2003 and 14.0 in 2010.

that I end up creating 'personal.xlsb'.

All my VBA projects are still developed in xl2003. They all work fine
in the later versions because I've made their code 'version-aware'.
This often requires revision in the later version so I have access to
the objects/methods/functions not included in xl2003. The VBA project
still saves in the original xl2003 format.

I put my PERSONAL.XLS file in the default location...

C:\Users\Garry\AppData\Roaming\Microsoft\Excel\XLSTART

I did the equivalent operation. It was convenient that I found an empty
XLSTART folder.

..so Excel knows where to find it at startup. Since I share this file
over my home network with 3 other machines running versions xl8 and
later, I didn't want to have 2 versions of it. So far it works a dream!
I just copy it to a network share where the other machines can access
it at startup. One of those machines is a netbook, meaning it's
portable and so I store its copy local in the same folder as mentioned.
It and this one both are Win7 x64 OS, with the netbook running Home
Premium.

Do you run 64 bit Office at all? I would not expect any advantage to me
from doing so. I don't care about 64-bit addresses and have no need for
64-nit int.

(The other 2 machines are Dell Precision series portable workstations
running XP Pro x86)

I am much more confident than I was at first sight.

 

[GS]

How would I add 2003 to a machine running 2010. I have media for

2003,
but not for 2010.

What do you mean by "I have media for 2003"?
My MSO11 Pro is on a disc. My MSO14 Pro is a download. Just install
both, but the last install will become the default. If you want 2010 as
default then run 'Repair' in 'Programs and Features' to restore it as
default.

How do you select the version of Excel?

I use 'Freebar' from sourceforge! It's a VB6 replacement for the Office
Toolbar. I put a shortcut on there for whatever apps I want and just
select from there to run whatever version I want. Fortunately, each
version has a unique icon, making it easy to distinguish them. The
toolbar also displays a 'tooltip' when you hover over its icons.

Note that the usual notification about personal.xls being already open
displays when running more than 1 version.

You can get Freebar here...

http://freebar.sourceforge.net/en/

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Walter Briscoe]

What do you mean by "I have media for 2003"?

I mean I have Office 2003 on a disc.

My MSO11 Pro is on a disc. My MSO14 Pro is a download. Just install

I infer I can reload MS014,given a URL and some possible magic.

both, but the last install will become the default. If you want 2010 as
default then run 'Repair' in 'Programs and Features' to restore it as
default.


I use 'Freebar' from sourceforge! It's a VB6 replacement for the Office
Toolbar. I put a shortcut on there for whatever apps I want and just
select from there to run whatever version I want. Fortunately, each
version has a unique icon, making it easy to distinguish them. The
toolbar also displays a 'tooltip' when you hover over its icons.

Note that the usual notification about personal.xls being already open
displays when running more than 1 version.

You can get Freebar here...

http://freebar.sourceforge.net/en/

Thanks, that is likely to be REALLY useful.

I referred to some object model issues.
A long time ago, I wrote Sub ShowShapes() to dump ActiveSheet Shapes to
the Immediate Window. It does not work in 2010. I have commented On
Error calls to show some of the discrepancies.
I created a Macro to create a "Hello, World" textbox and added a call of
Showshapes to it.
In 2003, this is written to the Immediate Window.
1 shapes
Ix Name Shapetype Left,Top,Width,Height AM, AS, M(L, T, R, B) Text
1.0 Text Box 4 msoShapeRectangle 195,107.25,164.25,87 Tr, Fa, M(7.2,3.6,7.2,3.6) F(Regular,Arial,8): "Hello, World"

In 2011, I get a 1004 error, when I try to access
ActiveSheet.Shapes(1).TextFrame.AutoMargins

Without suppressing On Error statements, the code writes
1 shapes
Ix Name Shapetype Left,Top,Width,Height AM, AS, M(L, T, R, B) Text
1.0 Text Box 4 msoShapeRectangle 195,107.25,164.25,87 Tr, Fa, M(7.2,3.6,7.2,3.6)

I would value any insight into the deficiencies in my code.
In 2011, ActiveSheet.Shapes(1).TextFrame.AutoMargins shows in the Locals
Window as <Application-defined or object-defined error>

I use shapes as I am given worksheets containing shapes. ;(

Option Explicit

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 01/08/2014 by IBM
'

'
If ActiveSheet.Shapes.Count <> 0 Then ActiveSheet.Shapes(1).Delete
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 195#,
107.25, _
164.25, 87#).Select
Selection.Characters.Text = "Hello, World"
With Selection.Characters(Start:=1, Length:=12).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ShowShapes
Stop
End Sub

Public Sub ShowShapes() ' Noddy to dump shapes on ActiveSheet
Dim GroupName As String
Dim i As Long, j As Long
Dim o As ShapeRange
Dim s As String
Dim Saved As Boolean
Dim v As Shape
Dim W As Shape

Saved = ActiveWorkbook.Saved
Debug.Print ActiveSheet.Shapes.Count & " shapes"
Debug.Print Left("Ix ", 6) & _
Left("Name" & " ", 14) & _
Left("Shapetype ", 19) & _
Left("Left,Top,Width,Height" & " ", 28) & _
"AM, AS, M(L, T, R, B) Text"
i = 0
For Each v In ActiveSheet.Shapes
i = i + 1
If Not v.Name Like "Group *" Then
Debug.Print ShapeLine(i, 0, v)
Else
GroupName = v.Name
' Can't analyse a group without destroying it
Debug.Print ShapeLine(i, 0, v) & "consists of " & _
v.GroupItems.Count & " items"
Set o = v.Ungroup
j = 0
For Each W In o
j = j + 1: Debug.Print ShapeLine(i, j, W)
Next W
o.Group ' Recreate group
' Restore default name V is destroyed by ungroup
ActiveSheet.Shapes(i).Name = GroupName
End If
Next v
ActiveWorkbook.Saved = Saved
End Sub

Private Function ShapeLine(ByVal Imain As Long, ByVal Isub As Long, _
ByVal v As Shape) As String
Dim ShapeType As String
Dim Text As String

ShapeType = TXAutoShapeType(v)
Text = ShapeText(v)
ShapeLine = Left(Imain & "." & Isub & " ", 6) & _
Left(v.Name & " ", 14) & _
Left(ShapeType, 19) & _
Left(v.Left & "," & v.Top & "," & v.Width & "," & _
v.Height & " ", 28) & _
Text
End Function

Private Function TXAutoShapeType(ByVal x As Shape) As String
Dim s As String

Select Case x.AutoShapeType
Case msoShapeMixed: s = "msoShapeMixed"
Case msoShapeRectangle: s = "msoShapeRectangle"
Case Else
Debug.Print "Untranslated AutoShapeType: " & x.AutoShapeType & _
"."
Debug.Print "cf. x.AutoShapeType in Locals window to get name"
Debug.Assert False ' Force error
End Select
s = Left(s & " ", 20)
TXAutoShapeType = s
End Function

Private Function ShapeText(ByVal v As Shape) As String
' On Error statements in this are suppressed.
Dim s As String
Dim i As Long
Dim j As Long

' On Error Resume Next
With v.TextFrame
'
' Crashes here with a 1004 as AutoMargins has invalid contents.
'
s = IIf(.AutoMargins, "Tr, ", "Fa, ") & _
IIf(.AutoSize, "Tr, ", "Fa, ")
s = s & "M(" & .MarginLeft & "," & .MarginTop & "," & _
.MarginRight & "," & .MarginBottom & ") "
With .Characters.Font
If Err.Number <> 0 Then _
Exit Function ' Return empty string if no textframe
' On Error GoTo 0 ' Any errors now are fatal
s = s & "F(" & .FontStyle & "," & .Name & "," & _
.Size & "): """
End With
' On Error Resume Next
j = .Characters.Count
If Err.Number <> 0 Then
' On Error GoTo 0 ' Any errors now are fatal
s = s & "NO TEXT"
Else
' On Error GoTo 0 ' Any errors now are fatal
' Text limits itself to 255 bytes
For i = 1 To j Step 255
s = s & .Characters(Start:=i).Text
Next i
End If
End With
s = s & """"
ShapeText = s
End Function

 

[GS]

I don't do much with 'Shapes' but I'll play around with it over the
weekend...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Walter Briscoe]

I don't do much with 'Shapes' but I'll play around with it over the
weekend...

Thanks, Garry.
I did not say that my Sub Macro1() should be run in a new WorkSheet.
I like to construct failures which are standalone and can easily be
ported by anyone interested. I shall play too.

 

[Walter Briscoe]

In message <[email protected]> of Sat, 2 Aug 2014
08:03:51 in microsoft.public.excel.programming, Walter Briscoe

Thanks, Garry.
I did not say that my Sub Macro1() should be run in a new WorkSheet.
I like to construct failures which are standalone and can easily be
ported by anyone interested. I shall play too.

I have a hypothesis: Excel text boxes do not support automatic margins
in 2010.
In 2003, I right-clicked the box right hand edge and selected Format
Text Box... I selected the Margins Tab and found Automatic checked and
the margins blank. When I unchecked Automatic, the margins were given
values of Left: 0.25 cm, Top: 0.13 cm, Right: 0.25 cm and Bottom: 0.13
cm.
In 2010, I right-clicked the box right hand edge and, in the absence of
Format Text Box..., I selected Format Text Effect...
I selected the Text Box entry and found the same margins as given above,
but no automatic margin control.
Hence my hypothesis. I shall have to write some version-dependent code.
If I had support, I would raise a query with Microsoft on a possible
undocumented change.

 

[GS]

So far I found in 2003/2010 ObjectBrowser...

Shape>TextFrame>AutoMargins

...is a common property of type Boolean. Perhaps the issue is that of
using a fully qualified ref to the specific 'Shape' object in question?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Walter Briscoe]

So far I found in 2003/2010 ObjectBrowser...

Shape>TextFrame>AutoMargins

..is a common property of type Boolean. Perhaps the issue is that of
using a fully qualified ref to the specific 'Shape' object in question?

Why might that be a problem? How would I access it otherwise?

I now have some code which "seems to work" in both Excel 2003 and 2010.

I simplified my driving code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 01/08/2014 by IBM
'

'
Dim sh As Shape
If ActiveSheet.Shapes.Count <> 0 Then ActiveSheet.Shapes(1).Delete
Set sh = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 195#, 107.25, 164.25, 87#)
sh.TextFrame.Characters.Text = "Hello, World"

ShowShapes
Stop
End Sub

I removed the On Error logic from Function ShapeText and put some
version specific code in place.
It is GOOD to be rid of error handling code.
It is POOR to have version specific code. ;(

Private Function ShapeText(ByVal v As Shape) As String
Dim s As String
Dim i As Long
Dim j As Long
Dim TF As TextFrame
Dim Ch As Characters

Set TF = v.TextFrame
With TF
If Application.Version = "14.0" Then
s = "Un, " ' Automargins does not seem defined in Excel 2010
Else
s = IIf(.AutoMargins, "Tr, ", "Fa, ")
End If
s = s & IIf(.AutoSize, "Tr, ", "Fa, ")
s = s & "M(" & .MarginLeft & "," & .MarginTop & "," & _
.MarginRight & "," & .MarginBottom & ") "
Set Ch = .Characters
If Ch.Text = "" Then ShapeText = s & "NO TEXT": Exit Function
With Ch.Font
s = s & "F(" & .FontStyle & "," & .Name & "," & .Size & "): """
End With
j = Ch.Count
' Text limits itself to 255 bytes in 2003. Safe limit not checked in 2010
For i = 1 To j Step 255
s = s & .Characters(Start:=i).Text
Next i
End With
s = s & """"
ShapeText = s
End Function

Characters is a curious member of TextFrame, behaving as both a Property
and a Method.
I find it a pity that View\Locals does not show it as a Property.

I am now comfortable that I can move on to the next porting issue.
Thanks to both Claus and Garry.

 

[GS]

It is GOOD to be rid of error handling code.

I find error handling an essential part of programming and so consider
it just part of 'good practice'!

It is POOR to have version specific code. ;(

I find that making code to work in all versions needs to be
'version-aware' and so also consider that as being a 'good practice'
part of programming!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

I meant to point out that as of MSO 2010, VBA7 is used. Earlier
versions run VBA6!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion