Real Geek Forums > Archives > Operating Systems > Apple Macintosh > Mac Office > Mac Office Excel > VBA Split function missing ?

VBA Split function missing ?

Posted: 02-14-2006, 01:37 PM
Francis Dufour
Guest
Posts: n/a
 
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

VBA Split function missing ?


Reply With Quote

Responses to "VBA Split function missing ?"

JE McGimpsey
Guest
Posts: n/a
 
Re: VBA Split function missing ?
Posted: 02-14-2006, 04:14 PM
In article <C017556A.14AD%nospam@localhost>,
Francis Dufour <nospam@localhost> wrote:
> 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
Reply With Quote
Francis Dufour
Guest
Posts: n/a
 
Re: VBA Split function missing ?
Posted: 02-14-2006, 06:23 PM



Le 14/02/06 12:14, dans
jemcgimpsey-553E92.10145714022006@msnews.microsoft.com, «*JE McGimpsey*»
<jemcgimpsey@mvps.org> a écrit*:
> In article <C017556A.14AD%nospam@localhost>,
> Francis Dufour <nospam@localhost> wrote:
>
>> 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:
>
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).


Reply With Quote
JE McGimpsey
Guest
Posts: n/a
 
Re: VBA Split function missing ?
Posted: 02-15-2006, 01:51 PM
In article <C017985D.14BC%nospam@localhost>,
Francis Dufour <nospam@localhost> wrote:
> 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.
Reply With Quote
Francis Dufour
Guest
Posts: n/a
 
Re: VBA Split function missing ?
Posted: 02-22-2006, 05:41 PM



Le 15/02/06 09:51, dans
jemcgimpsey-2F3924.07510815022006@msnews.microsoft.com, «*JE McGimpsey*»
<jemcgimpsey@mvps.org> a écrit*:
> In article <C017985D.14BC%nospam@localhost>,
> Francis Dufour <nospam@localhost> wrote:
>
>> 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.

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.

Reply With Quote
JE McGimpsey
Guest
Posts: n/a
 
Re: VBA Split function missing ?
Posted: 02-23-2006, 09:43 PM
In article <C0221A7B.16D8%nospam@localhost>,
Francis Dufour <nospam@localhost> wrote:
> 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
Reply With Quote
Francis Dufour
Guest
Posts: n/a
 
Re: VBA Split function missing ?
Posted: 02-24-2006, 05:18 PM



Le 23/02/06 17:43, dans
jemcgimpsey-DB522A.15435423022006@msnews.microsoft.com, «*JE McGimpsey*»
<jemcgimpsey@mvps.org> a écrit*:
>
> 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.

Reply With Quote
Francis Dufour
Guest
Posts: n/a
 
Re: VBA Split function missing ?
Posted: 02-27-2006, 06:25 PM



Le 24/02/06 13:18, dans C024B828.1726%nospam@localhost, «*Francis Dufour*»
<nospam@localhost> a écrit*:
>
> 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.

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


Thanks.

Reply With Quote
JE McGimpsey
Guest
Posts: n/a
 
Re: VBA Split function missing ?
Posted: 02-28-2006, 02:22 PM
In article <C028BC6C.1767%nospam@localhost>,
Francis Dufour <nospam@localhost> wrote:
> 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.
Reply With Quote
Francis Dufour
Guest
Posts: n/a
 
Re: VBA Split function missing ?
Posted: 03-02-2006, 01:13 PM



Le 28/02/06 10:22, dans
jemcgimpsey-33AA93.08222128022006@msnews.microsoft.com, «*JE McGimpsey*»
<jemcgimpsey@mvps.org> a écrit*:
> In article <C028BC6C.1767%nospam@localhost>,
> Francis Dufour <nospam@localhost> wrote:
>
>> 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.

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


Reply With Quote
 
LinkBack Thread Tools Display Modes
Reply

« Previous Thread | Next Thread »

Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are Off
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On
Forum Jump


Similar Threads
Thread Thread Starter Forum Replies Last Post
Greyed out Split function Breadstick Windows XP Movie Maker 5 11-12-2006 10:01 PM
restore function and help function missing armin Windows XP Configuration & Management 2 11-11-2004 02:08 PM
XP Zip Function Missing ... A n n e Windows XP Help & Support 10 10-24-2003 05:27 PM
XP Search function missing Peter Windows XP Basics 1 10-16-2003 08:36 PM
'Search' ..function missing Marie S. Windows XP Basics 0 08-26-2003 08:29 PM