VBA Excel Characters and Spaces Concatenating Multiple Strings

Question

I need to create a Error Code String where certain error characters occupy certain positions in a string.

Say you have Code F and it must be in position 2.

Output: _F

Now if we have Code G which is in position 5.

Output: ____G

Now what if in an instance I have TWO errors? How can I output this string:

_F__G

User Error Prompt:

Prompt

My Code:

ErrorCode = Space(CodeNumber.Value) + CodeCharacter.Value

Is there an easy way to do this? I understand I can do this for the first code adn then for the second take the earlier number (2) and subtract it from (5) and take 1 away and then space and add character. But that seems messy and difficult when we have multiple codes and spaces.

What happens when I add two strings in VBA? Say "___A" + "_B" I assume its "___A_B".

Is there anyway to get "_B_A?"

Thanks!

------------------------After trying ideas from both answers--------------------

I get this which ONLY returns ONE Error Code:

Private Sub ExportError_Click()
    Dim myErr As String
    myErr = ""

Dim myFile As String
myFile = "C:\Reformatted.txt"
Open myFile For Output As #1

    If F_2 = True Then
    AddCode "F", 2, myErr
    ElseIf G_3 = True Then
    AddCode "G", 3, myErr
    ElseIf H_4 = True Then
    AddCode "H", 4, myErr
    End If

Print #1, myErr

Close #1
Shell "C:\Windows\Notepad.exe C:\Reformatted.txt", 1

End Sub

Function AddCode(Lett As String, Pos As Long, ByRef ErrString As String)
    If Pos > Len(ErrString) Then
        ErrString = ErrString & Space(Pos - Len(ErrString))
    End If
    Mid(ErrString, Pos, 1) = Lett
End Function

Private Sub UserForm_Click()

End Sub

It's not clear from your question exactly how you will need to combine the different codes (do they come from different sources) or how you determine which character needs to go in which position. — Tim Williams
– Tim Williams, Commented Jun 26, 2015 at 20:25
@TimWilliams Good Question! I clarified it a little further. Its from Textboxes. — Dasman
– Dasman, Commented Jun 26, 2015 at 20:50

Tim Williams · Accepted Answer · 2015-06-26 23:18:39Z

4

Sub Tester()
    Dim myErr As String

    AddCode "F", 2, myErr
    AddCode "G", 5, myErr
    AddCode "A", 1, myErr

    Debug.Print myErr
End Sub

Function AddCode(Lett As String, Pos As Long, ByRef ErrString As String)
    If Pos > Len(ErrString) Then
        ErrString = ErrString & Space(Pos - Len(ErrString))
    End If
    Mid(ErrString, Pos, 1) = Lett
End Function

From your example:

Private Sub ExportError_Click()
    Dim myErr As String
    myErr = ""

    Dim myFile As String
    myFile = "C:\Reformatted.txt"
    Open myFile For Output As #1

    If F_2 = True Then AddCode "F", 2, myErr
    If G_3 = True Then AddCode "G", 3, myErr
    If H_4 = True Then AddCode "H", 4, myErr

    Print #1, myErr

    Close #1
    Shell "C:\Windows\Notepad.exe C:\Reformatted.txt", 1

End Sub

edited Jun 26, 2015 at 23:18

answered Jun 26, 2015 at 20:31

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dasman Over a year ago

Why is it "Debug.print?"

Dasman Over a year ago

myErr wont accept two errors. I got it to accept 1. Let me know if you have any tips.

Dasman Over a year ago

Hey Tim this is good to go. One of the code has a position of 0 (First letter is the Code. How can I make sure to not have an error with that? Error is with: "Mid(ErrString, Pos, 1) = Lett" But since Pos would be 0 I assume it wont work?

Dasman Over a year ago

Nvm I solved the problem with this: If Pos = 0 Then ErrString = ErrString + "F" Else Mid(ErrString, Pos, 1) = Lett End If

Mike · Accepted Answer · 2015-06-26 20:13:01Z

3

Might be better to use an array to hold the error codes, then concatenate each value in the array at the end to get your complete error code. You can then access their correct position by the array index.

Dim codes(0 To 4) As String
Dim errorString As String
Dim code As Variant

codes(1) = "F"
codes(4) = "G"

For Each code In codes
    If code <> "" Then
        errorString = errorString + code 'if there is a code, insert it
    Else
        errorString = errorString + " " 'if no code, insert a space
    End If
Next code

I hardcoded the errors but if you have a list somewhere you can loop through them and assign them like the psuedo-code below:

For Each error In errors
    codes(error.CodeNumber) = error.CodeCharacter
Next error

answered Jun 26, 2015 at 20:13

Mike

62011 silver badges16 bronze badges

1 Comment

Dasman Over a year ago

Hey Mike, For each instance/entry the user would check and uncheck whichever error box pertains. In case that influences your code anyhow.

Comintern · Accepted Answer · 2015-06-26 23:33:20Z

Either @Mike's or @TimWilliams' solutions will work - your (new?) issue is how you are selecting the items to add to your error string. When you use 'ElseIf', you'll only get one of your three conditions.

That said, here are a couple other options. First, similar to Mike's answer, you can use a Byte array...

Private Sub ExportError_Click()
    Dim myErr() As Byte
    myErr = StrConv(String$(5, " "), vbFromUnicode)

    Dim myFile As Integer
    myFile = FreeFile
    Open "C:\Reformatted.txt" For Output As #myFile

    If F_2 = True Then myErr(1) = CByte(Asc("F"))
    If G_3 = True Then myErr(2) = CByte(Asc("G"))
    If H_4 = True Then myErr(3) = CByte(Asc("H"))

    Print #myFile, StrConv(myErr, vbUnicode)
    Close #myFile

    Shell "Notepad.exe C:\Reformatted.txt", 1
End Sub

...or my personal preference, a Dictionary:

Private Sub ExportError_Click()
    Dim myErr As New Scripting.Dictionary
    Dim pos As Integer

    'Initialize to spaces.
    For pos = 1 To 5
        myErr.Add pos, " "
    Next pos

    Dim myFile As Integer
    myFile = FreeFile
    Open "C:\Reformatted.txt" For Output As #myFile

    If F_2 = True Then myErr(2) = "F"
    If G_3 = True Then myErr(3) = "G"
    If H_4 = True Then myErr(4) = "H"

    Print #myFile, Join$(myErr.Items, vbNullString)
    Close #myFile

    Shell "Notepad.exe C:\Reformatted.txt", 1
End Sub

Note that to use the Dictionary, you'll need a reference to the Microsoft Scripting Runtime. In the VB editor's menu, go to Tools->References..., and check the box next to "Microsoft Scripting Runtime". You may have to scroll a ways in the list.

Collectives™ on Stack Overflow

VBA Excel Characters and Spaces Concatenating Multiple Strings

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related