# Replace2 using start and end rather than length

If you already know the start and ending character positions of text you want to replace, this bypasses the need to calculate the length. It just helps eliminate a step by not requiring you to calculate the length because this uses start_num and end_num directly as parameters. It can be used as an Excel function or a VBA function.

Public Function Replace2(ByVal old_text As String, _
ByVal start_num As Long, _
ByVal end_num As Long, _
ByVal new_text As String) As String
'Replaces old_text from character positions start_num to end_num with new_text;
'   does not delete any old_text characters when end_num < start_num.
'   e.g. when end_num is 0.

'Replace2 is designed to help when it's more convenient to code using start and
'   end positions rather than being forced to calculate a length.

'Replace2 can be used as an Excel or VBA function as an alternative to the
'   Worksheet.Replace function with the following design differences:
'      1. Replace2 uses a start_num and end_num pair rather than a start_num
'         with a length.
'      2. Replace2 is also more forgiving of parameter error, it's designed to
'         properly interpret and correct start_num and end_num value errors. E.g.
'         This ensures the start_num falls between 1 and old_text length + 1
'            and ensures the end_num falls between 0 and old_text length.
'      3. An end_num of 0 or end_num < start_num does not delete any old_text characters.
'      4. new_text is inserted just prior to the start_num position in the old_text.

Dim LenOldText As Long
LenOldText = Len(old_text)
If start_num < 1 Then start_num = 1
If end_num < 0 Then end_num = 0
If start_num > LenOldText Then start_num = LenOldText + 1
If end_num > LenOldText Then end_num = LenOldText
If end_num < start_num Then end_num = start_num - 1
Replace2 = Left(old_text, start_num - 1) & new_text & Right(old_text, LenOldText - end_num)
End Function

• This is working code. I created it for those who would prefer to use the Replace function with a start and end character position and not need to calculate length in their code. I usually know a start and end character position and so this eliminates a step in my coding. Apr 26, 2021 at 18:11
• My suggestion would be to move the new_text parameter up to the second spot, then make start_num and end_num as Optional (with a default value). With this arrangement, calling Replace2 would default to replacing the entire string, or beginning to a given end_num or from a given start_num to the end of the original string. I'm just thinking it may make the function a bit more versatile. Apr 27, 2021 at 15:03
• What is the purpose of If end_num < 0 Then end_num = 0? Seems if we set it to zero here, it will be overwritten by If end_num < start_num Then end_num = start_num - 1 since start_num is >=1. And the former statement has no impact on If end_num > LenOldText Then end_num = LenOldText between the two statements as negative and zero will never be greater than the length of the original text. Apr 27, 2021 at 17:44
• In this era of Dynamic Array functions, you might want to rework it to Spill when passed ranges of > 1 cell Jun 15, 2021 at 5:17