violating any best practices etc? 1

[imarosel]

previously posted in the wrong forum.

Sub BackupByDate()
Dim dname As String, strTest As String
dname = ThisWorkbook.Path & "\B" & Format(Now(), "yyyy_mmdd")
strTest = Dir(dname, vbDirectory)
If (strTest = "") Then MkDir (dname)
ActiveWorkbook.SaveCopyAs dname & "\BK_" & Format(Now(),"hh") & "." & Format(Now(),"mm") & "." & Format(Now(), "ss") & "_" & ActiveWorkbook.Name
ActiveWorkbook.Save 'also save current file
End Sub

slightly modified from what I found here:

http://www.mvps.org/dmcritchie/excel/excel.htm

 

[fumei]

I am not sure what it is you are asking, since you do not appear to be asking anything.

I have qualms regarding the use of dots (".") within the filename though.

faq219-2884

Gerry
My paintings and sculpture

 

[imarosel]

Sorry, thought the subject line made it clear. Thought somebody might be able to look at this small subroutine and tell me if I'm violating any best practices.

 

[xlbo]

best practice is a matter of opinion but as Gerry says, dots in filenames tend to be a no no and I would have concerns about using ACTIVEworkbook - use THISworkbook instead where possible

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[imarosel]

allright that is a good tip, no dots in the filename.
Why is that? Underscores good to go?

 

[fumei]

Underscores are commonly used. Why no dots? It is not so much a concern now, as applications are a bit more forgiving, but dots are (still) used by (some) OS file systems to indicate the name vs extension. With (some) applications still using that name/extension split in the background.

I am wondering about the length of the name, and the purpose to include the folder path as part of it. In Word, I used your code on a file: C:\MyFiles\Test\MyFileName.doc

Here is the result, a file with the filename of:

C:\MyFiles\Test\B2007_1120\BK_08.11.59_MyFileName.doc

Seems overly convoluted to me. Does it violate best practices? Hard to say, as best practices are, as Geoff mentions, a matter of opinion.

Why do you need hours/minutes/seconds?



faq219-2884

Gerry
My paintings and sculpture

 

[imarosel]

This is code that is called before a macro is run that saves the file into a backup folder so in case the macro junks things up the data is not lost. I put the seconds on because when testing it I didn't want to wait a minute to make sure it incremented on files.

Thanks for the tips, I've learned to do VB in excel by recording macros and looking at my code. I've learned through perusing the internet and such that this has given me some bad habits (e.g. selecting a sheet before I transform some data on it, or selecting every cell before I do something). I have a little breathing room at work right now so I'm trying to reform some of those habits, that is why I have a random sub posted here asking for people to tell me if they see any bad practices in there.

Thanks again.

 

[fumei]

You are welcome. I commend you on the asking.

faq219-2884

Gerry
My paintings and sculpture

 

[xlbo]

ditto that

general best practice for excel vba

Don't select
Don't activate
don't use ACTIVE anything
Refer to objects and use the methods associated with them
Use WITH / END WITH construct and indenting to make code readable and faster to type
Don't loop unnecessarily - many things can be accomplished without loops
Learn about all the features of excel so that you don;t have to program around functionality that already exists
Declare ALL variables and use Option Explicit

That'll do for now

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fumei]

All very good suggestions! And not just for Excel.

My top four (for what it is worth), in order, of the list would be:

Refer to objects and use the methods associated with them

Use WITH / END WITH construct and indenting to make code readable and faster to type

Don't select

Declare ALL variables and use Option Explicit

faq219-2884

Gerry
My paintings and sculpture

 

[HughLerwill]

Could be the routine is not used on new Workbooks, however, if ThisWorkbook has not been saved before its .Path will be "". Does that matter.

 

[imarosel]

I didn't think about that, good point, thanks.

 

[fumei]

Maybe we should add knowing how to use Watch and other debugging tools to best practices!

That, and solid basic error trapping. It is indeed a good point regarding .Path possibly = "".

If ThisWorkbook.Saved = False Then

would do a top level error checking for that.

faq219-2884

Gerry
My paintings and sculpture