Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Simple Macro to Format All Sheets in a Workbook?

  Asked By: Leon    Date: Dec 14    Category: MS Office    Views: 638
  

I'm a far rawer VBA programming than anyone. I scarcely know what a
Recordset is. I'd be grateful for some code that does the following:

Goes to the last tab in the current Workbook. Tests to see if it's
also the first tab in the current workbook. If not, it runs macro
Format1 and moves to the previous tab. If it is the first tab, it runs
macro Format2 and quits. It must not move to the next previous tab
until Format 1 has run completely. Format1 and Format2 are located in
PERSONAL.XLS, where this one will be too. They are recorded macros.

This code has to involve a loop, and probably involves MovePrevious
and MoveLast, both of which require a specification for the
placeholder "recordset." For one thing, I have no idea what to put there.

I imagine this will also use BOF and EOF properties, but I don't know
how to use them. I scarcely understand what a property is. And I need
this macro to work yesterday to meet a deadline (which is why I'm
learning VGA as fast as I can).

Somebody please put me out of my misery by writing what must be a
simple bit of code for me? I promise I'll still go on to learn VBA.

Share: 

 

8 Answers Found

 
Answer #1    Answered By: Helene Stewart     Answered On: Dec 14

This should do it

Option Explicit

Sub formatSheets()

Dim x
Dim y

x = Worksheets.Count

While x > 1

Worksheets(x).Activate
Format1
doevents
x = x - 1
Wend
Application.Worksheets(x).Activate
format2

End Sub

 
Answer #2    Answered By: Feodora Bonkob     Answered On: Dec 14

This is very helpful indeed. I think I see how it works. What's "Wend"
mean?

It's nothing like what I expected. I didn't know about
"Worksheets.Count." Nor that you could refer to sheets  by number. I
had the idea  one would use MoveLast somehow, with something like
"Activesheet.MoveLast," although that doesn't work. Then MoveLeft, use
y = BOF, MoveRight. If y = False run  format1, if y = True run format2.
Was I even in the ballpark?

Anyhow, this does the trick, I'm sure. I haven't tried it yet, but
I'll do that next. For a refinement, let me ask you if there's a way
to test if the active worksheet contains any text? And if not, delete
it instead of running either of the format  macros?

God, I've got a lot to learn. I didn't even know Sheets existed, and
I'm still shaky about its syntax. Where can I find exhaustive lists of
the commands, recordsets, and so forth, available? I've several books
and none of them really has that.

 
Answer #3    Answered By: Della Simpson     Answered On: Dec 14

Terms like moveLast, BOF, EOF are for dealing with a text file or a database.
In this case you are using object oriented programming  and therefore you use
an object (e.g. a spreadsheet) which may be in a collection (Spreadsheets) a
collection has a count (How many spreadsheets are there) and each item in the
collection can be referred to by its index (1 to Count) or by its name.

 
Answer #4    Answered By: Devrim Yilmaz     Answered On: Dec 14

OK, I guess Wend = WhileEnd or EndWhile.

It works fine, with the sole exception that I had to comment out the
"Option Explicit" statement because it made the debugger burp.
Something about "invalid inside procedure" or the like. What was
variable y supposed to be for?

 
Answer #5    Answered By: Ella Brown     Answered On: Dec 14

Your Option Explicit should be the very first line of the module. It can't
go inside a subroutine or function. However, none of that code  should have
been put  inside a subroutine or function, so I suspect your copy/paste
missed.

Consider Option Explicit as absolutely essential for VBA development -
particularly if you plan to ask questions on Excel groups - it reduces the
possibility of typos causing obscure problems.

Looks as though "y" was defined but never used.

 
Answer #6    Answered By: Liam Bouchard     Answered On: Dec 14

So much for trying to learn  this stuff from books. That's the first
time I've gotten a clear idea  of what a Collection is, that Excel VBA
is object-oriented programming, and that EOL and BOF aren't even
useable in Excel VBA. Please bear with me as I ask more ignorant
questions.

1. What are the other items in the Excel Collection(s)? Where can I
find a list of such things, so I know what I can refer to and what its
proper name is? How was I to know that worksheet objects are called
"Sheets" and not  "Worksheets" or "Tabs"?

2. I understand  about Option Explicit, and why it's essential, but I
note that the routine you sent me leaves the variable to be defined by
default as Variant. Could Option Explicit have caused a problem
because the variable(s) were not explicit?

3. I notice that when I record a macro, and in the code  you sent me,
the whole thing  is called a "sub". I imagine that's what makes it
callable by name from other macros. What's a "procedure" and how do
procedures relate to macros  and subs?


4. How is "method" defined, and where can I find a list of the
methods available in Excel VBA? Is a method a kind of verb that acts
on an object, which is analogous to a noun? How can I find the
syntactical requirements for methods, and for that matter, what they do)?

 
Answer #7    Answered By: Hababah Younis     Answered On: Dec 14

> 1. What are the other items in the Excel Collection(s)? Where can I
> find a list of such things, so I know what I can refer to and
> what its proper name is?
Try gooling for the object model. If you can get a pictorial representation
that's great. The object model shows what is connected to what and gives
great clues as to how to reference things. Also... In the VBE you could try
having a look at the object browser but it's a bit  daunting at first if you
don't know what your looking at!

> How was I to know that worksheet
> objects are called "Sheets" and not  "Worksheets" or "Tabs"?
Object model... Sample code... Excel VBA Help... Ask here!!!
You could also try the techtrax site for tutorials and info :-)

> 3. I notice that when I record a macro, and in the code  you sent me,
> the whole thing  is called a "sub". I imagine that's what
> makes it callable by name from other macros. What's a
> "procedure" and how do procedures relate to macros  and subs?
"Macro" is really a misnomer and comes from... IMHO,, (Again!! This could
get boring!!) the dark ages of Excel and other programs.
Subs are Procedures. It's that simple. So are functions. The difference is
that Subs don't return a value.
Procedures are just lines of code to do things... Pick up that.. Do this...
Do that..
simple  sub to beep would be..

Sub soundbeep
Beep
End Sub

You would make it happen in your code by writing

Soundbeep

If you wanted to return a value... Like setting a date format  for example,
you'd use a function... Which remember is still a procedure.. Just a
different type.. One that returns ummm .. Values
Sub is from subroutine I think.

> 4. How is "method" defined, and where can I find a list of the
> methods available in Excel VBA? Is a method a kind of verb
> that acts on an object, which is analogous to a noun? How can
> I find the syntactical requirements for methods, and for that
> matter, what they do)?
Methods... Well!!!!
There are, very basically 2 dinguses that an object may have...
Properties... And Methods.
Methods do things.... Examples are .Delete .Add .Hide .Load and so
on.
Properties are values describing status.
If you use the .Hide method for example then there is a .Visible property
that changes to False.
To find out what properties/methods an object has you have to look at the
documentation in the help. Having said that... There is a lot of information
that pops up when you type a dot "." after an objects name.

 
Answer #8    Answered By: Alfonsine Miller     Answered On: Dec 14

> If you use the .Hide method for example then there is a
> .Visible property
> that changes to False.

Just checked... I wuz wrong!!! Can you believe that!!! LOL!!

Anyroad... I think you get the idea... The questions you were asking showed
you got some smarts!

 
Didn't find what you were looking for? Find more on Simple Macro to Format All Sheets in a Workbook? Or get search suggestion and latest updates.




Tagged: