Best way to build strings: one variable or multiple????

[AlexB123]

Hi all,

I have a programming question, and I'm not sure what the concept I'm referring to is called. In the databases I work in, as well as in many spreadsheets, there is a constant need to archive documents in a folder tree by year, by month, and often by even finer subsets.

In the original code, the developer would use the same variable name as they checked for the existence of folders, and created them, and built the strings.

dateStamp = (Format(Year(Now()), YYYY))
    BaseDir = "C:\Alexb123\Current DB\XMEN\"
    ArchiveDir = BaseDir & "Archive\"
    
  If Len(Dir(BaseDir & "MOVED TO GROUP\", vbDirectory)) = 0 Then
        MkDir (BaseDir & "MOVED TO GROUP\")
        End If
    If Len(Dir(BaseDir & "MOVED TO GROUP\Moved To Group Requests\", vbDirectory)) = 0 Then
        MkDir (BaseDir & "MOVED TO GROUP\Moved To Group Requests\")
        End If
BaseDir = "C:\Alexb123\Current DB\XMEN\MOVED TO GROUP\Moved To Group Requests\"
    If Len(Dir(BaseDir & "Archive\", vbDirectory)) = 0 Then
        MkDir (BaseDir & "Archive\")
        End If
    If Len(Dir(BaseDir & "Archive\" & Format(Year(Now()), YYYY), vbDirectory)) = 0 Then
        MkDir (BaseDir & "Archive\" & Format(Year(Now()), YYYY))
        End If
    If Len(Dir(BaseDir & "Archive\" & Format(Year(Now()), YYYY) & "\" & MonthName(Month(Now())) & "\", vbDirectory)) = 0 Then
        MkDir (BaseDir & "Archive\" & Format(Year(Now()), YYYY) & "\" & MonthName(Month(Now())) & "\")
        End If
ArchiveDir = BaseDir & "Archive\" & Format(Year(Now()), YYYY) & "\" & MonthName(Month(Now())) & "\"

I've cleaned up the code, and written a function to call instead of repeated calls to mkdir. In the one programming class I ever took, my takeaway was that it is better to declare a new variable for everything instead of using single or generic variables throughout code. So I did the following:

Dim dateStamp As String: dateStamp = (Format(Year(Now()), YYYY))
    
    Dim PathDir1 As String: PathDir1 = GetBaseDir() & "MOVED TO GROUP\"
    Dim PathDir2 As String: PathDir2 = PathDir1 & "Moved To Group Requests\"
    Dim ArchiveDir1 As String: ArchiveDir1 = PathDir2 & "Archive\"
    Dim ArchiveDir2 As String: ArchiveDir2 = ArchiveDir1 & dateStamp & "\"
    Dim ArchiveDir3 As String: ArchiveDir3 = ArchiveDir2 & MonthName(Month(Now())) & "\"
    
        Call CheckForPaths(ArchiveDir2, ArchiveDir3)

In this case, using different variables makes sense ... I would have to call CheckForPaths repeatedly. But is there ever a reason to build a string using only a single variable name?

 

[welshgasman]

I would probably only have separate variables if they were needed by themselves as well.
So where you use Year and MonthName functions, I would just use as needed.
The only reason I would have separate variables would be for the likes of

Format(Year(Now()), YYYY) & "\" & MonthName(Month(Now())

so that if that extension changes for some reason and is used a lot of times in the code, changing it once at the top saves a lot of time, even with Find and Replace

I do however use separate variables if I have something reasonably complicated to calculate, to show the steps, rather than one huge line of functions concatenated together.

Everyone has their own style.