Is there a way to use the same code in multiple VBA scripts?

Question

I've got a dozen macros that have 90% similar code and I'm trying to find a way around having to revise every one each time they need to be universally tweaked (e.g., password change).

I have identified the code that is constant between them and am trying to find a way to have the end code be in this format:

specific code
universal code A
specific code
universal code B

I first tried to save the universal code as add-ins, which would have been ideal because not all of the macros are in the same file and I didn't want to have to make (not so tech savvy) users open multiple files.

When I did it this way, using Application.Run, I kept getting an error saying "the macro may not be available in this workbook or all macros may be disabled." Every check I did indicates that the add-in and its macros are available.

So I moved the universal code into the same file and tried Call. This came back with "Run-time error '-2147217908 (80040e0c)': Command text was not set for the command object. This was related to it trying to execute on a conn.State command.

I'd like the end code for the specific macros to look like this:

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Sub getCreativeData()
    sheetName = "Retrieve Creative Data"

    Application.Run "uploader_portable.xlam!GetA"

    querystr = "select * from me_dev.upfront_dashboard_creative_data"

    Application.Run "uploader_portable.xlam!GetB"
End Sub

(Sorry for showing so much code)

"uploader_portable.xlam!GetA" right now looks like this:

Sub GetA()
    Application.EnableEvents = False
    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = ActiveWorkbook
    Set ws = ActiveSheet

    wb.Activate
    ws.Select

    ws.Range("A1:AF1000").ClearContents

    connString = "Driver={Amazon Redshift (x64)}; yadayadayada"

    'Connect to Database
    conn.Open connString
End Sub

"uploader_portable.xlam!GetB" right now looks like this:

Sub GetB()
   ' Second set of code for the uploader retrieval add-in
    Set rs = conn.Execute(querystr, , adAsyncExecute)

    While conn.State = adStateExecuting + adStateOpen
        DoEvents
    Wend

    For x = 3 To rs.Fields.Count + 2
        ws.Cells(1, x) = rs.Fields(x - 3).Name
    Next

    If rs.RecordCount < Rows.Count Then
        ws.Range("C2").CopyFromRecordset rs
    End If    
End Sub

Do you just need to learn how to pass arguments in to a subroutine? and make each "universal code" more general that way. — BruceWayne, Jul 12 '19 at 21:25
Put the code in a `Function` or a ``Sub` then [call it](https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/calling-sub-and-function-procedures) — cybernetic.nomad, Jul 12 '19 at 21:26
And don't use Application.Run to call code from the addin. [Reference](https://stackoverflow.com/a/20177702/11683) the addin from the workbook. — GSerg, Jul 12 '19 at 21:41
Turning code which isn't adequately structured into add-ins will not make the code structured. Trying to use add-ins before the code is sufficiently polished is likely to raise more problems than it solves. Better to refactor the code using functions/subs with clean interfaces before you move it to an add-in (which might still be a good long-term goal). — John Coleman, Jul 12 '19 at 22:37
OOP is a thing, you should read up on it and then just learn to think in that way — Doug Coats, Jul 12 '19 at 23:59
@ajbentley I guess you can call me Morpheous now that ive given you the red pill. — Doug Coats, Jul 17 '19 at 16:24

score 0 · Answer 1 · answered Jul 13 '19 at 20:12

You dig in the right direction = The code must be used repeatedly! When you start with encapsulation, pull out the decorators, the hierarchy, the chain of methods, the unit tests, and the test-driven development. And maybe you will write such code:

Sub GetA()
    conn.Open connString, _
            range_Strange_Clear( _
            App_Events(False))
End Sub

Function connString( _
        Optional s As String) _
        As String

    connString = "Driver={Amazon Redshift (x64)}; yadayadayada"

End Function

Function range_Strange_Clear( _
        Optional s As String) _
        As String

    Range("A1:AF1000").ClearContents

End Function

Function App_Events( _
        b As Boolean) _
        As String

    Application.EnableEvents = b

End Function

Is there a way to use the same code in multiple VBA scripts?

1 Answers1