VBA, How to pass in a string argument, convert to single character array and resize according to non-CodePudding

Ok,

To all those who may come across this question, this is a problem I have been banging my head against for the past two weeks and have made little or no progress, so any help would be extremely welcome.

Here's the set up; then I will follow with an excerpt of the code I have written:

I am writing a function to get a very specific formula for each file name in a given folder. This naturally requires me to write a program which can take string arguments (in this case, excel file names) from a very broad domain of possibilities and yield a very specific output based on some key -and highly unique- parameters. Hence, my function is bijective and the set of arguments and set of products are massive; therefore, I am in the process of writing a sub-process for this function which partitions the string argument, by character, into a corresponding array, remove all the unnecessary characters, concatenate the remaining characters into the output string, and then go through a series of checks to enforce whatever name-formula the file requires. For now, I am just focused on splitting the string into an array, removing all non-numeric characters and combining the remaining characters back into a single string.

Naturally, I have tried the split function, but to my knowledge VBA doesn't support the splitting of a string into single characters. So I have written the following code, which, admittedly, is a bit inelegant, but I think in principle must work. -It does not. Would someone kindly tell me why it doesn't, and make a recommendation for altering it.

Dim arr() As Variant
        For i = Len(strArg) To i = 1
            If IsNumeric(Mid$(strArg, i, 1)) = True Then
            arr(i - 1) = Mid$(strArg, i, 1)
            Else: arr(i - 1) = ""
            End If
        Next
    newStr = Join(arr())

arr() always returns empty, so newStr is always "". Yet there are always numeric values in each string argument. -I can't imagine why I am getting this result. If I use ReDim arr(Len(strArg)), I get Len(strArg) number of " " back....

Thanks in advance to whomever may provide help.

CodePudding user response：

Not sure why you need to split it into an array for this. Your description says you only want to have numeric characters returned in a new string variable. A function like this should work for you:

Function GetNumbers(ByVal arg_sText As String) As String
    
    Dim i As Long
    Dim sChar As String
    Dim sNumbers As String
    
    For i = 1 To Len(arg_sText)
        sChar = Mid(arg_sText, i, 1)
        If IsNumeric(sChar) Then sNumbers = sNumbers & sChar
    Next i
    
    GetNumbers = sNumbers
    
End Function

Then just call it in your code like this:

newStr = GetNumbers(strArg)  'Example: "ab1c2d" = "12"

CodePudding user response：

Alternatively use a Regular Expression

Function NumOnly(s As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = False
        .IgnoreCase = True
        .Pattern = "[^0-9] "
         NumOnly = .Replace(s, "")
    End With  
End Function