none
How to Set A Row In a Matrix At Once RRS feed

  • Question

  • The variable Source is a string such as "hello, my name is James"

    I am using this code to be able to select certain words in this sentence. 

    The code below would output arrayint as a 1x2 string array, with the first column being "hello" and the second being "my name is James".  Then, the code splits it up again based on spaces.  I want it to have the structure of [hello, , , ; my, name, is, James]

    I cannot figure out how to assign the columns to be what I want them to.

    In matlab I would say arr(counter - 1, 1:4), setting columns 1 to 4.  Is there a parallel way for visual basic?

    Dim arrint() As String arrint() = VBA.Split(Source, ",") Lengtharrint = UBound(arrint) + 1 Dim arr() As String For counter = 1 To Lengtharrint

    arr(counter - 1,???) = Split(arrint(counter - 1), " ") Next

    Wednesday, December 2, 2015 3:40 AM

All replies

  • Re:  text to an array

       

    Your narrative is not clear to me; try this...
    '---
    Sub TextToArray(ByRef strSource As String)
    'Jim Cone - Portland, Oregon - Dec. 2015
     Dim arr() As String
     Dim strNew As String
     Dim vTemp As Variant
     Dim C As Long
     Dim N As Long
     Dim R As Long

     C = 4  'column location
     R = 3  'row location
     N = VBA.InStr(1, strSource, ",", vbBinaryCompare)  'position of comma

     If N > 0 Then
       strNew = VBA.Right$(strSource, VBA.Len(strSource) - N)
     Else
       MsgBox "Can't find comma.   ", vbExclamation, "jsb348"
       Exit Sub
     End If
     vTemp = VBA.Split(VBA.Trim(strNew), " ", -1, vbBinaryCompare)

    'Size array
     ReDim arr(1 To 2, LBound(vTemp) To UBound(vTemp))
    'Load first element of array.
      arr(1, 0) = VBA.Left(strSource, N - 1)
    'Load second row of array.
     For N = LBound(vTemp) To UBound(vTemp)
       arr(2, N) = vTemp(N)
     Next
    'Place array values on worksheet.
     Range(Cells(R, C), Cells(R + 1, UBound(vTemp) + C)) = arr()
    End Sub
    '---
    Sub GetItStarted()
      Call TextToArray("hello, single document interface sucks")
    End Sub

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Proposed as answer by André Santo Wednesday, December 2, 2015 10:19 AM
    • Edited by James Cone Sunday, October 30, 2016 7:39 PM
    Wednesday, December 2, 2015 6:17 AM
  • I am new to VBA.  Can you explain:

    -the difference between Split() and VBA.Split()

    -why you used a $ after VBA.right

    -why you specified C, N, and R as Long and not Integer

    Also, thanks, this helped!

    Thursday, December 3, 2015 4:20 AM
  • Re:  code minutia

    "VBA." identifies the library from which the function comes.
    It is generally not necessay.

    $ used in VBA.Right$(...) forces the return value to a string.
    It is generally not necessary

    VBA, I read years ago, is more efficient when using Longs (or something like that - <grin>).
    An Integer variable for a row number is limited to ~32000, while the number of worksheet rows is ~1,000,000

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 7:39 PM
    Thursday, December 3, 2015 4:56 AM
  • Thanks again, this is only my second time using VBA and I am new to coding.  I have one last question:

    -If vTemp is going to be a string array, why did you specify it as a Variant?

    Thursday, December 3, 2015 5:07 AM
  • Re: more code details

    Why use Variant instead of String...

    Habit:
      In xl97 VBA, you could not assign to an Array.
    Belt and Suspenders:
      One never knows what the user is going to stuff into the function.
      I prefer using Variants when code is accepting user input.
    '---
    Some free advice about the forums (since you are new here), which you appear not to need...
      Always respond to people who attempt to answer your questions.
      Don't forget to mark appropriate responses as an Answer.

    '---
    Jim Cone
    Portland, Oregon USA


    • Edited by James Cone Sunday, October 30, 2016 7:39 PM
    Thursday, December 3, 2015 5:49 AM