VBA Split function missing ?

[Francis Dufour]

Hello,

I'm triying to use the Split function in a Excel macro with Mac Excel 2004.


Public Sub Workbook_Open()
Dim strTemp() As String
strTemp = Split(Application.Path, ":")
strPath = strTemp(0) + ":FT:"
MsgBox strPath
End Sub


When this code executes, Excel says : "Sub or function not defined".

It works perfectly on a PC. Does the Split function exist on Excel 2004 for
Mac ?


Thanks

 

[JE McGimpsey]

It works perfectly on a PC. Does the Split function exist on Excel 2004 for
Mac ?

No - Mac VBA is VBA5, i.e., the same version as WinXL97. Split was
introduced in VBA6.

What I do for my cross-platform apps is to include a conditionally
compiled substitute function in the app. That way for WinXL, the faster,
native function is called, but it still works for MacXL. For instance,
for Split, this is similar to something I've used:

#If Mac Then
Public Function Split(ByVal sInput As String, _
Optional ByVal sDelimiter As String, _
Optional ByVal nLimit As Long = -1, _
Optional ByVal bCompare As Integer = vbBinaryCompare _
) As Variant
Dim nCount As Long
Dim nPos As Long
Dim nDelimiterLength As Long
Dim nStart As Long
Dim sOutput() As String

If nLimit = 0 Then
Split = Array()
Else
nDelimiterLength = Len(sDelimiter)
If nDelimiterLength = 0 Then
Split = Array(sInput)
Else
nStart = 1
nPos = InStr(nStart, sInput, sDelimiter, bCompare)
Do While nPos
ReDim Preserve sOutput(0 To nCount) As String
If nCount + 1 = nLimit Then
sOutput(nCount) = Mid(sInput, nStart)
Exit Do
Else
sOutput(nCount) = Mid(sInput, nStart, nPos - nStart)
nStart = nPos + nDelimiterLength
End If
nCount = nCount + 1
nPos = InStr(nStart, sInput, sDelimiter, bCompare)
Loop
ReDim Preserve sOutput(0 To nCount) As String
sOutput(nCount) = Mid(sInput, nStart)
Split = sOutput
End If
End If
End Function
#End If

 

[Francis Dufour]

Le 14/02/06 12:14, dans
(e-mail address removed), « JE McGimpsey »

No - Mac VBA is VBA5, i.e., the same version as WinXL97. Split was
introduced in VBA6.

What I do for my cross-platform apps is to include a conditionally
compiled substitute function in the app. That way for WinXL, the faster,
native function is called, but it still works for MacXL. For instance,
for Split, this is similar to something I've used:

Ok, thanks for the code.

But my macro still doesn't work. Excel says "Impossible d'affecter à un
tableau". I don't know what should be the exact message in English but
should be something like "Cannot affect to an array".

The same code work with a Windows version of Excel (but it uses the native
Split() function).

 

[JE McGimpsey]

Ok, thanks for the code.

But my macro still doesn't work. Excel says "Impossible d'affecter à un
tableau". I don't know what should be the exact message in English but
should be something like "Cannot affect to an array".

Probably "Can't assign to array"

Assign the variable as a variant rather than an array of strings. For
instance, instead of:

Dim str() As String

use

Dim str As Variant

The same code work with a Windows version of Excel (but it uses the native
Split() function).

Yes, VBA6 has a number of changes to variable declaration and assignment
that isn't easily fixed in VBA5. However, using a Variant, while
slightly more overhead, will work on both platforms.

 

[Francis Dufour]

Le 15/02/06 09:51, dans
(e-mail address removed), « JE McGimpsey »

Probably "Can't assign to array"

Assign the variable as a variant rather than an array of strings. For
instance, instead of:

Dim str() As String

use

Dim str As Variant


Yes, VBA6 has a number of changes to variable declaration and assignment
that isn't easily fixed in VBA5. However, using a Variant, while
slightly more overhead, will work on both platforms.

OK.. Seem to work.. But..

When I open my file, sometime Excel will crash. If it doesn't crash it will
crash when I will want to save or it will deny the save with the message
"Document non enregistré". If I remove/comment this code from
"ThisWorkBook', Excel doesn't crash and I can save :


Public strPath As String
Private Sub Workbook_Open()
Dim strTemp As Variant
strTemp = Split(Application.Path, ":")
strPath = strTemp(0) + ":FT:"
End Sub


When it crash, it said:

Microsoft Error Reporting log version: 1.0

Error Signature:
Exception: EXC_BAD_ACCESS
Date/Time: 2006-02-22 13:29:08 -0500
Application Name: Microsoft Excel
Application Version: 11.2.0.050714
Module Name: VBE
Module Version: 11.2.0.050718
Module Offset: 0x000f2800
Extra app info: Reg=French Loc=0x040c


How can I do this simple thing (get the absolute path to a specific folder)
without crashing ?

It doesn't crash on a PC (even if I rename the Split function to Split2 and
I call it). I tried on two Mac, one on OSX 10.3 and one on 10.4.

Thanks.

 

[JE McGimpsey]

How can I do this simple thing (get the absolute path to a specific folder)
without crashing ?

It doesn't crash on a PC (even if I rename the Split function to Split2 and
I call it). I tried on two Mac, one on OSX 10.3 and one on 10.4.

Can't tell from your crash log snippet what's happening (and it would be
unlikely that I could even if you'd supplied the whole log).

Your Workbook_Open code works fine for me with XL 11.2.2 under OS X
10.4.5, using the conditionally compiled Split() function.

However, I don't understand how it works on a PC if you use a MacOS path
separator (":")...

It's possible that, after lots of editing, your code module is corrupt
(WinXL seems to tolerate corruption far better than MacXL). I would try
running Rob Bovey's Code Cleaner on it (using the PC):

http://appspro.com/Utilities/CodeCleaner.htm

 

[Francis Dufour]

Le 23/02/06 17:43, dans
(e-mail address removed), « JE McGimpsey »

Can't tell from your crash log snippet what's happening (and it would be
unlikely that I could even if you'd supplied the whole log).

Your Workbook_Open code works fine for me with XL 11.2.2 under OS X
10.4.5, using the conditionally compiled Split() function.

Hello,


How to reproduce the bug:

1- Create a new Excel document
2- Add the code in ThisWorkBook and the Split function into a module
3- Quit the VBA editor
4- Enter some data in the sheet
5- Save the document & Quit Excel
6- Open the Excel document (may crash at this step)
7- Enter some data in the sheet
8- Try to save (option+s) : it will crash or will say "Document non
enregistré" (don't know the exact message in English) and won't save the
document.

If nothing is wrong, quit Excel and do again steps 6 to 8. It should bug
with less than 3 tries.

I can reproduce this on OSX 10.3 and OSX 10.4.5 with Excel 2004 11.2.


Where did you find 11.2.2 ? I'm at Office 11.2.1 (Excel 12.2.0) and
autoupdate says there is no updates for me (and no downloads for 11.2.2 on
the Microsoft site).

However, I don't understand how it works on a PC if you use a MacOS path
separator (":")...

When I say it works, I want say it doesn't crash. When I try to save, it's
not with vba but with option+s (it also crash with a save call in vba but
this is not the question), so my strPath doesn't need to be used for saving
for making Excel to crash.

It's possible that, after lots of editing, your code module is corrupt
(WinXL seems to tolerate corruption far better than MacXL). I would try
running Rob Bovey's Code Cleaner on it (using the PC):

http://appspro.com/Utilities/CodeCleaner.htm

I tried the Code Cleaner without success. I still have this bug .


Thanks.

 

[Francis Dufour]

Le 24/02/06 13:18, dans C024B828.1726%nospam@localhost, « Francis Dufour »

How to reproduce the bug:

1- Create a new Excel document
2- Add the code in ThisWorkBook and the Split function into a module
3- Quit the VBA editor
4- Enter some data in the sheet
5- Save the document & Quit Excel
6- Open the Excel document (may crash at this step)
7- Enter some data in the sheet
8- Try to save (option+s) : it will crash or will say "Document non
enregistré" (don't know the exact message in English) and won't save the
document.

If nothing is wrong, quit Excel and do again steps 6 to 8. It should bug
with less than 3 tries.

I can reproduce this on OSX 10.3 and OSX 10.4.5 with Excel 2004 11.2.


Where did you find 11.2.2 ? I'm at Office 11.2.1 (Excel 12.2.0) and
autoupdate says there is no updates for me (and no downloads for 11.2.2 on
the Microsoft site).



When I say it works, I want say it doesn't crash. When I try to save, it's
not with vba but with option+s (it also crash with a save call in vba but
this is not the question), so my strPath doesn't need to be used for saving
for making Excel to crash.

Any ideas ? Are you able to reproduce the bug by following my steps ?


Thanks.

 

[JE McGimpsey]

Any ideas ? Are you able to reproduce the bug by following my steps ?

I missed your earlier post - I'll take a look at it today if I get a
chance.

 

[Francis Dufour]

Le 28/02/06 10:22, dans
(e-mail address removed), « JE McGimpsey »

I missed your earlier post - I'll take a look at it today if I get a
chance.

Hello,

The bug seem to be in your split function. I found this function as a
replacement and it doesn't crash/bug anymore.

#If Mac Then
Function Split(Chaîne As String, Optional Balise As String = " ") As
Variant

Dim Éléments() As String, LongChaîne As Long, PrécBalise As Long
K = -1
'Cas où aucune balise n'est spécifiée : le tableau n'a qu'une
'entrée qui contient toute la chaîne
If Len(Balise) = 0 Then
ReDim Éléments(0)
Éléments(0) = Chaîne
GoTo Fin
End If

Do
K = K + 1
LongChaîne = Len(Chaîne)
'Recherche le dernier caractère avant la balise
PrécBalise = InStr(1, Chaîne, Balise, vbBinaryCompare) - 1
'Quand il n'y a plus d'occurrence de la balise, récupère
'la fin de la chaîne

If PrécBalise = -1 Then PrécBalise = LongChaîne
'Insère les données dans une entrée du tableau
ReDim Preserve Éléments(K)
Éléments(K) = Mid(Chaîne, 1, PrécBalise)
If PrécBalise = LongChaîne Then Exit Do
'Réduit la chaîne
Chaîne = Right(Chaîne, LongChaîne - PrécBalise - Len(Balise))
Loop

Fin:
Split = Éléments()
End Function

#End If