Run Excel Trim Function from Access

[Edgar_]

I showed the code I used in post #17 and for Ofc2021, it does not create an Excel session.

Option Compare Database
Option Explicit

Private Sub VoxTalk(Msg As String, Optional Gender As String = "Male")
    Dim Vox As Object
    Set Vox.Voice = Vox.GetVoices("Gender = " & Gender).Item(0)
    Vox.Speak Msg
End Sub

Sub test()
    VoxTalk "hello"
End Sub

With or without a reference to Excel, it does not work for me using Ac2021 or Ac2016. I only removed the toggle variable.

 

[The_Doc_Man]

All I can say is I tested it under Ac2021. It was originally developed under Ac2010. It worked correctly for both versions. One thing you might wish to try, though... Make the variable Vox an object OUTSIDE of the sub declaration, as part of the class declaration area. Your problem might be a visibility issue.

 

[Josef P.]

[OT: SAPI.SpVoice]

If the Vox object has not been instantiated, the code can not work.

Private Sub VoxTalk(Msg As String, Optional Gender As String = "Male")
    Dim Vox As Object
    Set Vox = ...  '<----
    Set Vox.Voice = Vox.GetVoices("Gender = " & Gender).Item(0)
    Vox.Speak Msg
End Sub

Reading Text Aloud in Microsoft Access

Want a quick way to get started with Text-to-Speech in your VBA application? This 4-line method gets the job done and requires no references!

nolongerset.com

 

[The_Doc_Man]

My error. I missed one line hidden away .... In Form_Load you need

    Set Vox = CreateObject("SAPI.SpVoice")

This line was buried in the midst of a few other lines and I missed it. However, it DOES NOT trigger creation of an Excel instance even though it comes from the Excel library. So you need an SAPI object but not an Excel object.

Apologies for having missed that line buried in the midst of several other things being initialized in the particular project. And for that reason - being initialized in the _Load code, you have to make the Vox object part of the class module's declaration area.

 

[ebs17]

The question would therefore arise as to whether the WorksheetFunction class can be instantiated using its own CLSID key without having to use the parent object Excel.
I couldn't find such a CLSID.

 

[NauticalGent]

So DevHut has referenced this thread on his page and offered up his 2¢ (which is worth much more) using RegEx, and even mentions the Self-Healing-Object-Variables concept he wrote about in the past.

I thought I would mention it here just in case someone reads this thread in the future.

Good stuff!

 

[ebs17]

Daniel Pineault writes off, what an honor.
However, in consistent evaluation of the thread here, there is a weakness in the codes.

Dim oRegEx                As Object
Set oRegEx = CreateObject("VBScript.RegExp")

This happens with every function call.
The following variants are better:

1) The RegEx object is persistently created outside and simply used in the function (my standard because I potentially use RegEx more often and in different ways and am therefore limited to one object):

Private pRegEx As Object

Public Property Get oRegEx() As Object
   If (pRegEx Is Nothing) Then 
      Set pRegEx = CreateObject("Vbscript.Regexp")
      Set oRegEx = pRegEx
   End If
End Property

In tests with queries, i.e. repeating the function call many times, I found a performance advantage of a factor of 11 when using the persistent object. 1100 percent is a lot.

2) The object is declared statically and persists throughout the function's runtime.

Static oRegEx As Object
If (oRegEx Is Nothing) Then Set oRegEx = CreateObject("VBScript.RegExp")

 

[NauticalGent]

Daniel Pineault writes off, what an honor.

I think so, but then I feel the same way when others, such as yourself, donate their time to address and/or assist with something I am working on.

 

[ebs17]

I meant that in the most positive sense.
In most cases, programming means copying known elements and partial solutions and putting them together sensibly and efficiently. So copying and getting suggestions from elsewhere is the normal case.
Inventing something truly new is very rare.

 

[NauticalGent]

I meant that in the most positive sense.

 

[KitaYama]

Private pRegEx As Object

Public Property Get oRegEx() As Object
   If (pRegEx Is Nothing) Then
      Set pRegEx = CreateObject("Vbscript.Regexp")
      Set oRegEx = pRegEx
   End If
End Property

Set oRegEx = pRegEx
Should be outside of IF.