Macros in 2007

[DeborahP]

I created a macro in 2007. Tried to run it and I get some errors messages.
Here is a few lines of my code:

Range("A3").Start
. = "=NOW()"
Range("A3").Start
.Borders = "[$-409]mmmm d, yyyy;@"
Range("A1").Start
. = "ABC COMPANY"
Range("A2").Start
. = "Quarterly Report"
Range("A6").Start
. = "Region"
Range("A7").Start
. = "North"
Range("A8").Start
. = "East"
Range("A9").Start
. = "South"
Range("A10").Start
. = "West"
Range("A12").Start
. = "Total Sales"
Range("B6").Start
. = "Sales "&chr(10)&"Amount"
With .Alignment(ErrorString := 1, Native := 13).
.Interior = "Calibri"
. = "Regular"
. = 11
. = FALSE
. = FALSE
. = FALSE
. = FALSE
.RectangleBottom = False

It seems as if my VBA editor is slicing my code. I am getting a lot of RED and sytax message.

Can anyone help??

 

[boblarson]

First of all, what "error messages" are you getting? It doesn't help us to diagnose the problem if you don't give us the applicable information.

Second, this part has too many = signs:

With .Alignment(ErrorString := 1, Native := 13).
.Interior = "Calibri"
. = "Regular"
. = 11
. = FALSE
. = FALSE
. = FALSE
. = FALSE

And what is with all of the . = False stuff? You don't use periods to separate arguments, you use commas (or semi-colons if in certain regions).

It might help if you can post the Excel Spreadsheet.

 

[DeborahP]

I know there are alot of ""And what is with all of the . = False stuff? You don't use periods to separate arguments, you use commas (or semi-colons if in certain regions).""

It seems to be taking my macro and creating invalid VBA code. I went to the same macro in Excel 2003 and copied and pasted it in 2007. It worked.
I am not sure what is happening in 2007. I recorded the macro the right way.
It is screwing up my code.
It is putting Range("A3").Start
. = "=NOW()"
When it should have Range("A3").Start "=NOW()"

Any suggestions?

 

[boblarson]

Not sure what you are saying about it taking your macro and creating VBA code. Macros in Excel ARE VBA code. There is no conversion. So, you are trying to record these actions and then when you run it, it doesn't work?

Sorry to ask this, but can you outline, step by step, the steps you took when recording the macro so I can try doing it here? I'm not sure what the Range("A3").Start comes from so if you can give a few of the steps, I can see what it does for me.

 

[HaHoBe]

Hi, Deborah,

in none of the Versions I own (Excel97 to Excel2010 - all for Windows) the Range-Object has a Start-Value - if you created a class giving the Range that attribute you should tell us about that.

Range("A3").Borders = "[$-409]mmmm d, yyyy;@"

This line of code is wrong as border looks for LineStyle, Weight, ThemeColour and an inidcation of which border of a cell to work on but certainly not for a formatting as a date. If it´s formatting it should be assigned to the Range.NumberFormat.

'With .Alignment(ErrorString := 1, Native := 13).
'.Interior = "Calibri"
'. = "Regular"
'. = 11
'. = FALSE
'. = FALSE
'. = FALSE
'. = FALSE
'.RectangleBottom = False

This bit is incomplete as no indication is given which object is used (same as above).

I´d think this code never had a chance to run in any version of Excel as the name of a font is part of the Font-Object (or the Character-Property) in Excel and should be advised by it´s Name-Property.

If there are problems with the display of the code you could export the module in 2003 and import it in 2007. If there is still a problem please use the way via Word for the import.

Could you please attach a copy of the workbook and describe what the macro is supposed to do?

With Range("A3")
  .Formula = Date
  .NumberFormat = "[$-409]mmmm d, yyyy;@"
End With
Range("A1").Value = "ABC COMPANY"
Range("A2").Value = "Quarterly Report"
Range("A6").Value = "Region"
Range("A7").Value = "North"
Range("A8").Value = "East"
Range("A9").Value = "South"
Range("A10").Value = "West"
Range("A12").Value = "Total Sales"
Range("B6").Value = "Sales " & Chr(10) & "Amount"
'With .Alignment(ErrorString := 1, Native := 13).
'.Interior = "Calibri"
'. = "Regular"
'. = 11
'. = FALSE
'. = FALSE
'. = FALSE
'. = FALSE
'.RectangleBottom = False

Ciao,
Holger

 

[DeborahP]

I am teaching an Excel 2007 class tonight.
I was reviewing the exercises for Macros and got the error messages.
The recording is simple.
I click on the bottom left the record macro button, type in the name and the create my sheet with headers and formulas. I stop recording, go to sheet 2 and run the macro. It errors out immediately with a syntax error message. It seems to break the code...every other line is in red.

I copied the old code from the same exercise from when I taught it in version 2003. It worked. However, I need to have it working for class.

Is there something I need to do in 2007 to get my VBA to work correctly?

 

[chergh]

Is there something I need to do in 2007 to get my VBA to work correctly?

Yeah you need the code to be written correctly for it to work in 2007. The code you have posted above wouldn't work in any version of excel. Should you really be teaching macros if you don't understand them?