0

I would like to use piece of the code in several modules of Workbook VBA. Basically this is used to establish connection to file management system via it's client. I use it in a lot of macros and have to copy this to each module separately. Is it possible to convert this to some kind Public function or macro so it can be used in whole Workbook?

On top of each module there is this one:

Option Explicit

Const szVaultName = "Name"
Const szVaultGUID As String = "{1234}" 

Public oMFClientApp As New MFilesAPI.MFilesClientApplication
Public oObjVerFile As MFilesAPI.ObjectVersionAndProperties
Public oVault As MFilesAPI.Vault
Public oObjectSearchResults As MFilesAPI.ObjectSearchResults

Here is code for establishing connection:

Sub EstablishMFilesConnection()
    Dim oMFClientApp As New MFilesAPI.MFilesClientApplication
    Dim oVaultConnections As MFilesAPI.VaultConnections

    'Login to the vault
    Set oVaultConnections = oMFClientApp.GetVaultConnectionsWithGUID(szVaultGUID)

    If oVaultConnections.Count = 0 Then
        Set oVault = oMFClientApp.BindToVault(szVaultName, 0, True, True)
        MsgBox "No vaults found with the GUID: " + szVaultGUID, vbExclamation
        End
    Else
        On Error Resume Next

        Set oVault = oMFClientApp.BindToVault(oVaultConnections.Item(1).Name, 0, True, True)
        oVault.TestConnectionToVault

        If Err.Number <> 0 Then
            MsgBox "Can't connect to M-Files", vbExclamation
            End
        End If

        On Error GoTo 0
    End If

    On Error GoTo 0

End Sub
10101
  • 2,232
  • 3
  • 26
  • 66
  • Are you talking about using `EstablishMFilesConnection()` within one workbook or would you like to use it over several workbooks? – Storax Nov 04 '19 at 16:34
  • Same workbook, but then I have Public variables. Or should I just insert them to each macro? – 10101 Nov 04 '19 at 16:36
  • If the public variables exist in every module, they have no business being public. – Mathieu Guindon Nov 04 '19 at 16:37
  • 1
    Rule of thumb, a connection (and objects and variables in general) should be as short-lived as possible. Looks like `EstablishMFilesConnection` should be a `Function` procedure that *returns* a `MFilesAPI.Vault` object that other code can use -- there's no need for `oVault` to be module-scope. – Mathieu Guindon Nov 04 '19 at 16:40
  • Yes, `oVault` is used later in other codes. So basically this should be public function – 10101 Nov 04 '19 at 16:46
  • 1
    Yes. Same with the "search results" variable, and likely the "version and properties" variable too. The last thing you want is stale global state, and global state that can be overwritten at any time by anything anywhere. Use functions and pass parameters, avoid globals. Note that the `As New` object is auto-instantiated - you probably want to avoid that. – Mathieu Guindon Nov 04 '19 at 16:49

0 Answers0