Table of Contents

A Common Mistake when Dimensioning Variables
Backup Folders using VB

A Common Mistake when Dimensioning Variables

One common mistake people make when dimensioning variables is to put more than one variable on the same line - but they do it incorrectly. You might think that the following example dimensions the variables FromPath and ToPath both as String Variables. But actually this is not the correct way to do it, and it does not give you the result you would otherwise expect.

Sub MyCode()
   Dim FromPath, ToPath As String
   ...
	 
End Sub
		 

You have to tell VBA the type of each variable, even if they are on the same line. So the way that VBA interprets the code above is actually as follows.

Sub MyCode()
   Dim FromPath
   Dim ToPath As String
   ...
	 
End Sub
		 

So what actually is happening is that FromPath is being set as a Variant, and ToPath is being set as a String. Now of course your code will still work, and you may never realise that you have done this. But if you are going to bother to dimension your variables, you may as well do it correctly.

The correct way to dimension more than one variable on a line is as follows.

Sub MyCode()
   Dim FromPath as String, ToPath As String
   ...
	 
End Sub
		 

back to top

Backup Folders using VBA

It is possible to use VBA to create backups of your folders by utilising the file system object (FSO). The FSO allows you to manipulate folders and files on your PC. In this example, the FSO is used to copy an entire folder from a source folder to a backup folder. Just change the from path and to path in the code below.

Sub Backup_Folder()
    Dim FSO As Object
    Dim FromPath As String, ToPath As String
    Dim FromFolder As String

    FromPath = "C:\SourceFolder"
    FromFolder = Right(FromPath, Len(FromPath) - 3)
    ToPath = "C:\BackupFolder"
    Set FSO = CreateObject("scripting.filesystemobject")
    
    If FSO.FolderExists(FromPath) = False Then
        MsgBox FromPath & " doesn't exist"
        Exit Sub
    End If

    ToPath = ToPath & "\" & FromFolder & " " & Format(Date, "yyyy-mm-dd")
    FSO.CopyFolder Source:=FromPath, Destination:=ToPath
    MsgBox "Backup of folder " & FromPath & " is created in " & ToPath

    Set FSO = Nothing
End Sub

Note the last line of code Set FSO=Nothing. When ever you use the set command to assign an object to a variable in your code, you should always finish your routine with this line of code for every variable you set. Taking this step will release the object from memory, and return the space it was allocating back to the freely available memory.

back to top