vba in excel 2007

[blueflash]

I have a vba program written in excel 2003 and am trying to get it to run in excel 2007.
The program gives a runtime error 5 "Invalid procedure call or argument" at the statement
Selection.Characters(Start:=2, Length:=5).Font.Name = "Arial"

Could someone tell me how to correct this please.

Also is there any "converter" that will help in converting 2003 vba to run in excel 2007 and 2010?

Thanks

 

[joeu2004]

I have a vba program written in excel 2003 and am trying
to get it to run in excel 2007. The program gives a runtime
error 5 "Invalid procedure call or argument" at the statement
Selection.Characters(Start:=2, Length:=5).Font.Name = "Arial"
Could someone tell me how to correct this please.

Works fine for me. You have some other error.

First, did you copy-and-paste that statement from the XL2007 macro into your
posting?

If not, you might unconsciously fixed whatever syntax error your have in the
XL2007 macro.

Second, is Selection another object, for example a chart, not a cell?

Third, is the Arial font available in your XL2007?

 

[blueflash]

Thanks for your reply.
I typed in the statement and checked that it was exactly how it was in the code.
I am trying to change the font name for a data label on a chart.
Arial is available as I can use that font when changing manually.
The program always stops at this line with runtime error 1004
Unable to set the name property of the font class.
a few lines of code above the stopping point are:

ActiveChart.SeriesCollection(2).Points(6).DataLabel.Select
Selection.Characters.Text = "95"
Selection.Characters(Start:=2, Length:=5).Font.Name = "Arial"
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Position = xlLabelPositionLeft
.Orientation = xlHorizontal
End With
ActiveChart.SeriesCollection(2).Points(7).DataLabel.Select
Selection.Characters.Text = "90 "
Selection.Characters(Start:=2, Length:=5).Font.Name = "Arial"

 

[Martin Brown]

Works fine for me. You have some other error.

First, did you copy-and-paste that statement from the XL2007 macro into
your posting?

If not, you might unconsciously fixed whatever syntax error your have in
the XL2007 macro.

Second, is Selection another object, for example a chart, not a cell?

Third, is the Arial font available in your XL2007?

Mostly likely cause is whatever he has selected at the time doesn't like
the requested action. XL2007 VBA is seriously incompatible with XL2003
where shapes and graphs are concerned. And the macro recorder is so
hopelessly broken that it might as well not exist.

 

[Walter Briscoe]

In message <[email protected]> of
Wed, 23 May 2012 23:57:36 in microsoft.public.excel.programming,

Thanks for your reply.
I typed in the statement and checked that it was exactly how it was in
the code.
I am trying to change the font name for a data label on a chart.
Arial is available as I can use that font when changing manually.
The program always stops at this line with runtime error 1004
Unable to set the name property of the font class.
a few lines of code above the stopping point are:

[snip]

ActiveChart.SeriesCollection(2).Points(7).DataLabel.Select
Selection.Characters.Text = "90 "
Selection.Characters(Start:=2, Length:=5).Font.Name = "Arial"

I am still looking for a USP to persuade me to move to excel 2007.

I constructed a minimal VBA module which works in 2003.
I tried porting it to 2007.
I found help on "run a macro useful".
I found I had no Developer tab.
I clicked the Microsoft Office Button [I found it at the top left hand
corner of my excel window] and "Excel Options". I checked "Show
Developer tab in the Ribbon" and "OK", but got "The server drafts
location you entered for offline editing is not valid or you do not have
permission to access that location. Specify a location on your local
computer."
Googling suggests using Options Save.
"Server drafts location" was empty and browsing for one selected the
drive containing my .xls file. I chose C:\temp\ServerDraftLocation.
That did not fix my problem.
Some how or other, I could live with it and successfully ran the
following code:

Option Explicit

Sub aaMacro1()
Cells(1, 1) = "Hello world"
Cells(1, 1).Font.Name = "Comic Sans" ' Maybe not needed?
Cells(1, 1).Select
Selection.Characters(Start:=2, Length:=5).Font.Name = "Arial"
End Sub

I suggest you do a binary chop between my code and yours to get the
simplest piece of stand-alone code which shows the problem.
Then post that code. Others may then solve it for you.
Alternatively, post a workbook on a site which allows that sort of thing
and a reference to the file with instructions on running it.

 

[joeu2004]

I typed in the statement and checked that it was
exactly how it was in the code.

As I said before, you should copy-and-paste the code from VBA to your
posting. Humans are very good at overlooking minor typos, correcting them
when retyping, and failing to notice the difference.

I am trying to change
the font name for a data label on a chart. Arial is
available as I can use that font when changing manually.
The program always stops at this line with runtime error
1004 Unable to set the name property of the font class.
a few lines of code above the stopping point are:

All works fine for me in both XL2003 and XL2007, at least in an XY Scatter
chart after selecting the chart and manually adding data labels.

I suggest that you upload an example Excel file (devoid of private data)
that demonstrates the problem to a file-sharing website and post the
"shared", "public" or "view-only" link (aka URL; http://...) in a response
here. The following is a list of some free file-sharing websites; or use
your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com