asked on

Excel VBA: add specific string v2 (properly exit sub)

Hello experts,

I have the following procedure used for adding specific character in a column at the end or at the beginning of a string.

Sub Add_Specific_Char()
Dim rng As Range
Dim strSpecificChar As String
Dim strCol As String
Dim lngLastRow As Long
Dim lngRow As Long
Dim intWhich As Integer

    intWhich = InputBox("Please report value related to the action that you want to perform: 1 for adding string at the beginning 2 at the end")
    Select Case intWhich
        Case 1, 2
        Case Else
            MsgBox "Please enter '1' or '2'"
            Exit Sub
    End Select
            
    strCol = InputBox("Please report column letter in which you want to apply procedure", "Choose Column Letter")
    Set rng = Application.InputBox("Please select the cell in which is reported value that you want to add", "Select cell", Type:=8)

    lngLastRow = Range(strCol & Rows.Count).End(xlUp).Row


    If Not rng Is Nothing Then
    
        strSpecificChar = rng
        
        For lngRow = 1 To lngLastRow
            Select Case intWhich
                Case 1
                    Cells(lngRow, strCol).Value = strSpecificChar & Cells(lngRow, strCol).Value
                Case 2
                    Cells(lngRow, strCol).Value = Cells(lngRow, strCol).Value & strSpecificChar
            End Select
        Next

    End If
    
End Sub

Open in new window

I would like to revise it with the following requirements:

1-Properly exit sub when user click on cancel for the various inputbox set up in the procedure.

If you have questions, please contact me.

Thank you very much for your help.

ASKER CERTIFIED SOLUTION

Kimputer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fabrice Lambert

(Don't forget variables declaration).

Additional notes:
You function (or procedure) break the SRP and should be split into 3:
One prompting for input.
One performing the requested action (happend or prepend)
One calling the 2 above.

The smaller the functions (thus the fewer responsibilities), they easyer they are to debug and maintain.

Luis Diaz

ASKER

Thank you very much unable to test it right now. I will test it this week.
@Fabrice Lambert: thank you for your advice. Could you please remember us the SRP convention?

Fabrice Lambert

Could you please remember us the SRP convention?

It isn't a convention, but a good practice.

A function, procedure or class, should have only one and only one responsibility.
This way it is short, easy to debug, easy to mainain, easy to evolve.

Luis Diaz

ASKER

@Fabrice: thank you very much for your help. This means that I need to create individual procedures and then call them in a compiled procedure:

Example
Sub FlagActions()
'-Flags in order to distinguish the various action
End Sub

Sub InputBox()
'-InputBox to select the reference column to apply the procedure
End Sub

Sub AddSpecificString()
'Porcedure to add specific string at the first or end position
End Sub

Thank you in advance for your advice.

Luis Diaz

ASKER

@Kimcomputer: I tested and it works. I just realized that string should not be just declare as string but it can also be numeric what should I modified to cover string and also numeric reported in Range to add?

Thank you in advance for your help.

Fabrice Lambert

Variant type support any data type.
Use it with extreme caution.

Luis Diaz

ASKER

Thank you for this feedback Fabrice.
What is the risk of variable declaration could you please provide an example.
Thank you again!

Fabrice Lambert

The risk is to perform illegal operation on incorrect data type.

Sample code:

Private Sub Substract(left as variant, right as variant)
    Debug.Print left - right
End Sub

Public Sub Main()
    Substract 10, 3             '// Ok, arguments are integers
    Substract "10", "3"         '// Ok, arguments are strings and implicitly convertible to a numeric type
    Substract 10.5, 3.2         '// Ok, arguments are double
    Substract "10.5", "3.2"     '// Ko, arguments are strings and not convertible to any numeric type
    Substract "abc", "def"      '// Ko, arguments are string
End Sub

Open in new window

VBA provide funcionalities to check data types, but checking data type is usually a sign of design flaw.

Luis Diaz

ASKER

Got it, thank you for your feedback.