Excel 2010 VBA dynamic info to static variable

Question

I have a number of cells with information formatted like this

XXXXXXXXXXXXXXXXXXX_'XXXXXXXXXXXXXXXXXXX'XXXXXXXXXXXXXXXXXXX'X'

Using VBA I want to split the information uing the quote as a delimiter

I take that cell in as a string.

I wrote a loop that goes through the string one character at a time and count the quotes (here it happens to be four but that could change) it stores that count in a variable: numQuotes

I wrote a second loop to go through the string and note the numerical position of the quote. I initialized a dynamic array called positions() and give it a size of numQuotes. Then each time it detects a quote it feeds that quotes position number into the array.

The code looks like this:

   ' loop2
    ' start with a dynamic array
    Dim positions() As Integer, size As Integer, k As Integer
    size = quoteCount
    ReDim positions(size)

    Dim quoteCount2 As Integer
    quoteCount2 = 0

    For k = 1 To stringLength

            Dim CharInspector2 As String
            CharInspector2 = Mid(cellContents, k, 1)
            If CharInspector2 = "'" Then
            quoteCount2 = quoteCount2 + 1
            positions(quoteCount2) = k
            End If
    Next k

That all works! But now I need to get the quote positions out into static variables for the next part of my code.

Is there a way to do that?

Thanks

Aharon

http://stackoverflow.com/questions/7097684/vba-arrays-and-global-variable-declarations — Ravi Yenugu, Jun 25 '14 at 13:08

score 0 · Answer 1 · answered Jun 25 '14 at 13:15

Here is one way:

Option Explicit

Sub test()
Dim foundPosition As Integer
Dim startPosition As Integer
Dim testString As String

testString = "XXXXXXXXXXXXXXXXXXX_'XXXXXXXXXXXXXXXXXXX'XXXXXXXXXXXXXXXXXXX'X'"

startPosition = 0

Do
    startPosition = startPosition + 1
    foundPosition = InStr(startPosition, testString, "'")
    If foundPosition <> 0 Then
        ' Do whatever you need to do with that position, here I'm just spitting out the position to the immediate window
        Debug.Print (foundPosition)
    End If
    startPosition = foundPosition
Loop Until startPosition = 0
End Sub

Excel 2010 VBA dynamic info to static variable

1 Answers1