Ranges and Arrays

[Will Brown]

I have two questions, and before I ask them there is one thing you
should know: I'm a newbie.

Question 1: I am making an encryption program for an IT project. I need
to have the user type in (via a userform) a string, have the program
take a character from the string and then put that into a sheet, first
char in A1, second in A2, etc., which does calculations on the character
to encrypt it. I can figure out how to code all of it besides the put
the characters into a sheet part. I thought the easiest way would be to
do this:

Dim char As String
Dim text As String
Dim i As Integer

While i < len(text)
char = Mid(text, i, 1)
(range code here).Value = char
Wend

but I have no idea how to make it put it in the right range! Is there a
way to make it so that the program puts the value of char into a range
based on i, like Sheet1.Range("A" + i) (that doesn't work, I already
tried)??

Question 2: Is there any way to create an array (like a matrix) in excel
without writing to a sheet?

Thank you very much for your time, and your help is very much
appreciated!!!!

Will Brown

 

[JulieD]

Hi Will

to put it out to a worksheet you can use the following code


Dim i As Long
For i = 1 To Len(TextBox1.Value)
Sheets("Sheet1").Range("A1").Offset(0, i - 1).Value =
Mid(TextBox1.Value, i, 1)
Next

note, you might like to include some error handling to deal with the
situation where the person types in more than 256 characters.

and yes, you can use arrays in excel - therefore an alternative would be:

Option Base 1
Private Sub CommandButton1_Click()
Dim myarray() As String
i = Len(TextBox1.Value)
ReDim myarray(i)
For i = 1 To Len(TextBox1.Value)
myarray(i) = Mid(TextBox1.Value, i, 1)
Next
'write out to a message box to prove it works
For i = 1 To Len(TextBox1.Value)
MsgBox myarray(i)
Next
End Sub

 

[Tom Ogilvy]

Dim char As String
Dim text As String
Dim i As Integer

i = 1
Do While i <= len(text)
char = Mid(text, i, 1)
worksheets("Sheet1").Cells(2,i).value = char
i = i + 1
Loop

or to populate an array

Dim char As String
Dim text As String
Dim i As Integer
Dim v() as String


redim v(1 to len(text))
i = 1
Do While i <= len(text)
char = Mid(text, i, 1)
v(i) = char
i = i + 1
Loop

 

[Bob Phillips]

Will,

I don't understand why you want to put it into a range, or even into a VBA
array. Why not just work on each character in a similar way to what you have

For i = 1 To Len(Text)
val = EncryptChar(Mid(text,i,1))
'do whatever with val
Next i

where EncryptChar is the encrypting function which returns a value that you
process.

--

HTH

RP
(remove nothere from the email address if mailing direct)