Title Case 1

[Joeclueless]

Is there a way to use an expression in VBA to change CAPS to Title Case?

I have the same issue as thread219-915762 although in VBA environment...


I'm trying to find something like this:

LCase ([NAME])

I've tried this:

Left( [NAME_ALF] , 1) & LCASE (Mid([NAME_ALF] ,2,20))

But that doesnt allow for multiple words??

Any Ideas??


Thanks again!

Joe

 

[SkipVought]

Hi,

try this

function TitleCase(s)
a = split(s, " ")

for i = 0 to ubound(a)
  TitleCase = TitleCase & UCase(left(a(i), 1)) & LCase(right(a(i), len(ai)-1)) & " "
next
TitleCase = left(TitleCase, len(TitleCase)-1)
end function

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[Remou]

Hi
Or maybe: StrConv("string string etc", vbProperCase)
It is not very good, though as words such as 'of' are capitalised. If you are fussy about this kind of thing, and I am, it takes a fair bit of code to get it right.

 

[TonyJollans]

Hi Joeclueless,

You can also use the methods of the parent app - what are you runniing in?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[fumei]

If it is in Word, and you can make a Selection of it:

Selection.Range.Case = wdTitleWord

will make the selection Title Case. Although it will make it Title Case, so things like "OF" will become "Of". But then, that IS Title Case.

If you passing a string that is already in CAPS, simply insert it, reselect it, and use the above.

Gerry

 

[Joeclueless]

The parent app is ArcGIS, ArcMap 9.0.

I am adding the bits of code into the label expression form.

This is how the app likes to see the code:

______________________________________________

Function FindLabel ( [LABEL_FIELD] )
FindLabel = [LABEL_FIELD]
End Function

______________________________________________


So if I were to try to change the display of the string, I would enter:
______________________________________________

Function FindLabel ( [NAME_ALF] )
FindLabel = Left( [NAME_ALF] , 1) & LCASE (Mid([NAME_ALF] ,2,20))

End Function

______________________________________________

If I want to try Skip's tip, then I need to declare those variables right? But I'm not sure how to put that in when I already have the FindLabel function....



Thanks again!

Joe

 

[Joeclueless]

I'm trying Remou's solution here and it looks like this:

________________________________________________________

Function FindLabel ( [Label_Field] )
FindLabel = StrConv( [Label_Field], vbProperCase)

End Function

________________________________________________________

But I get an error:

"Expression contains an error. Modify the expression and try again. Error 13 on line 2. Type mismatch: 'StrConv'.

Could anyone help on that??

Thanks again!

Joe

 

[Joeclueless]

Hey now...

Is this post in the wrong forum?? Or is my question to dumb?? Or is the answer already here?

I'm not sure if people are avoiding me or not?

Any hints?

Thanks again!

Joe

 

[TonyJollans]

Hi Joe,

Nobody's avoiding you! I think the problem is that few people here know anything about ArcGIS - I certainly don't.

The syntax you post looks odd to me (I don't understand the use of the square brackets) but that doesn't mean it's wrong - it just means it's not what I know.

There are a couple of answers in the thread - do they not work for you? What about Skip's in the first reply? It extends your original suggestion to multiple words - does it not work?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Joeclueless]

Thanks Tony,

The square brackets refer to a field in a table.

I think Skip's post is what I need to use, but I think the FindLabel function needs to be called so, In my earlier post, I was asking if anyone would know how to use Skip's TitleCase function in conjunction with the parent app's FindLabel function....

I kinda don't know what I'm talking about... But that's why I'm clueless.....

Thanks again!

Joe

 

[TonyJollans]

Hi Joe,

OK, I've done a bit of reading and AcrGIS uses VBScript (which is not the same as VBA) inside its own structure. I don't pretend to understand it all but it seems that what you are doing is about right. BUT, VBScript does not have StrConv.

So, on to Skip's code. VBScript does have the Split function so I think you should be able to use Skip's code like this:

Function FindLabel ( [Label_Field] )
    a = split( [Label_Field] , " ")

    for i = 0 to ubound(a)
        TitleCase = TitleCase & UCase(left(a(i), 1)) & LCase(right(a(i), len(ai)-1)) & " "
    next
    FindLabel = left(TitleCase, len(TitleCase)-1)
End Function

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Joeclueless]

Tony,

I appreciate that!

Sorry, I guess this is not in the right forum...

I was uder the impression that ArcGIS 9 used VBA-

Anyways, I am still getting an error... I tried the code you posted:

Function FindLabel ( [Label_Field] )
    a = split( [Label_Field] , " ")

    for i = 0 to ubound(a)
        TitleCase = TitleCase & UCase(left(a(i), 1)) & LCase(right(a(i), len(ai)-1)) & " "
    next
    FindLabel = left(TitleCase, len(TitleCase)-1)
End Function

And this error came up:
_______________________________________________
Error 5 on line 5.
Invalid procedure call or argument: 'right'.
_______________________________________________

It seems funny to me that no error was given to 'left' which was first??

Any Ideas?




Thanks again!

Joe

 

[TonyJollans]

Hi Joe,

Sorry for the delay - I've been away all week.

Right is a valid funcion in VBScript so should work but, on close inspection, I notice a typo in the code I cut and pasted ..

[purple] ... LCase(right(a(i), len(ai)-1)) & " "[/purple]

Try correcting it

[blue] ... LCase(right(a(i), len(a[highlight]([/highlight]i[highlight])[/highlight])-1)) & " "[/blue]

and see if that helps. If not, I'm not sure where to go next - you could use a Mid function (Mid(a(i),2) might be good enough) instead of Right.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Joeclueless]

Thanks Tony,

That works like a charm!!

Awesome!!!!!!!!!!!!!!!!!
Have a STAR!


Thanks again!

Joe

 

[TonyJollans]

Thanks, Joe,

My pleasure!

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Joeclueless]

Hey there folks,

I'm now trying to qualify on a type field to include records to include in the FindLabel function....

Function FindLabel ( [NAMEA-ALF], [TYPE] )
    if ( [TYPE] <> 2000, 2100, 2200, 5500, 5600) 
    FindLabel [NAMEA-ALF]
    end if
    a = split( [NAMEA-ALF]  , " ")

    for i = 0 to ubound(a)
        TitleCase = TitleCase & UCase(left(a(i), 1)) & LCase(right(a(i), len(a(i))-1)) & " "
    next
    FindLabel = left(TitleCase, len(TitleCase)-1)
End Function

I'm getting an error, does this code look reasonable?

Thanks again!

Joe

 

[PHV]

Something like this ?
Function FindLabel([NAMEA-ALF], [TYPE])
If [TYPE] <> 2000 And [TYPE] <> 2100 And [TYPE] <> 2200 And [TYPE] <> 5500 And [TYPE] <> 5600 Then
FindLabel = [NAMEA-ALF]
Exit Function
End If
a = Split([NAMEA-ALF], " ")
For i = 0 To Ubound(a)
TitleCase = " " & TitleCase & UCase(Left(a(i), 1)) & LCase(Mid(a(i), 2))
Next
FindLabel = Mid(TitleCase, 2)
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244