Capitalize string except for conjunction words
I think that you should be using ArrayLists for those conditional statements.

specifically you should store your conjunction words in an ArrayList something like this

Dim conjunctions
Set conjunctions = CreateObject("System.Collections.Arraylist")

conjunctions.Add("a")
conjunctions.Add("an")
conjunctions.Add("and")
conjunctions.Add("as")
conjunctions.Add("at")
conjunctions.Add("by")
' Etc ......

for each item in xTextSplit
    xWord = item
    if conjunctions.contains(lcase(item))
        xWord = lcase(item)
    elseif suffixes.contains(lcase(item))
          xWord = ucase(item)
    end if
    xCompleteWord = xCompleteWord &amp; " " &amp; xWord
next


suffixes would be another ArrayList.  this could be used with the method that Vogel612 suggests.  

creating an ArrayList is a better way to perform this task because now you can add more words to the conjunction ArrayList and not have to worry if you found all the instances of it in the code.  It is more Maintainable.

NOTE: I haven't tested this code



 I think that you should be using Dictionary's for those conditional statements.

specifically you should store your conjunction words in a dictionary something like this

Dim conjunctions   ' Create a variable.
Set conjunctions = CreateObject("Scripting.Dictionary")

conjunctions.Add(1,"a")
conjunctions.Add(2,"an")
conjunctions.Add(3,"and")
conjunctions.Add(4, "as")
conjunctions.Add(5, "at")
conjunctions.Add(6, "by")
' Etc ......

for each item in xTextSplit
    xWord = item
    if conjunctions.Exist(lcase(item))
        xWord = lcase(item)
    elseif suffixes.Exist(lcase(item))
          xWord = ucase(item)
    end if
    xCompleteWord = xCompleteWord &amp; " " &amp; xWord
next


suffixes would be another dictionary.  this could be used with the method that Vogel612 suggests.