Very odd VBA error when using Split()

[WillockBoy]

I have a very odd VBA error when using Split() in a VBA function. I’m using it within a database running on a Microsoft Virtual Desktop in Access 365 in 64 bit mode. No VBA error code number is produced except for the attached screenshot of a very simple Split() function used to Tuesday 15 April 2025 the current application

I have exported the module to a new blank database with no other tables, forms, reports or modules included and, for a time it runs. However, for no obviously consistent reason the code stops running. I then created a simple Split() example and the same error occurs.

I have transferred the database in full (tables, linked tables, queries, forms, reports and modules) to a new access file, without success. I have also deleted all tables, linked tables, queries, forms, reports and all but the relevant VBA module from the new copy database, without success. I've also systematically reduced the number of "References" attached to the modules without success

Has anyone any thoughts or had any similar problems relating to the Split() function?

 

[Pat Hartman]

Compile errors are usually caused by invalid syntax or undeclared variables.

Here is the definition of Split(). As you can see, in this case, the error is syntax.

Split function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

Please note that Intellisense is your friend. If you don't understand the arguments, review the documentation.

 

[theDBguy]

Hi. Welcome to AWF!

In other words, you forgot to tell split what character you want to split by.

 

[Josef P.]

The use of the (VBA) Split function is perhaps unusual, but correct. (Without defined Delimiter parameter " " is used.)

If the compiler error is not marked, the VB project could be “confused”, then decompile would help. Of course, create a backup first.

 

[WillockBoy]

Dear All

Many thanks for your replies, which are much appreciated.

Firstly, my first Split() example was a poor one as I hadn't included In the split statement the string to search for. Nevertheless, the code does work in a new database file with no other objects. I have amended the code to add a "-" in the statement but I continue to get the same error.

JosefP., I have decompiled the copy of the database and re-tested. Sadly, all to no avail. I have made one error in stating the version of Access being used. It is in fact "Office 16".

If you have any other thoughts, they would be appreciated.

 

[Gasman]

Upload the dB.
With the required string delimiter this time.

 

[Josef P.]

1. Let's simply rule out Split as a direct cause: please test the attached file.
2. What do you have to change in the attached file for the error to occur?

Note: I had a similar error message several times when customizing msaccess-vcs with a 64-bit access when I used string arrays as parameters. But after decompile it was always fixed.

/edit: Another idea:
Have you created a separate Split function?
=> Try: LArray = VBA.Split(LString)

Example:

Private Sub TestMe()
    Dim LString As String
    Dim LArray() As String
'...
    LArray = Split(LString)
'...
End Sub


Private Function Split(ByRef ArrayToSplit() As String) As String()
'...
End Function

=> Error as shown in #1

 

[DHookom]

I tried the test and got this error message

Once I changed the Private to Public, it worked as expected.
I expect the OP would know if there was a user defined function with the name Split(). I've run into this mistake in the past.

 

[Josef P.]

Note: Private procedures can be started by placing the cursor in the function and pressing F5.
It's a habit of mine not to make test code publicly accessible. Hopefully I won't forget to make it public in future examples.

 

[Gasman]

I tried the test and got this error message
View attachment 119165

Once I changed the Private to Public, it worked as expected.
I expect the OP would know if there was a user defined function with the name Split(). I've run into this mistake in the past.

I had someone in another forum who named the module the same as the function name. Completely different error message.

 

[WillockBoy]

1. Let's simply rule out Split as a direct cause: please test the attached file.
2. What do you have to change in the attached file for the error to occur?

Note: I had a similar error message several times when customizing msaccess-vcs with a 64-bit access when I used string arrays as parameters. But after decompile it was always fixed.

/edit: Another idea:
Have you created a separate Split function?
=> Try: LArray = VBA.Split(LString)

Example:

Private Sub TestMe()
    Dim LString As String
    Dim LArray() As String
'...
    LArray = Split(LString)
'...
End Sub


Private Function Split(ByRef ArrayToSplit() As String) As String()
'...
End Function

=> Error as shown in #1

Josef P.,

You are a genius! Placing VBA. before Split(LString) is the solution.

I really appreciated your most valuable help. Thank you

 

[DHookom]

I had someone in another forum who named the module the same as the function name. Completely different error message.

I've seen that issue in addition to creating a function with the name of an existing function. I don't recall all the errors. That's why naming conventions rock. All my module names now begin with "mod". I have seen a naming convention for functions and subs but I haven't used them.

 

[Josef P.]

If VBA.Split works, there will be another Split function somewhere in your code or in a linked library (VBA references).
=> write Split (without VBA. before), set cursor inside the word and click Shift+F2. This should bring you to the split function.

 

[WillockBoy]

If VBA.Split works, there will be another Split function somewhere in your code or in a linked library (VBA references).
=> write Split (without VBA. before), set cursor inside the word and click Shift+F2. This should bring you to the split function.

Josef P.

Just an update. I was unaware of the use of Shift+F2 when searching for a function. When I carried out your suggestion, I found the culprit, just as you predicted. A Split function was found in a module, copied a long time ago from Stephen Lebans.

You learn something new every day and this experience has been really valuable. thank you once again.

 

[Pat Hartman]

Great find. I'm sure that Lebans function was created before Split() was added to Access. When naming objects and functions, I use a naming scheme that will never conflict with anything in Access, VBA, or SQL server because I've run into this in the past but not for a very long time.

 

[WillockBoy]

Great find. I'm sure that Lebans function was created before Split() was added to Access. When naming objects and functions, I use a naming scheme that will never conflict with anything in Access, VBA, or SQL server because I've run into this in the past but not for a very long time.

Pat
To devise and adhere to a naming system for Access VBA is an excellent idea that I will adopt and thank you for your post. As I wrote previously, it has been a valuable lesson to learn.