Can Excel Vba code be updated via an Excel Add In for multiple users

Question

I have an Excel workbook that contains lots of VBA code. The VBA Code consists of many Sub routines, Functions and User Forms. Over 200+ employees will be using this Workbook.

Currently my VBA code lives inside the distributed Excel Workbook. The problem I fear I will be faced with is updating each Workbooks VBA code if any update is ever needed.

Would it be best to write all my VBA code as part of an Add In, upload a new version of the Add In to a site and have employees download from there? If so, would I encounter any limitations or restrictions? Is such feature even possible? Is VB.Net a better solution?

I have created an XLAM file from my original Workbook File. The original Workbook file containa all my Sub Routines, Functions, and UserForms. I am encountering an error when calling the UserForm directly, even though I referenced the XLAM file that contains UserForm1.

The following scenarios are being ran from the distributed WorkBook copy. The WorkBook is referencing the XLAM file.

Scenario1: Calling a UserForm from a Sub assigned to a shape The following Sub returns a Runtime Error 424 Object Required

Sub RectangleRoundedCorners1_Click()
UserForm1.Show 'highlights this line on the error, XLAM reference houses UserForm1
End Sub

Scenario2: Calling a Sub Procedure from a shape that calls the UserForm This method doesn't return an error, why? Can we not reference UserForm Objects from a referenced Add In?

Sub RectangleRoundedCorners1_Click()
showUserForm
End Sub

Sub showUserForm()
UserForm1.Show
End Sub

Scenario 3: Using UserForms to input values into Worksheet Cells

Would I have to refrence the ActiveWorkbook in each of my UserForms?

Private Sub CommandButton1_Click()
Set wb = ActiveWorkbook
Set ws = wb.Sheets("clientmenu")
    forceLogOut
    'clear filter so that we dont mix new customers up

    If ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
        With ws.Shapes("priorities")
            .Fill.ForeColor.RGB = RGB(64, 64, 64)
        End With
    End If


    If contact.value <> "" And result.value = vbNullString Then
        MsgBox "Please enter a result"
        result.BorderColor = vbRed
        result.BackColor = vbYellow
        result.DropDown
        Exit Sub

    ElseIf contact.value = vbNullString And result.value <> "" Then
        MsgBox "Please enter a date"
        contact.BorderColor = vbRed
        contact.BackColor = vbYellow
        Exit Sub

    Else: With ws
            callDate
            callResult
        End With
    End If




    With ws
        lastrow = .Range("A" & Rows.Count).End(xlUp).Row + 1

        If Me.priority_ = vbNullString Then
            ws.Range("A" & lastrow).Interior.Color = vbWhite
            ws.Range("A" & lastrow).Font.Color = RGB(0, 0, 0)

        ElseIf Me.priority_ = "None" Then
            ws.Range("A" & lastrow).Interior.Color = vbWhite
            ws.Range("A" & lastrow).Font.Color = RGB(0, 0, 0)
            ws.Range("B" & lastrow).value = vbNullString

        ElseIf Me.priority_ = "High" Then
            '.Cells(x, 1).Interior.Color = RGB(0, 176, 80)
            ws.Range("A" & lastrow).Font.Color = RGB(0, 176, 80)
            ws.Range("B" & lastrow).value = addnewClient.priority_.Text
        ElseIf Me.priority_ = "Medium" Then
            '.Cells(x, 1).Interior.Color = RGB(255, 207, 55)
            ws.Range("A" & lastrow).Font.Color = RGB(255, 207, 55)
            ws.Range("B" & lastrow).value = addnewClient.priority_.Text
        ElseIf Me.priority_ = "Low" Then
            '.Cells(x, 1).Interior.Color = RGB(241, 59, 59)
            ws.Range("A" & lastrow).Font.Color = RGB(241, 59, 59)
            ws.Range("B" & lastrow).value = addnewClient.priority_.Text
        End If

If Me.client = vbNullString Then
MsgBox "Must enter Clients name in order to proceed"
Exit Sub
ElseIf Me.client <> vbNullString Then

ws.Range("L" & lastrow).value = Format(Now(), "mm/dd/yyyy")

        ws.Range("A" & lastrow).value = addnewClient.client.Text
        ws.Range("A" & lastrow).Font.Name = "Arial"
        ws.Range("A" & lastrow).Font.Size = 18
        ws.Range("A" & lastrow).Font.Bold = True



        ws.Range("B" & lastrow).Font.Name = "Arial"
        ws.Range("B" & lastrow).Font.Size = 14
        ws.Range("B" & lastrow).HorizontalAlignment = xlCenter

        ws.Range("C" & lastrow).value = addnewClient.priority.Text

        ws.Range("C" & lastrow).Font.Name = "Arial"
        ws.Range("C" & lastrow).Font.Size = 14
        ws.Range("C" & lastrow).HorizontalAlignment = xlCenter

        ws.Range("E" & lastrow).value = addnewClient.contact.value
        ws.Range("E" & lastrow).Font.Name = "Arial"
        ws.Range("E" & lastrow).Font.Size = 14
        ws.Range("E" & lastrow).HorizontalAlignment = xlCenter


        ws.Range("G" & lastrow).value = addnewClient.result.Text
        ws.Range("G" & lastrow).Font.Name = "Arial"
        ws.Range("G" & lastrow).Font.Size = 14
        ws.Range("G" & lastrow).HorizontalAlignment = xlCenter


        ws.Range("I" & lastrow).value = addnewClient.segmentType.Text
        ws.Range("I" & lastrow).Font.Name = "Arial"
        ws.Range("I" & lastrow).Font.Size = 14
        ws.Range("I" & lastrow).HorizontalAlignment = xlCenter

        ws.Range("K" & lastrow).value = addnewClient.notes.Text

        If Me.contact = vbNullString Then
        ElseIf Me.contact <> vbNullString Then
            ws.Range("J" & lastrow) = Sheet3.Range("J" & lastrow).value + 1
            ws.Range("J" & lastrow).Font.Name = "Arial"
            ws.Range("J" & lastrow).Font.Size = 14
            ws.Range("J" & lastrow).Font.Bold = True
            ws.Range("J" & lastrow).HorizontalAlignment = xlCenter

        End If
        End If

    End With



    'With Sheet3
    'Sheet3.Range("A" & lastrow & ":K" & lastrow).Interior.Color = vbWhite
    Application.GoTo Range("A" & lastrow), True
    'End With

    wb.Sheets(2).Range("C4") = Format(Now, "mm/dd/yyyy")
    Unload Me

End Sub

Take a look at the following question: https://stackoverflow.com/q/50473365/8597922. — Victor K, Jul 30 '19 at 21:10
@Victor K how would that approach work for User Forms or code held at the Sheet Module Level? How would it trigger macros that are called from shapes? — PP8, Aug 01 '19 at 04:42
You can definitely include user forms in an add-in. Worksheet events can be handled as well but it might depend on you particular situation. — Victor K, Aug 01 '19 at 13:22
@Victor K for clarification purposes, would I then call the VBA code by using `Application.Run(ModuleName.SubName)` and reference everything to the `ActiveWorkBook`? — PP8, Aug 01 '19 at 15:04
Depend entirely on organization of code. You can have an .xlsm that has an add-in as a refence. Than you can use `ModuleName.SubName` or even `SubName`. Or you can open an add-in every time and have it reference `ActiveWorkbook`, or, conversely, have workbook use `Application.Run`. — Victor K, Aug 01 '19 at 15:19
@VictorK i think i am understanding a bit better. I'm going to update my question with some scenarios. Could you post an answer to those scenarios so I may mark it as the answer, I would like for you to get credit for your input. — PP8, Aug 01 '19 at 19:38
@VictorK I have added 3 different scenarios, I hope my edits make sense — PP8, Aug 01 '19 at 23:16
I will look into the scenarios that you provided and I will try to give an answer tomorrow. — Victor K, Aug 02 '19 at 01:03

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

One thing to know about any Userform is that it is just like any other class but with a UI element to it. That means that it follows a very similar set of rules to classes. If you look at the properties of a Class Module you will see a property called Instancing. VBA allows for two options: Private and PublicNotCreatable.
If you select PublicNotCreatable than you can use a class, but you cannot instantiate it outside of its project. Similar to your Scenario 1:

'in Project A:
Dim Cls as ProjectB.TestClass
Set Cls = New ProjectB.TestClass

I believe this will give you a compile error. This behavior is documented here along with a proposed solution, which I use myself, although it somehow a little 'hacky'. But it works and that's what Microsoft tells VBA programmers to do. This is similar to your Scenario 2:

'in ProjectB:
Public Function NewTestClass() as TestClass
     Set NewTestClass = New TestClass
End Function
'in ProjectA:
Public Sub InstantiateTestClass()
    Dim Cls as ProjectB.TestClass 
   'as long as there are no other classes with the same name in your references 
   'you can drop "ProjectB." prefix
    Set Cls = NewTestClass
End Sub

Note how I use NewTestClass, which is a function that returns an instance of a TestClass and it almost the same as usual instancing of Set xxx = New TestClass. So basically you need a function inside of the project that will return you an instance of the class in that same project to any outside project.
Given that every UserForm comes with one free instance your Scenario 2 works. You can rewrite it like this:

'in ProjectB:
Public Function NewUserForm() as UserForm1
    Set NewUserForm = New UserForm1
End Function
'in ProjectA:
Public Sub ShowUserForm()
    Dim View as ProjectB.UserForm1
    Set View = ProjectB.NewUserForm
    View.Show
End Sub

Now I would suggest to change that approach with UserForm1.Show as as explained here. I use a similar approach as advocated in that blog, which works great, but might take you down a rabbit hole of learning and writing things you didn't know you need in your code. Definitely made it easier to maintain code in my experience. You can take a look at some questions about the topic, like this one.

In Scenario 3 it appears to me that yes, you would need to reference an ActiveWorkbook the way it is written now. But I would strongly advocate to create a class, that will take a Workbook or a Worksheet(or a Shape or data or another class (preferably with an interface) or whatever it actually need to do its work) as an argument and take responsibility out of Button_Click event:

in ProjectB:

Public Function NewWorksheetManipulator() as WorksheetManipulator
    Set NewWorksheetManipulator= New WorksheetManipulator
End Function

Class WorkSheetManipulator:

Private ClientSheet as Worksheet
Private ManipulatedSheet as Worksheet
Public Property Set SheetClients(byval Value as WorkSheet)
    Set ClientSheet = Value
End Property
Public Property Set SheetToManipulate (byval Value as WorkSheet)
    Set ManipulatedSheet = Value
End Property
Public Sub DoStuff()
If ManipulatedSheet.FilterMode Then
    ManipulatedSheet.ShowAllData
    With ClientSheet.Shapes("priorities")
        .Fill.ForeColor.RGB = RGB(64, 64, 64)
    End With
End If
'etc...
End Sub

in ProjectA:

Public Sub Private Sub CommandButton1_Click()()
    Dim Manipulator as WorkSheetManipulator
    Set Manipulator = WorkSheetManipulator
    Set Manipulator.SheetClients = ActiveWorkbook.Sheets("clientmenu")
    Set Manipulator.SheetToManipulate = ActiveSheet
    Manipulator.DoStuff
End Sub

Now, I did not test this code, but in concept this is how you can make a more modular, portable and organized code. Please note that there are many things you can do better than in my example: Option Explicit, private field to back variables in classes, better names, etc.

Can Excel Vba code be updated via an Excel Add In for multiple users

1 Answers1

Linked