Thread: Initial Declaration of Non-Static Array in Function

Hello Everyone!

I've written this 'Keywords_Extract' Function (The function process a string, separates the words and returns the results to and undefined array.) many times and have finally come up with a version that seems to be stable and preform exactly how I need it to. However, I'm concerned about the required ErrorHandler: that I have written to make the code execute properly.

Once the code reaches the statement:

ReDim Preserve Keyword_List(UBound(Keyword_List) + 1) As String

the code returns a subscript out of range error due to passing the undefined array to the Ubound Function. I initially expected this to return False or 0 but this does not seem to be the case. To correct the problem I have written in some Error Handling that assumes on Error 9 the Keyword_List array has not yet been defined and defines it. Though this works, I'm concerned about making an assumption.

Is there any way return the value 0 (zero) from an undefined array or check if Error Code 9 was result of the specified undefined array?

I want to eliminate the ErrorHandler as a requirment or modify it so it's not assuming the error was caused by the undefined array. I do realize that I could probably define the array to 1 after the initial Dim statement and it may produce the desired results however, it doesn't feel like the best solution. (I could be wrong though.).

Any suggestion, comments, or ideas would be appreciated.
Thanks in advanced.

Option Explicit
Option Base 1

Function KeyWords_Extract(ByVal InpString As String) As Variant
On Error GoTo ErrorHandler

Dim Keyword_List() As String
Dim wBuffer As String

    Do While InStr(1, InpString, Chr(32)) > 0
    DoEvents
        InpString = mRtrim(mLtrim(InpString))
        Select Case InStr(1, InpString, Chr(32))
            Case Is > 0
                ReDim Preserve Keyword_List(UBound(Keyword_List) + 1) As String
                wBuffer = Mid(InpString, 1, InStr(1, InpString, Chr(32)) - 1)
                Keyword_List(UBound(Keyword_List)) = wBuffer
                InpString = Mid(InpString, InStr(1, InpString, Chr(32)), (Len(InpString) - InStr(1, InpString, Chr(32))) + 1)
            Case Is = 0
                ReDim Preserve Keyword_List(UBound(Keyword_List) + 1) As String
                wBuffer = InpString
                Keyword_List(UBound(Keyword_List)) = wBuffer
                InpString = InpString
        End Select
    Loop
KeyWords_Extract = Keyword_List()
Exit Function

ErrorHandler:
Select Case Err.Number
        Case 9
            ReDim Keyword_List(1) As String
            Resume Next
        Case Else
            On Error GoTo 0
    End Select
End Function

The following 2 functions are used to remove multiple spaces from the beginning and end of a string.

Function mRtrim(ByRef InpString As String) As String
    Do While Mid(InpString, Len(InpString), 1) = Chr(32)
    DoEvents
        InpString = Mid(InpString, 1, Len(InpString) - 1)
    Loop
    mRtrim = InpString
End Function

Function mLtrim(ByRef InpString As String) As String
    Do While Mid(InpString, 1, 1) = Chr(32)
    DoEvents
        InpString = Mid(InpString, 2, Len(InpString) - 1)
    Loop
    mLtrim = InpString
End Function

Hi Extradimensional and welcome to the forum,

To capture the last error number I think there is an Err.Number or ErrNum in VBA.
Look at the example at http://msdn.microsoft.com/en-us/libr...ffice.12).aspx to see how they pick up which was the last error number to occur. Edit: Duh - I see you already use Err.Number in your code....

Does that help?

I was also thinking that arrays always start with a zero element. I always have to look this up and might be part of your problem.

I also need to understand what you mean by an "Undefined Array" and see if this might be the problem.

Hi,
Perhaps you can load the array from the beginning with the number of elements in it, in the zero spot in the array. They do this for strings in other languages. The first byte is a length byte. If you handle the number of elements by yourself, using the first number in the array, you might not need to check the ubound or get an error in the first place.

I fear this function is equivalen to yours:

Function snb(c01)
  snb=split(trim(c01))
End Function

Hahahaha! Split eh? That sure would have been useful...

Your function comes really close to preforming the same function as mine, it has problems (returns the extra spaces to the array as elements) with multiple spaces in the string though. It might be to my advantage to utilize the split function and reduce the amount of code in my function; but I'll save that for when I get to "I need my code to go faster!" phase since what I have works as I need it to.

Once I get to that phase and if I rewrite my function utilizing split; I'll post the new function for anyone who wants it. For now, I have modified my Function to allow the user to specify a separator other than a space character.

Here is the modified function for anyone who is interested:

Function KeyWords_Extract(ByVal InpString As String, Optional ByVal chrSeperator As String) As Variant
On Error GoTo ErrorHandler

Dim Keyword_List() As String
Dim wBuffer As String
Dim IsArrDefined As Boolean

Select Case chrSeperator
    Case Is = ""
        chrSeperator = Chr(32)
    Case Else
        'Do Nothing and Continue
End Select

    Do While InStr(1, InpString, chrSeperator) > 0
    DoEvents
        InpString = mRtrim(mLtrim(InpString))
        Select Case InStr(1, InpString, chrSeperator)
            Case Is > 0
                ReDim Preserve Keyword_List(UBound(Keyword_List) + 1) As String
                wBuffer = Mid(InpString, 1, InStr(1, InpString, chrSeperator) - 1)
                Keyword_List(UBound(Keyword_List)) = wBuffer
                InpString = Mid(InpString, InStr(1, InpString, chrSeperator), (Len(InpString) - InStr(1, InpString, chrSeperator)) + 1)
            Case Is = 0
                ReDim Preserve Keyword_List(UBound(Keyword_List) + 1) As String
                wBuffer = InpString
                Keyword_List(UBound(Keyword_List)) = wBuffer
                InpString = InpString
        End Select
    Loop
KeyWords_Extract = Keyword_List()
Exit Function

ErrorHandler:
    Select Case Err.Number
        Case 9
            If (Not Not Keyword_List) <> 0 Then
                IsArrDefined = True
            Else
                IsArrDefined = False
            End If
            Select Case IsArrDefined
                Case True
                    On Error GoTo 0
                Case False
                    ReDim Keyword_List(1) As String
                    Resume Next
                Case Else
                    MsgBox "Catastrophic Error:" & Chr(13) & "Unreachable Area Accessed!" & Chr(13) & Chr(13) & "Terminating Code Execution.", vbCritical, "Error Returned from KeyWords_Extract Function"
                    End
            End Select
        Case Else
            On Error GoTo 0
    End Select
End Function

You'd better dive into VBA's builtin facilities:
s='separator'

Function snb(c01,s)
  do until instr(c01,string(2,s))=0
    c01=replace(trim(c01),string(2,s),s)
  loop
  snb=split(c01,s)
End Function

or

Function snb(c01, s)
  snb = Filter(Split(Replace(Replace(c01, string(2,s), s & "~" & s), string(2,s), s & "~" & s), s), "~", False)
End Function

Almost... However, your first function does not account for leading and trailing spaces returning them to the array as empty elements and your second function contains a syntax error which I don't care to debug to test its usefulness for my applications.

While I agree that I should probably read a list of built-in VBA functions so I know everything that is available; it would be prudent to post a link to such a list for my reference when suggesting I need to get up to speed on what VBA has to offer.

But for now, my function is functioning exactly as I need it to and I will use your example to rewrite it once I am ready to refine my application. I appreciate you taking the time to show me how absurdly long my function is compared to what it could be, but I had fun writing it and learned a few things on the way.

Thanks!

For those who are really interested: I amended my last code.

BTW: debugging is the best way to learn VBA
The desired links are on your computer: VBeditor F2 and F1.

Hey Extradimensional,

None of us know it all. We are all he trying our best. Now, back to your question.

I've read many times where programmers should avoid Option Base 1. The reason was that the default was 0 and if you didn't specify it, those "off by 1" errors happen. I got a great explaination of this history at:
http://vb.mvps.org/hardcore/html/opt...asedarrays.htm
which suggest you should you specify the array size to keep from getting "off by 1 errors".

Also in:
http://www.ozgrid.com/forum/showthread.php?t=57252
the off by 1 error is also discovered.

I hope you find the answer to your question. We always learn things from others, even if we don't like what we are learning.

How about joining in and answering some of these questions for others. That way you might learn you have a speciality and need to grow in other areas.

Hey Extradimensional,

I believe I have the answer for you. I find it at:
http://www.eggheadcafe.com/software/...-in-array.aspx
The clue is that arrays need to be declared as Variant Types.
It looks like you declare it as a String.

I'd love to know if this fixes your problem but don't know if you'll be back to read this extra information.

Thank you, Marvin P. I have found all of your posts to be most informative and helpful while they provide accurate and concise information relevant to the questions at hand.

I understand the risks of using Option Base 1 however I never use the zero position in the array and figured there is no reason to have a zero position in the array if I was never using it. However, I will keep the off by one error in mind for future reference during debugging.

I know I'm new here and have very few posts, 2 of those posts are answers to someones question though. (Even if I was beaten to the answer on one of them.) There are so many knowledgeable helpful people here posting in the forum it is a bit difficult to answer a question before someone else beats me to it. But I have been keeping an eye out for posts that I could help with, it's only fair since I have been helped.

And while I don't mean to belittle snbs' helpful examples of the built-in functions I was not using; the title of the post was "Initial Declaration of Non-Static Array in Function", which has been solved.

I appreciate all the information I have been given (especially that from snb who made it painfully clear that my function is crazy long and it would be in my best interest to take a look at the VBA. list) But if one is going to write a function to replace mine it would be common courtesy to do so with in the VBA editor (to avoid sever syntax errors) if one is to bother at all. The first example was enough to show proof of concept and I only pointed out in what way it didn't work since snb took the time to write it, I figured I would take the time to test it.

I don't believe I have any 'specialties' as most of my knowledge is derived from GWBASIC, QBASIC and Visual Basic (all of which I was never trained in) and I do have a lot to learn, that's why I am here.

Variant type is a great place to start as it caused me some issues in the past, however, if you scroll up to post #5 you will find the solution to my question which involves determining if the array has been defined or not if the program errors out (highlighted in bold).

I believe the code in the link you provided fails due to not having () following the declaration. The declaration I have used: "Dim Keyword_List() As String" declares an undefined string type array, seems to work fine, though I have not been able to pass arrays through a function without the function being declared as variant.

Thank you Marvin P.
Thank you snb.