VBA Modules: I've started a new blog

EDIT: left the background info up but have moved all my code posts into a new separate code blog

http://baldywrittencod.blogspot.com


Original post and info follows:

As a bit of personal background, I've been developing in VBA automation since 2009, with about 3 years of moderate Excel & Access VBA development behind that, so the majority of the code is designed with Excel automation in mind, but does make use of Access and Outlook VBA where needed.  I've never had to automate Powerpoint or other MS apps but I'm sure I will eventually.

I work for a globocorp, and all the network addresses you will see here are completely inaccessible to anyone outside the company, so just change them for your own file locations.

Please note that I make no claims to copyright as all my code is open source, largely stolen, copied, or adapted from other snippets of code I've found via Google, usually with the original source link included for reference and polite attribution.  I've never had any formal VBA training, so this is all picked up from the interweb, and adapted for my own needs, using my own fair hands.  Feel free to steal, copy or adapt my code for your own needs -- that's what I did!

The modules I'll post here are interlinked (so you'd need to "install" the whole series to get full functionality and stop errors with Debug > Compile) but they do largely work as standalone modules.  I've annotated which modules are dependent on which, so it's easy to see that for yourself.

I've been loathe to post these modules online because they're never, ever "finished", as I keep adding new features as and when I need them.  But, in a stroke of momentary genius a while ago, I started version-numbering the macros to keep track of the various changes, and I've recently started keeping a Version History in all modules, so I reckon I can now keep my blog up-to-date with changes.  (We'll soon see!)  Plus, after migrating everything to a new server with Office 2010, I think I'm finally running out of necessary improvements to make.  It does just about everything I need it to.

However, I must be clear about this: this code is not for the faint-hearted.  I don't always put syntax or other descriptive details in the macros, so you'll need a fair bit of VBA knowledge under your belt to make sense of some of it.  But it should at least work... feel free to just try it out, and ask me questions if you're not sure about how anything works or what it does -- I'll improve the code annotations as I go along -- but if you're a VBA n00b, there are countless Excel / VBA forums with hundreds of members where you can get basic support, just as I did.  VBA has a pretty steep learning curve if you don't have the time to develop your skills gradually.  I had 5 years to develop mine.  Another guy I saw asking a complete n00b question on a forum ended up with his profile being marked as "Excel VBA MVP" within 3 years.  So before you jump from the high dive board, I'd strongly suggest you either pay for a professional VBA course, hire a spod to do it, or just think again and start in the shallow end.

First module here:
http://baldywrittencod.blogspot.com/2013/08/vba-modules-modzip-v502aaa.html

VBA Macro to remove special characters from path and/or filename and/or VBA object name

Solution originally posted here:
http://stackoverflow.com/a/14157011/1540567 

This VBA function removes special characters from a string.  In my case I wanted a single function to return a valid path and/or filename and/or VBAProject name.  It works with both URL and UNC paths (and tries to clean up any paths with mixed slashes).

It's easy enough to tweak this for other purposes.  You can specify additional "forbidden" characters easily and add any extra boolean switches for your own specific needs, or you could just split into separate functions.

The function also checks the maximum string length and either crops or pops up a message box if a filename (not path) exceeds 128 characters -- very useful for SharePoint uploads -- or a VBA object name exceeds 35 characters.

Function fn_Clean_Special(str As String, CropLength As Boolean _
    , Optional VBObjectName As Boolean) As String
'v1.03 2013-01-04 15:54
'removes invalid special characters from path/file string
', True stops message box warnings and autocrops string
'     [, True] also removes spaces and hyphens and periods (VBA object)
'~ " # % & * : < > ? { | } ..   / \   -

Dim b As Integer, c As Integer, pp As String
Const tt As String = "fn_Clean_Special"
Dim sc(0 To 18) As String
sc(0) = "~"
sc(1) = Chr(34)  ' Chr(34) = " quotemark
sc(2) = "#"
sc(3) = "%"
sc(4) = "&"
sc(5) = "*"
sc(6) = ":"
sc(7) = "<"
sc(8) = ">"
sc(9) = "?"
sc(10) = "{"
sc(11) = "|"
sc(12) = "}"
sc(13) = ".."
'slashes for filenames and VB Object names
sc(14) = "/"
sc(15) = "\"
'hyphen & space & period for VB Object names
sc(16) = "-"
sc(17) = " "
sc(18) = "."

'remove special characters from all
For b = 0 To 13
    str = Replace(str, sc(b), vbNullString)
Next b

'check filename length (length AFTER the LAST slash max 128 chars)
b = InStr(1, str, sc(14))  'look for fwd slash
If b > 0 Then
    str = Replace(str, sc(15), sc(14))  'remove all back slashes
    Do Until b = 0  'until last slash found
        c = b       'c is position of last slash
        b = b + 1                   'next position
        b = InStr(b, str, sc(14))   'next position
    Loop
Else  'no fwd slashes
    b = InStr(1, str, sc(15))  'look for back slash
    If b > 0 Then
        str = Replace(str, sc(14), sc(15))  'remove all fwd slashes
        Do Until b = 0  'until last slash found
            c = b       'c is position of last slash
            b = b + 1                   'next position
            b = InStr(b, str, sc(15))   'next position
        Loop
    End If
End If
'c is position of last slash, or 0 if no slashes
If Len(str) - c > 128 Then
    If CropLength = True Then
        str = Left(str, 35)
    Else
        pp = "WARNING: filename > 128 chars"
        MsgBox pp, vbCritical, tt
    End If
End If

'remove slashes from filenames only
If c > 0 Then
    For b = 14 To 15
        str = Left(str, c) & Replace(Right(str, Len(str) - c), sc(b), vbNullString)
    Next b
End If


If VBObjectName = True Then
'remove slashes and swap hyphens & spaces & periods for underscore in VB object name
    Const scUS As String = "_"
    For b = 14 To 18
        str = Replace(str, sc(b), scUS)
    Next b
'then remove invalid characters from start of string
    Dim c1 As String
    c1 = Left(str, 1)
    Do While c1 = scUS Or c1 = sc(18) Or IsNumeric(c1)
        str = Right(str, Len(str) - 1)
        c1 = Left(str, 1)
    Loop
'remove double underscore
    Do While InStr(str, scUS & scUS) > 0
        str = Replace(str, scUS & scUS, scUS)
    Loop
    'check object name length (max 35 chars)
    If Len(str) > 35 Then
        If CropLength = True Then
            str = Left(str, 35)
        Else
            pp = "WARNING: object name > 35 chars"
            MsgBox pp, vbCritical, tt
        End If
    End If
End If

fn_Clean_Special = str

End Function

Debug Window results:

?fn_clean_special("\\server\path\filename.xls", True)
\\server\path\filename.xls
   
?fn_clean_special("\\server\path\filename.xls", True, True)
server_path_filename_xls

?fn_Clean_Special("\\special character\testing   for \VBproject.xls", True, True)
special_character_testing_for_VBpro