Clearify Class Modules and Custom Functions (1 Viewer)

[silentwolf]

Hi guys,

hope I am not getting anyone on the nerves for asking again!?

This is like a little recap of different discussion and threads I had in previous posts.

Not sure if that is the way to point to a previous discussion?

Implements and Class specific methodes

As I like to reuse some of my code I deside to use a class Module to do so.

However there remains some questions for me and I was hoping for some Ideas.
I have read all your suggestions and I can't agree more on not trying to complicated more then anything else!

That is way I am still trying to find a solution or better a best practice type of approche for what I try do accomplish.

Below there is a part of my code which has also been discussed here and many of great help I did receive!
So that is not really the issue.

Public Sub UpdateAuszug(TableName As String)
    Dim db As DAO.Database
    Dim tbd As DAO.TableDef
   
    Dim strSQL As String
   
    Set db = CurrentDb
    Set tbd = db.TableDefs(TableName)
             
    strSQL = "UPDATE " & TableName & " SET Umsatztext = KillBlanks([Umsatztext])"
    db.Execute strSQL
   
    Set tbd = Nothing
    Set db = Nothing

End Sub

'Custom Function to delete blanks within a string
Public Function KillBlanks(TempString)
    Dim Temp        As String
    Dim Zeichen     As String
    Dim NeuerString As String
    Dim Gefunden    As Boolean
    Dim i           As Integer

    Gefunden = False
    NeuerString = ""

    If IsNull(TempString) Then
        ' Wenn kein Inhalt im Feld vorhanden ist,
        ' wird die Funktion abgebrochen
        Exit Function
    Else
        Temp = CStr(TempString)
        For i = 1 To Len(Temp)
            Zeichen = Mid$(Temp, i, 1)
            If Zeichen = Chr(32) Then
                If Not Gefunden Then
                    Gefunden = True
                    NeuerString = NeuerString & Zeichen
                End If
            Else
                NeuerString = NeuerString & Zeichen
                Gefunden = False
            End If
        Next i
        ' Das Ergebnis wird an die Funktion wieder übergeben
        KillBlanks = NeuerString
    End If
End Function

As mentioned that is a part a small part of a project.

What I did is I created a Class Module where I put something like

Properties:
*TableName
*FileName

Methode:
*ClearTable(tableName as string)
*ImportData(fileName as string)

In this kind of enviroment there is all clear for me to set it Up in a Class and it makes live a little easier and reusable.

However.

If I like to include the above Code in the Class there comes the Issue.

I got a Form with just two buttons btnBrowse, btnImport and a textBox txtFileName

I guess it is obvious what I like to do.. brows for the File, hit the Import Button and hopefully all goes to plan and the File gets Imported and Updated so
I can further process or save it and so on.

Question:
-When I try to put the custom functions and I do have a few in that particular Project to update "Strings" where should I put those? In the Same Class? In a Module? In the FormClass?

When I try to put the CustomFunctions in the Class module and Try to Run the "Public Sub UpdateAuszug(TableName As String)" it gives me an Error.
If I keep the Custom Function in a codeModule modFunctions It all works well.

So I do not like to complicate things but I like to have it kind of all in one place for reusability.

I learn from Tutorials and also Books and of course from you guys but Examples are always easier as the real live projects. And there I do have still a lack of understanding to have a good reusable and compact "project" so to speak.

Maybe some can let me know how you track this kind of "projects" either with a Class or without or in the FormClass Module.

Hope it makes sense to you what I am saying and get some input how you track those kind of projects.

Would be much appreciated!
P.S. I read of course all your suggestions in the previous discussions and I do understand them but still have some issues of not getting to sidetracked and get my project all messed up.

Cheers

 

[Uncle Gizmo]

The main reason I'm responding is that I notice you have not yet received a reply, hence I am bumping your question up the list so to speak.

I can't grasp the essence of your question. I think you mean you want to associate functions with a form via a class module?

I have experimented with class modules, and I wondered if you have seen my YouTube Play List:- Object Oriented - Nifty Access

1) - OOP's Beginner - Intro - Nifty Access​

YouTube Play List HERE:- Object Oriented - Nifty Access

Please Note, I am little better than that beginner with Class Modules, so take everything with a pinch of salt!

I also have a Blog on my website about my "Call Called" Class Module...

CallCalled Class Module - Nifty Access

CallCalled Class Module I will be adding information about my "CallCalled" Class Module here, as it becomes available. The most recent example of it is on Access World Forums here:-VBA to reuse data entry popup form Class Module - Overview Class Module - Overview Video 1 (01:09) You can see how...

www.niftyaccess.com

The "Call Called" Class Module allows you to add extra functions to your Form which may or may not be useful in your particular case. I am unable to judge, but I thought it might provide some insight into your problem...

 

[silentwolf]

Hi Uncle Gizmo,

many thanks for your reply! And Yes I found the first Video already pretty good and helpful!
I will check out your other videos too you have a good way of explaining!

Cheers for your help!

 

[Uncle Gizmo]

I found the first Video already pretty good and helpful!

The forum software stripped out the playlist link, just showing the first video! If you want to see the full playlist, follow the link here:-

YouTube Play List HERE:- Object Oriented - Nifty Access

 

[silentwolf]

Sure like to do that!!
Many thanks for sending the link!
Also there is not often Set used in any tutorials I have seen so far and always was wondering how to do that.
So nice that you did cover that straigth on the first video!

 

[MarkK]

I don't understand. Do you have a database you can post? It is sometimes easier to understand someone else's intent if we can see the code. Then, also, it is easy to express new ideas.

That said, the code you posted can be condensed. Consider...

Public Sub UpdateAuszug(TableName As String)
    CurrentDb.Execute "UPDATE " & TableName & " SET Umsatztext = KillBlanks([Umsatztext])"
End Sub

Public Function KillBlanks(TempString) As String
    KillBlanks = VBA.Replace(Nz(TempString, ""), " ", "")
End Function

Or even, combining everything...

Public Sub UpdateAuszug(TableName As String)
    CurrentDb.Execute "UPDATE " & TableName & " SET Umsatztext = Replace(Nz([Umsatztext], ''), ' ', '')"
End Sub

Cheers,

 

[silentwolf]

Hi thanks Mark for your reply,

I will try to put something together tomorrow.


Cheers

 

[silentwolf]

@mark,
Kill Blanks is a little different as "just getting rid of spaces" It deletes all Spaces longer then "one space"
As the Importat File has two or more empty spaces between words.

But will try to put something together later today.

Cheers

 

[MarkK]

Here's another shot at KillBlanks...

Function RemoveDoubleSpace(text As String)
'   recursive function to remove double spaces in text
'   using VBA.Replace() and VBA.InStr()
    Dim tmp As String
    ' remove double spaces
    tmp = VBA.Replace(text, "  ", " ")
    ' check if there are more, and if so, remove them too
    If InStr(tmp, "  ") Then tmp = RemoveDoubleSpace(tmp)
    ' return the result
    RemoveDoubleSpace = tmp
End Function

 

[MajP]

Instr returns a long so your recursive call will never occur. Need to check > 0

 

[MarkK]

I suspect the return value of InStr() is being implicitly converted to a boolean to satisfy the If block expression. Anything non-zero = True, and only zero = False. You can check > 0 if you prefer, but the code works correctly as posted in #9.

 

[CJ_London]

A recursive function is a very inefficient way of removing spaces, requires more code and will take up more room on the stack. Fine to practice concepts but not one I would recommend for a production environment. Just use a While loop. Having said that I suspect the time difference for even a string with 40 contiguous spaces will be minimal, but I would be more concerned about the impact on the stack

 

[silentwolf]

I use this function
I got it from someone but not sure anymore from whom.

'Löscht alle Leeren Zeichen Text
Public Function KillBlanks(TempString)
    Dim Temp        As String
    Dim Zeichen     As String
    Dim NeuerString As String
    Dim Gefunden    As Boolean
    Dim i           As Integer

    Gefunden = False
    NeuerString = ""

    If IsNull(TempString) Then
        ' Wenn kein Inhalt im Feld vorhanden ist,
        ' wird die Funktion abgebrochen
        Exit Function
    Else
        Temp = CStr(TempString)
        For i = 1 To Len(Temp)
            Zeichen = Mid$(Temp, i, 1)
            If Zeichen = Chr(32) Then
                If Not Gefunden Then
                    Gefunden = True
                    NeuerString = NeuerString & Zeichen
                End If
            Else
                NeuerString = NeuerString & Zeichen
                Gefunden = False
            End If
        Next i
        ' Das Ergebnis wird an die Funktion wieder übergeben
        KillBlanks = NeuerString
    End If
End Function

that works fine for me.

 

[CJ_London]

I would just use

Function RemoveDoubleSpace(ByVal text As Variant) as string
'text as variant to cater for nulls, add "" to covert to a zls if null
   while InStr(text & "", "  ")

       text = Replace(text & "", "  ", " ")

   wend

    RemoveDoubleSpace = trim(text)
   'returns a zls as a minimum, trim to remove spaces at beginning and end -could use with replace line instead
End Function

not sure why you are using VBA.

 

[silentwolf]

Ok,
I will try that ..) Thanks CJ

 

[Cronk]

Re #9 to #11

? instr("abxxcd","xx") = true
False

 

[silentwolf]

Hi guys,

sorry for the late reply.. had some bigger issues at home I had to take care of.

However I did change as suggested and CJ's function works quite well.

But I can not seam to get it why I can not put those custom function into the class module?

Sub UpdateAuszug(TableName As String)
    Dim db As DAO.Database
    Dim tbd As DAO.TableDef
    
    Dim strSQL As String
    
    Set db = CurrentDb
    Set tbd = db.TableDefs(TableName)
      
    strSQL = "UPDATE " & TableName & " SET Umsatztext =     RemoveDoubleSpace([Umsatztext])"
    db.Execute strSQL   
    
    Set tbd = Nothing
    Set db = Nothing

End Sub

The above code is in a Class Module. Just a part of it of course.

If I am putting a Custom function also into the Class Module

Function RemoveDoubleSpace(ByVal text As Variant) As String
'text as variant to cater for nulls, add "" to covert to a zls if null
   While InStr(text & "", "  ")

       text = Replace(text & "", "  ", " ")

   Wend

    RemoveDoubleSpace = Trim(text)
   'returns a zls as a minimum, trim to remove spaces at beginning and end -could use with replace line instead
End Function

When I run the code from a form I get a Runtime Error 3085 on the line marked in bold

if I put the Custom Function "RemoveDoubleSpace" in a Standart Module it works fine.

Why is that?
I would like to have all related Functions within the class so it is all in one rather then using a class and a standard module.

Thanks for help or clarifying that issue.

 

[MajP]

I would like to have all related Functions within the class so it is all in one rather then using a class and a standard module.

Unfortunately in VBA you cannot use a method of a class without instantiating an instance of the class. This is not the same in all languages. For example In VB.NET and C# you can declare a method as "Shared"

Instance and Shared Methods - VB.NET

As you have seen in earlier chapters, some classes allow you to call some of their members without first creating an instance of the class

www.w3computing.com

You still reference the class when using the method such as the "Math" class. To use square root function
x = Math.sqrt
In these languages there is nothing called a standard module, all modules are Class modules.

In VBA to do what you are saying is doable, but a little cumbersome. If you have some functions that the class uses, but you still want to use them you would have to instantiate the class.

If removeDoubleSpaces is public in some class to use it you would have to

dim Helper as new YourClassName
x = helper.removeDoubleSpaces ("some string")

Personally, I often double it up in both the class module and standardmodule. There is no problem with the same name in both modules. Anything in the class is protected from other code. If I have two classes that are using removedoublespace, I could put the code in a standard module and both classes could call that. But I write classes as standalone black boxes. If I want to use one of those classes in another application, I do not want to be forced to remember to also import a standard module with part of the code. Obviously you have to weigh this on how common the code is, how often is it updated, etc.

 

[silentwolf]

Hi MajP,
thanks for your explaination and link!



Cheers!

 

[Uncle Gizmo]

Use of Class Modules

💡 When to Use Functions and Classes

People always ask me when to use functions and when to use classes. In this short, I’ll give you a short explanation and examples that are easy to understand...

youtube.com