Questions concerning VBA coding

[Sloth]

I recently created a new macro that copies a sheet named Template and changes
the name according to the result of cell K2, and then deletes cell K2 in the
new sheet. K2 contains the formula...

=TEXT(TODAY(),"mm-dd-yyyy")&" ;
"&TEXT(YEAR(TODAY()),"yy")&TEXT(TODAY()-DATE(YEAR(TODAY()),1,0),"###")

it outputs something like "12-19-2005 ; 05353"

and the code is

Sub Copy_Template()
Sheets("Template").Copy Before:=Sheets("Template")
Sheets("Template (2)").Name = Cells(2, 11)
Range("K2").Select
Selection.ClearContents
End Sub

I am extremely new to VBA coding and created this code by editing a recorded
macro. I have three questions concerning this macro.
1. How can I include the K2 formula in the coding?
2. I created a custom button and added it to the toolbar. If the file is
not open, I want the button to do nothing, or bringup an error message
(something like "This button is unavailible). Is this possible, or something
equally effective?
3. For you programers out there, is there anything majorly wrong with this
macro? In works for now, but I don't want to accidently overwrite, or
delete, anything in the future.

Also, can anyone suggest a good book to learn VBA programming?

 

[Bob Phillips]

and the code is

Sub Copy_Template()
Sheets("Template").Copy Before:=Sheets("Template")
Sheets("Template (2)").Name = Cells(2, 11)
Range("K2").Select
Selection.ClearContents
End Sub

I am extremely new to VBA coding and created this code by editing a recorded
macro. I have three questions concerning this macro.
1. How can I include the K2 formula in the coding?

It already does. Cells(2,11) and Range("K2") is K2

2. I created a custom button and added it to the toolbar. If the file is
not open, I want the button to do nothing, or bringup an error message
(something like "This button is unavailible). Is this possible, or something
equally effective?

Could try this

Sub Copy_Template()
Dim sh as worksheet
Set sh = Sheets("Template")
If Not sh Is Nothing Then
sh .Copy Before:=sh
Activesheet.Name = Range("K2").Value
Range("K2").ClearContents
End If
End Sub

3. For you programers out there, is there anything majorly wrong with this
macro? In works for now, but I don't want to accidently overwrite, or
delete, anything in the future.

Should be okay now it uses explicit references.

Also, can anyone suggest a good book to learn VBA programming?

John Walkenbach's VBA For Dummies.

 

[Sloth]

Thanks for your reply.

1. I was wanting to use VBA coding to create the text string, instead of
pulling from K2. This would help if I need to change in the template I won't
have to change the code as well. It is not a big deal, but I wanted to know
if and how to do it in the macro. Sorry for being unclear.

2. I figured out I can put the code in the Module1 under the Personal.XLS
to keep the other file opening (the one with the template that is). But when
I push the button I get an error message. At this line saying the subscript
is out of range.

Set sh = Sheets("Template")

I tried rearanging things and this did not work either (same error, but at
the if statement).

Sub Copy_Template()
If Not Sheets("Template") Is Nothing Then
Dim sh As Worksheet
Set sh = Sheets("Template")
sh.Copy Before:=sh
ActiveSheet.Name = Range("K2").Value
Range("K2").ClearContents
End If
End Sub

 

[Dave Peterson]

#1. You could use something like:

Sheets("Template (2)").Name _
= Format(Date, "mm-dd-yyyy") _
& ";" & Format(Year(Date), "yy") _
& Format(Date - DateSerial(Year(Date), 1, 0), "000")


#2. Subscript out of range in this case means that the activeworkbook doesn't
have a worksheet named "Template". Are you sure you were looking at the correct
workbook?

Maybe something like this with some checks would work ok:

Option Explicit
Sub Copy_Template2()

Dim sh As Worksheet

Set sh = Nothing
On Error Resume Next
Set sh = Sheets("Template")
On Error GoTo 0

If sh Is Nothing Then
MsgBox "No sheet named Template in activeworkbook"
Else
sh.Copy Before:=sh
'activesheet is the sheet just copied.
ActiveSheet.Name = Format(Date, "mm-dd-yyyy") _
& ";" & Format(Year(Date), "yy") _
& Format(Date - DateSerial(Year(Date), 1, 0), "000")
End If
End Sub

 

[Sloth]

Thank you so much Dave. I tweeked the code a bit to suit my liking, and it
seems to be working fine. What is the "Option Explicit" and the "On Error
GoTo 0" for?

The following code works very well. It copies the template sheet, and does
nothing when there isn't one (which is exactly what I need). Sorry if I
confused you, but with the second part I was trying to adapt the macro to
other files, and didn't specify it. Thank you both for helping me.

Sub Copy_Template()
Dim sh As Worksheet
Set sh = Nothing
On Error Resume Next
Set sh = Sheets("Template")
If Not sh Is Nothing Then
sh.Copy Before:=sh
ActiveSheet.Name _
= Format(Date, "mm-dd-yyyy") _
& " ; " & Format(Year(Date), "yy") _
& Format(Date - DateSerial(Year(Date), 1, 0), "000")
End If
End Sub

 

[Dave Peterson]

Option Explicit
is a directive to excel that says that you will be declaring variables in this
module.

By declaring your variables, you get nice help from the VBE (especially
intellisense which helps you complete the next word.

Dim Wks as Worksheet
then type wks. (include the dot)
You'll see a list of all the properties and methods that you can use.

In fact, if you have a variable named:

SlothFlagForKeepingTrackOfSomethingImportant as boolean

You can type:
slothflag
and hit ctrl-space.

If you typed enough, the VBE will complete the typing for you. If you haven't
typed enough to make it unique, you'll see a list that you can choose from.

Also by declaring your variables, you can type in all upper/all lower and the
VBE will match your variable's case to the Dim statement. I find this a nice
way to check to see if I actually typed in the correct variable. If I see my
variable change case (to mixed), then I got it right.

And by telling the VBE that you will be declaring your variables, you won't be
able to even compile your code if you use a variable that isn't dimmed.

For example:

dim Al1 as long
all = al1 + 1

would run ok without the "option explicit" line--but it wouldn't do what I want.

By adding "option explicit", I'd get an error message that one of my variables
isn't declared. (There's an AL1 (A-L-(one) and A-L-L) in that code.)

=======

On error resume next
tells excel that something in the next portion could cause an error, but I don't
want it to help (by blowing up!). I expect and error sometimes and will look
for it later.

On error goto 0
tells excel to go back looking for errors--and do what it thinks is best
(Crashing if it's very bad!).

so

set sh = nothing
on error resume next
set sh = worksheets("Template")
on error goto 0

if sh is nothing then

is my way of seeing if my assignment to worksheets("template") was
successful--if it wasn't, then do something (Or don't do something).