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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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.
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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.

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

792 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