Sorry about the code post issue, Think I got it right this time. So, I see where it puts a call in for ConvExceptions. Tried to call ProperNames as a public function from a module, but got an error "Not Allowed'. To run this, does the search portion of my initial code need to be removed or replaced and simply run the ConvExceptions "True" portion of the If/Then?
Below is how I have the code set up in Module 1 for a public function
Code:
Public Function ConvExceptions(StringIn As String) As String
' Will find exceptions to Proper Case capitalization of names.
On Error Resume Next
If DCount("*", "tblExceptions", "[ExceptName] = " & Chr(34) & StringIn & Chr(34) & "") > 0 Then
Dim intResponse As Integer
Dim strFind As String
strFind = DLookup("[ExceptName]", "tblExceptions", "[ExceptName] = " & Chr(34) & StringIn & Chr(34) & "")
intResponse = MsgBox(strFind & vbCrLf & " is an exception name." & vbCrLf & " Accept the above capitalization? Y/N ?", vbYesNo, "Exception found!")
If intResponse = vbYes Then
ConvExceptions = strFind
Exit Function
End If
End If
ConvExceptions = StrConv(StringIn, 3)
End Function
Originally Posted by
Ajax
for future reference, please use the code tags to preserve indentation and readability of code - highlight the code and click the # button
but to answer your question you would need to split the string into its separate words
in vba something like
Code:
function properNames(s as string) as string
dim a() as string
dim i as integer
'Get the case for each word
set a=split(s," ")
for i=0 to ubound(a)-1
a(i)=ConvExceptions(a(i))
next i
'stitch the name back together
for i=0 to ubound(a)-1
propernames=propernames & " " & a(i)
next i
'remove initial space
propernames=mid(propernames,2)
end function