Solved

Storing values to an array

Posted on 2016-10-05
8
74 Views
Last Modified: 2016-10-05
I have the following code which converts a cell string into a string of Ascii codes separated by comma:

Dim Str As String
Dim MyString As Variant

L = Len(Cell.Value)
c = Cell.Value

For P = 1 To L
    Sep = ","
    If P = L Then Sep = ""
    NextVal = Asc(Mid(c, P, 1)) & Sep
    Str = Str & NextVal
Next P

Open in new window


This works and if I put a Stop command in and hover over Str I see the comma separated list of Ascii codes.

I now want to refer to particular elements of the string so assume I need to convert it to an array so that I can use "Array(n)" to refer to the nth element of the array. How do I convert the string to an array or add the values to the array one at a time?

More specifically I want to sort the array into numerical order so am looking at using SMALL function to retrieve the Array elements in numerical order.

I have tried with adding:

MyString = Array(Str)
Ch = MyString(1)

Open in new window


Line 2 is trying to pull the first value from the Array but I get Subscript out of Range on that line.

Assistance much appreciated.

Thanks
Rob H
0
Comment
Question by:Rob Henson
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 125 total points
ID: 41829685
Try

MyString = Split(Str, ",")
0
 
LVL 30

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 125 total points
ID: 41829686
Have you tried MyString = Split(str, ",")
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 41829691
pls try
Sub Macro()

Dim Str As String
Dim MyString As Variant

L = Len(Cell.Value)
c = Cell.Value

For P = 1 To L
    Sep = ","
    If P = L Then Sep = ""
    NextVal = Asc(Mid(c, P, 1)) & Sep
    Str = Str & NextVal
Next P
MyString = Split(Str, ",")
QuickSort MyString, LBound(MyString), UBound(MyString)
Ch = MyString(0)
End Sub

Sub QuickSort(arr, Lo As Long, Hi As Long)
  Dim varPivot As Variant
  Dim varTmp As Variant
  Dim tmpLow As Long
  Dim tmpHi As Long
  tmpLow = Lo
  tmpHi = Hi
  varPivot = arr((Lo + Hi) \ 2)
  Do While tmpLow <= tmpHi
    Do While arr(tmpLow) < varPivot And tmpLow < Hi
      tmpLow = tmpLow + 1
    Loop
    Do While varPivot < arr(tmpHi) And tmpHi > Lo
      tmpHi = tmpHi - 1
    Loop
    If tmpLow <= tmpHi Then
      varTmp = arr(tmpLow)
      arr(tmpLow) = arr(tmpHi)
      arr(tmpHi) = varTmp
      tmpLow = tmpLow + 1
      tmpHi = tmpHi - 1
    End If
  Loop
  If Lo < tmpHi Then QuickSort arr, Lo, tmpHi
  If tmpLow < Hi Then QuickSort arr, tmpLow, Hi
End Sub

Open in new window

0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41829696
You can fill the array at the first place like below....
Dim MyString()
Dim i As Long
L = Len(Cell.Value)
c = Cell.Value

For P = 1 To L
    NextVal = Asc(Mid(c, P, 1))
    i = i + 1
    ReDim Preserve MyString(1 To i)
    MyString(i) = NextVal
Next P

Open in new window

And you can still incorporate the Str variable to have a comma separated values in the above code if required.
0
 
LVL 33

Author Comment

by:Rob Henson
ID: 41829811
@Neeraj and Saquib - Split(Str,",") did the business for making the String into an array; thank you.
@Rgonzo, I have tried your code in combo with a UDF but it doesn't give the correct result. The UDF and Sub are:

Function SortChar(c As Range)
Dim Str As String
Dim MyString As Variant

L = Len(c.Value)
T = c.Value

For P = 1 To L
    Sep = ","
    If P = L Then Sep = ""
    NextVal = Asc(Mid(T, P, 1)) & Sep
    Str = Str & NextVal
Next P

MyString = Split(Str, ",")
QuickSort MyString, LBound(MyString), UBound(MyString)
Ch = MyString(0)
SortChar = arr
End Function

Sub QuickSort(arr, Lo As Long, Hi As Long)
  Dim varPivot As Variant
  Dim varTmp As Variant
  Dim tmpLow As Long
  Dim tmpHi As Long
  tmpLow = Lo
  tmpHi = Hi
  varPivot = arr((Lo + Hi) \ 2)
  Do While tmpLow <= tmpHi
    Do While arr(tmpLow) < varPivot And tmpLow < Hi
      tmpLow = tmpLow + 1
    Loop
    Do While varPivot < arr(tmpHi) And tmpHi > Lo
      tmpHi = tmpHi - 1
    Loop
    If tmpLow <= tmpHi Then
      varTmp = arr(tmpLow)
      arr(tmpLow) = arr(tmpHi)
      arr(tmpHi) = varTmp
      tmpLow = tmpLow + 1
      tmpHi = tmpHi - 1
    End If
  Loop
  If Lo < tmpHi Then QuickSort arr, Lo, tmpHi
  If tmpLow < Hi Then QuickSort arr, tmpLow, Hi
End Sub

Open in new window

When I use the UDF and point at a cell containing "Robert Henson" I get 0

I was seeking help with this to try and get a different solution for the following question because the formula driven solution I gave would have involved too many helper columns.

https://www.experts-exchange.com/questions/28974215/Sorting-the-contents-of-a-text-string-in-Excel-in-Alphabetical-order.html?anchor=a41829745#a41829745

It looks like Aikimark has now solved that without resorting to Arrays with the following:
Public Function CollatedChars(ByVal parmString As String) As String
    Dim lngBuckets(0 To 255) As Long
    Dim lngLoop As Long
    Dim bChar As Byte
    For lngLoop = 1 To Len(parmString)
        bChar = Asc(Mid(parmString, lngLoop, 1))
        lngBuckets(bChar) = lngBuckets(bChar) + 1
    Next
    CollatedChars = vbNullString
    For lngLoop = LBound(lngBuckets) To UBound(lngBuckets)
        If lngBuckets(lngLoop) <> 0 Then
            CollatedChars = CollatedChars & String(lngBuckets(lngLoop), Chr(lngLoop))
        End If
    Next
End Function

Open in new window


For education purposes though, I would like to see why the UDF and Sub combo isn't working.

Thanks
Rob H
0
 
LVL 33

Author Comment

by:Rob Henson
ID: 41829844
Thanks RGonzo, now works. I guess it was just the Join(MyString) line that was missing which was pulling the string back from the QuickSort sub.

I will post the complete code in the other question as well for completeness; "Sharing the love!!" as they say.

Thanks
Rob H
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41829856
corrected Code
Function SortChar(c As Range)
Dim Str As String, str1 As String
Dim MyString As Variant

L = Len(c.Value)
T = c.Value

For P = 1 To L
     Sep = ","
    If P = L Then Sep = ""
    NextVal = Asc(Mid(T, P, 1))
    Str = Str & Format(NextVal, "000") & Sep
Next P

MyString = Split(Str, ",")
QuickSort MyString, LBound(MyString), UBound(MyString)
Ch = MyString(0)
For Idx = 0 To UBound(MyString)
    NextVal = Chr(MyString(Idx))
    str1 = str1 & NextVal
Next
SortChar = str1
End Function

Sub QuickSort(arr, Lo As Long, Hi As Long)
  Dim varPivot As Variant
  Dim varTmp As Variant
  Dim tmpLow As Long
  Dim tmpHi As Long
  tmpLow = Lo
  tmpHi = Hi
  varPivot = arr((Lo + Hi) \ 2)
  Do While tmpLow <= tmpHi
    Do While arr(tmpLow) < varPivot And tmpLow < Hi
      tmpLow = tmpLow + 1
    Loop
    Do While varPivot < arr(tmpHi) And tmpHi > Lo
      tmpHi = tmpHi - 1
    Loop
    If tmpLow <= tmpHi Then
      varTmp = arr(tmpLow)
      arr(tmpLow) = arr(tmpHi)
      arr(tmpHi) = varTmp
      tmpLow = tmpLow + 1
      tmpHi = tmpHi - 1
    End If
  Loop
  If Lo < tmpHi Then QuickSort arr, Lo, tmpHi
  If tmpLow < Hi Then QuickSort arr, tmpLow, Hi
End Sub

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 41829886
Note: I'm posting this post-closure.

If you want a single statement conversion of the string, use the StrConv() function.
Example:
    Dim b(), lngLoop
    b = StrConv("Now is the time", vbFromUnicode)
    for lngLoop = 0 to UBound(b)
        debug.print lngLoop, b(lngLoop)
    next

Open in new window

 0             78 
 1             111 
 2             119 
 3             32 
 4             105 
 5             115 
 6             32 
 7             116 
 8             104 
 9             101 
 10            32 
 11            116 
 12            105 
 13            109 
 14            101 

Open in new window

0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question