access vba office 365 undefined function error.

Posted on 2013-11-23
Last Modified: 2013-11-24
I have a new db in 2003 to 2007 format i am using access 365. I have a function that I hoped would split the string and return the rows of the following field:

SplitMultiDelims([VIN Number / Model Prefix],";")

AB1111; AB1112; AB1114

I would want returned three rows

everytime i try to run it says undefined function but not sure whats missing.

references attached
The code I found via google.

I created a hello world function and put that in the module and that worked so not sure what item is missing.

Function SplitMultiDelims(Text As String, DelimChars As String) As String()
' SplitMutliChar
' This function splits Text into an array of substrings, each substring
' delimited by any character in DelimChars. Only a single character
' may be a delimiter between two substrings, but DelimChars may
' contain any number of delimiter characters. If you need multiple
' character delimiters, use the SplitMultiDelimsEX function. It returns
' an unallocated array it Text is empty, a single element array
' containing all of text if DelimChars is empty, or a 1 or greater
' element array if the Text is successfully split into substrings.
Dim Pos1 As Long
Dim N As Long
Dim M As Long
Dim Arr() As String
Dim I As Long

' if Text is empty, get out
If Len(Text) = 0 Then
    Exit Function
End If
' if DelimChars is empty, return original text
If DelimChars = vbNullString Then
    SplitMultiDelims = Array(Text)
    Exit Function
End If

' oversize the array, we'll shrink it later so
' we don't need to use Redim Preserve
ReDim Arr(1 To Len(Text))

I = 0
N = 0
Pos1 = 1

For N = 1 To Len(Text)
    For M = 1 To Len(DelimChars)
        If StrComp(Mid(Text, N, 1), Mid(DelimChars, M, 1), vbTextCompare) = 0 Then
            I = I + 1
            Arr(I) = Mid(Text, Pos1, N - Pos1)
            Pos1 = N + 1
            N = N + 1
        End If
    Next M
Next N

If Pos1 <= Len(Text) Then
    I = I + 1
    Arr(I) = Mid(Text, Pos1)
End If

' chop off unused array elements
ReDim Preserve Arr(1 To I)
SplitMultiDelims = Arr
End Function

Open in new window

Question by:PeterBaileyUk

Author Comment

ID: 39671308
and reference attached
LVL 119

Expert Comment

by:Rey Obrero
ID: 39671354
if you are just after splitting a simple string AB1111; AB1112; AB1114

I would want returned three rows

you just need this simple code

Sub SplitInfo(vText As String, DelimChar As String)
Dim vArr() As String, j As Long
If vText & "" = "" Then Exit Sub

vArr = Split(vText, DelimChar)
For j = 0 To UBound(vArr)
    Debug.Print vArr(j)
End Sub

Open in new window

LVL 84
ID: 39671382
Are those references as shown in Office 365? Or are those from your dev machine?

In most cases, you don't need the extensibility reference unless you have code that works with the VBA Editor environment (i.e. opening a Module and changing code there), so try removing that.

You should also determine if you need the other references. To do that, first, make a backup, then do this:

Open the project, open the VBA Editor. Click Debug - Compile and fix any errors you encounter. After doing that, remove a single reference, and then click Debug - Compile again. If you do NOT receive any errors or warnings, then you don't need that reference and can remove it.

Note that you'll need (at least) the top 3 items on your reference list.
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39671924
You did not say *how* you are using that function.  Is it in a query?  As a data source in a form or report?  Are you using it in another VBA procedure or an Access macro?  Please fill us in on exactly when, where, and how you are using it.

Also, make sure that the function is in a "regular" VBA module--not a form, report, or class module--and that the module name is not the same as the function name.
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.


Author Comment

ID: 39672298
I followed your tips but even with a simple sub it is just not working as its small I have zipped and attached it so maybe someone could shed light on this
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39672338
You're trying to call a Sub from a query. Subs do not return anything, so it will fail. I did this:

Function SplitInfo(vText As String, DelimChar As String)
    Dim vArr() As String, j As Long
    If vText & "" = "" Then Exit Function

    vArr = Split(vText, DelimChar)
    SplitInfo = vArr(0)
End Function
And changed the query to this:

Expr1: SplitInfo([VIN Number / Model Prefix],";")

That parses the FIRST value (before the ;  delimiter) and returns it to the query.

If you have "AB1111; AB1112; AB1114" and pass that in, however, it would just return the first item in that string. I'm not sure how you'd return 3 distinct Rows in your query for a value like that - in fact, I don't think you can do this. I think you'd need a subquery that could be joined to the main query, which would show 3 exactly duplicated rows EXCEPT for that value.

Author Comment

ID: 39672508
ok I got it now, I created a recordset looped through and for each iteration created an array with the split function and added that to a duplicate table without data in it.

Public Sub Main()
Dim dbs As DAO.Database
Dim rstVins As DAO.Recordset
Dim rstNew As DAO.Recordset
Set dbs = CurrentDb
Set rstNew = dbs.OpenRecordset("NewTable")
Set rstVins = dbs.OpenRecordset("QryData")
Dim RecordCountIndex As Long
Dim TestArray() As String
Dim TestString As String
Dim x As Long
Dim LastNonEmpty As Integer
Dim i As Integer

With rstVins
x = .RecordCount
Debug.Print "total records: " & x
  'method one
    Do Until .EOF
 TestArray = Split(.Fields("VINstem").Value)

        For i = 0 To UBound(TestArray)
          TestArray(i) = Replace(TestArray(i), ";", "")
          rstNew!Vinstem = TestArray(i)
          rstNew!SuzukiSparePartsCatalog = rstVins.Fields("SuzukiSparePartsCatalog").Value
          rstNew!Modelname = rstVins.Fields("Modelname").Value
          rstNew!YearCode = rstVins.Fields("YearCode").Value
          rstNew!Year = rstVins.Fields("Year").Value
        Next i

End With
MsgBox ("end")
End Sub

Open in new window


Author Closing Comment

ID: 39672510
thank you..steered me in the right direction.

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now