Field Calculator VBA ArcGIS 9.3 - how to replace the last three characters of a string?

4440
14
Jump to solution
09-22-2014 08:32 AM
StephenDrew1
New Contributor III

Hello, I'm a newbie to VBA Script, so any of the below script could be wrong. I'm trying to use Field Calculator to replace any "_10" at the end of a filename (i.e. an entry in a field, in an attribute table) with "_11". Could anyone help me please? Many thanks in advance! sdrew1

 

Pre-logic VBA Script Code

Dim MyStr as string

Dim strLen as integer

 

strLen = Len([FILENAME])

 

If Right([FILENAME],3) = "_10" Then

MyStr = Left([FILENAME], strLen-3)) & "_11"

Else

MyStr = [FILENAME]

End if

 

Filename =

MyStr

1 Solution

Accepted Solutions
PeteCrosier
Occasional Contributor III

I think 9.3 should support Python if you run the Field Calculator geoprocessing tool, otherwise for VBA this should work:

Dim length

Dim result

length = Len([Filename])

If length > 2 Then

  result = Left([Filename], length - 3) & Replace(Right([Filename], 3), "_10", "_11")

Else

  result = [Filename]

End If

---

Filename =

result

View solution in original post

14 Replies
TMgapinski
New Contributor

you'd have to give me more information, have you tried cut and paste?! Repeat the string?

0 Kudos
StephenDrew1
New Contributor III

There are a lot of them in the database. E.g. the FileName field is populated with record entries such as "New_Town_Planning_Project_10" and I want to make all the thousends of records entries be "New_Town_Planning_Project_11".

0 Kudos
KenBuja
MVP Honored Contributor

You shouldn't declare the variables as a type. Try

Dim MyStr

Dim strLen

0 Kudos
StephenDrew1
New Contributor III

Thanks Ken, for the help. It sadly still doesn't work (I get an error message that sayd 'Error Running VBA code: User interrupt.'). If you or anyone has any other ideas I would be a very grateful recipient! Steve

0 Kudos
PeteCrosier
Occasional Contributor III

How about a Python one-liner?

Filename = !Filename![:-3] + !Filename![-3:].replace("_10", "_11")

0 Kudos
StephenDrew1
New Contributor III

Hi Pete, thanks for the help but it's Arc 9.3 so only works in VBA - unless I'm missing something - I'm not used to Python (either). Thanks for trying to help - any clue about VBA would be very grateful!?

0 Kudos
PeteCrosier
Occasional Contributor III

I think 9.3 should support Python if you run the Field Calculator geoprocessing tool, otherwise for VBA this should work:

Dim length

Dim result

length = Len([Filename])

If length > 2 Then

  result = Left([Filename], length - 3) & Replace(Right([Filename], 3), "_10", "_11")

Else

  result = [Filename]

End If

---

Filename =

result

StephenDrew1
New Contributor III

That's great Pete, thanks very much! That works brilliantly. And I think I'm learning - I can actually work out why you've written it as you have. The only bit I don't know about is why you might have written 'if the length is greater than 2'? Is that in case there are default blank fields? (If you don't want to answer this that's ok - I'm just learning and can't find links on the net to teach me about it...). Cheers Pete. Steve

0 Kudos
PeteCrosier
Occasional Contributor III

Yeah, in case there's a string that's blank or too short for the Left and Right to work properly.

As Joe said, you can probably use the Replace function better if it supports the 'normal':

Replace(string, find, replace[, start[, count[, compare]]])

.. VBA format in field calculator.