References needed for Split

[PerlIsGood]

Alright, I'm losing my friggin mind here. I've used Split in VBScript projects before with no problems, but in my VBA project with Word/Access I'm getting a 'Sub or Function not defined' error. I'm assuming I need to add a reference to the project, but I can't for the life of me figure out which one I need...

Anyone know...? Notorious P.I.G.

 

[JohnYingling]

Are you using '97 or 2000 version. '97 does not have Split because the VBA has a VB5 base. Split, Replace, Join etc became available in VB6 and Office 2000.

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[PerlIsGood]

Gah! The company I'm writing this for is using Office '97 (but they use Outlook 2k). Looks like I'll have to do it the hard way :-\. Thanks for the info. Notorious P.I.G.

 

[PJlwcb]

Add this code to VBA97 to achieve Split/Join.
Be sure to remove/rename when you upgrade Office.


'Like VB6's Split function - should phase out with Access2000
'Note order of arguments differs from Perl's split function
Public Function Split(ByVal SourceText$, Optional DelimiterChars As Variant) As Variant
Dim iArrayIndex&, iPosn%, sInput$
ReDim ArrPieces(0) As String

If IsMissing(DelimiterChars) Then DelimiterChars = " "
sInput = SourceText
iPosn = InStr(sInput, DelimiterChars)
Do While iPosn
If iArrayIndex > UBound(ArrPieces) Then
ReDim Preserve ArrPieces(0 To iArrayIndex) As String
End If
ArrPieces(iArrayIndex) = Left$(sInput, iPosn - 1)
'Shrink sInput by
sInput = Mid$(sInput, iPosn + Len(DelimiterChars))
iPosn = InStr(sInput, DelimiterChars)
iArrayIndex = iArrayIndex + 1
Loop
If SourceText <> &quot;&quot; Then
If iArrayIndex > UBound(ArrPieces) Then
ReDim Preserve ArrPieces(0 To iArrayIndex) As String
End If
ArrPieces(iArrayIndex) = sInput
End If
Split = ArrPieces
End Function

Public Function Join(SomeArray As Variant, ByVal Delimiter As String) As String
Dim i%, sResult$
For i = LBound(SomeArray) To UBound(SomeArray)
sResult = sResult + Delimiter + SomeArray(i)
Next
If sResult <> &quot;&quot; Then sResult = Mid$(sResult, Len(Delimiter) + 1)
Join = sResult
End Function

 

[PerlIsGood]

Thx PJ. I didn't have `notification` on and didn't see this, but I ended up creating something similar to get around my problem. I'll keep your code around for safe keeping.

Got another problem:
Is Form_Load also not supported by Office`97? I've built a vb form into a Word template and everything is working just dandy, but I can't get Form_Load to work to populate my list_boxes...

I have a macro attached to a toolbar button:

Public Sub ClientCoding()
    Dim frmNew As New Normal.frmQuery
    Load frmNew
    frmNew.Show
End Sub

And then the form code:

Private Sub Form_Load()
    cbo1.AddItem "Billing"
    'ad nauseum
End Sub

Notorious P.I.G.