Results 1 to 4 of 4
  1. #1
    dbguy is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2020
    Posts
    2

    Excel VBA conversion to Access VBA


    Hello everyone - I have an existing module in Excel that I would like to convert into a module that can work in Access instead. I am not familiar with Access VBA at all, so I don't even know where to begin. If anyone could help me get a start, I would be delighted.

    Here is the VBA for Excel:

    Code:
    Public Function CountExtensions(CurrentDueDate As Date, DueDateExtensionHistory As String) As Integer
    
    
        Dim StrDates() As String
        Dim DueDates() As Date
        Dim ExtCount As Integer
        
        'Checks for null field
        If DueDateExtensionHistory = "" Then
            CountExtensions = 0
            Exit Function
        End If
        
        'Checks for trailing char return
        If Right(DueDateExtensionHistory, 1) = Chr(10) Then
            DueDateExtensionHistory = Left(DueDateExtensionHistory, Len(DueDateExtensionHistory) - 1)
        End If
        
        'Loads an array of strings of dates
        StrDates = Split(DueDateExtensionHistory, Chr(10))
            
        ReDim DueDates(UBound(StrDates()))
        
        
        'Converts the string dates into dates to prepare for comparison
        i = 0
        For i = 0 To UBound(StrDates())
            DueDates(i) = DateValue(StrDates(i))
        Next i
            
        'Calculates Extensions
        
        ExtCount = 0
        
        For i = 0 To UBound(DueDates())
        'Size of the array
            If i = UBound(DueDates()) Then
                If CurrentDueDate > DueDates(i) Then
                    ExtCount = ExtCount + 1
                End If
            Else
                If DueDates(i + 1) > DueDates(i) Then
                    ExtCount = ExtCount + 1
                End If
            End If
        Next i
        
        CountExtensions = ExtCount
    
    
        
    End Function

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That code should run as is in Access VBA. Place function in a general module.

    Call function from textbox or query or another procedure. Give inputs to arguments.

    Where will inputs come from?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    dbguy is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2020
    Posts
    2
    Quote Originally Posted by June7 View Post
    That code should run as is in Access VBA. Place function in a general module.

    Call function from textbox or query or another procedure. Give inputs to arguments.

    Where will inputs come from?
    Hi June - ok, so the inputs would come from two fields in the Access table. So in the CountExtensions functions, the first part would be the field tbl.Due Date and the second part would be tbl.DueDateEH. So the function would look like this:

    CountExtensions(tbl.Due Date,tbl.DueDateEH)

    I went to the database, Database Tools -> Visual Basic and copied the VBA into a module. But when I go to functions available in my query, I do not find the function there. Am I missing something?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Make sure function and module do not have same name.

    Make sure you save the code.

    It does show up in expression builder - which I seldom use.

    Access will find it when you complete an expression using it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 10-27-2015, 06:00 AM
  2. Replies: 2
    Last Post: 06-04-2014, 11:12 AM
  3. Replies: 6
    Last Post: 04-30-2013, 02:42 PM
  4. Replies: 3
    Last Post: 09-14-2011, 05:27 PM
  5. Conversion from Excel to Access
    By TMG in forum Import/Export Data
    Replies: 1
    Last Post: 10-20-2009, 12:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums