VBA string function

Question

Here is the prompted Question:

Write a sub that does the following: 1) it tasks the user to input a word; 2) it enters each character of the word in backwards order in consecutive cells in row 5, starting with cell "B5". hint: use a for loop.

I cant really figure out how to start, here is what I have but i know it is completely wrong:

Public Sub Question2()

Dim Word As String
Dim Counter As Integer

Word = InputBox("Please Enter a word")

For Counter = 1 To Len(Word)
    Range("B5").EntireRow.Value = StrReverse(Word)
Next

End Sub

BigBen · Accepted Answer · 2020-03-05 19:53:31Z

1

Perhaps something like the following, using Step -1 to loop from the last character to the first, Offset to increment the column with each iteration of the loop, and Mid$ to return the characters one at a time:

Public Sub Question2()

    Dim Word As String
    Dim Counter As Integer

    Word = InputBox("Please Enter a word")

    For Counter = Len(Word) To 1 Step -1
        Range("B5").Offset(, Len(Word) - Counter).Value = Mid$(Word, Counter, 1)
    Next

End Sub

Of if you want to use StrReverse:

Public Sub Question2()

    Dim Word As String
    Dim Counter As Integer

    Word = InputBox("Please Enter a word")
    Word = StrReverse(Word)

    For Counter = 1 To Len(Word)
        Range("B5").Offset(, Counter - 1).Value = Mid$(Word, Counter, 1)
    Next

End Sub

edited Mar 5, 2020 at 19:53

answered Mar 5, 2020 at 19:43

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

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

Comments

JustThisOnce · Accepted Answer · 2020-03-05 20:03:58Z

0

If you can be sure it is ANSI characters only (Most likely, fingers crossed) you can use StrConv to convert the string to UNICODE and then split into an array using the UNICODE terminator (Chr(0)) and then write the array directly to the cells...

Public Sub Question2()

   Dim vArray As Variant
   vArray = Split(StrConv(StrReverse(InputBox("Please Enter a word")), 64), Chr(0))
   Range("B5").Resize(1, UBound(vArray)) = vArray

End Sub

edited Mar 5, 2020 at 20:03

answered Mar 5, 2020 at 19:57

JustThisOnce

11 gold badge1 silver badge1 bronze badge

1 Comment

JustThisOnce Over a year ago

Oops - didn't notice the 'Use a For Loop' comment... Oh well.

Collectives™ on Stack Overflow

VBA string function

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related