excel - Sub function to show UserForm -

i have excel file multiple userforms. open userform have code such as

sub runadjuster()    adjuster.show end sub 

there 5 of these. considered best practice in terms of code should kept? had in module, have decided move thisworkbook object. looking tips on done keep code clean.

assuming adjuster name of form, you're using default instance here, isn't ideal.

this better:

dim view adjuster set view = new adjuster view.show 

yes, it's more code. you're using dedicated object (i.e. view) and, if object's state gets modified, these changes aren't going affect default instance. think of default instance global object: it's global, isn't oop.

now, may argue, why not "new up" object on same line declaration then?

consider this:

sub dosomething()     dim c new collection     set c = nothing     c.add "test" end sub 

is code accessing null reference , blowing run-time error 91? no! confusing? yes! hence, avoid as new shortcut, unless having vba automagically doing implicit stuff behind back.

---

so, you're asking best practice... tend consider vba userforms pre-.net version of winforms, , best practice design pattern winforms model-view-presenter pattern (aka "mvp").

following pattern, you'll have userforms strictly responsible presentation, , you'll have business logic either implemented in presenter object, or in dedicated object presenter uses. this:

class module: mypresenter

the presenter class receives events model, , executes application logic depending on state of model. knows concept of view, doesn't have tightly coupled concrete implementation (e.g. myuserform) - proper tooling write unit tests validate logic programmatically, without having run code , display form , click everywhere.

option explicit  private type tpresenter     view iview end type  public enum presentererror     err_modelnotset = vbobjecterror + 42 end enum  private withevents viewmodel mymodel private tpresenter  public sub show()     if viewmodel nothing         err.raise err_modelnotset, "mypresenter.show", "model not set object reference."     end if     'todo: set model properties     view.show     if not view.iscancelled dosomething end sub  public property view() iview     set view = this.view end property  public property set view(byval value iview)     set this.view = value     if not this.view nothing set this.view.model = viewmodel end property  public property model() mymodel     set model = viewmodel end property  public property set model(byval value mymodel)     set viewmodel = value     if not this.view nothing set this.view.model = viewmodel         end property  private sub class_terminate()     set this.view.model = nothing     set this.view = nothing     set viewmodel = nothing end sub  private sub viewmodel_propertychanged(byval changedproperty modelproperties)     'todo: execute logic needs run when changes in form end sub  private sub dosomething()     'todo: whatever needs happen after form closes end sub 

class module: iview

that's abstraction represents concept of view exposes presenter needs know userform - note everything needs know, isn't much:

option explicit  public property model() object end property  public property set model(byval value object) end property  public property iscancelled() boolean end property  public sub show() end sub 

class module: mymodel

the model class encapsulates data form needs , manipulates. doesn't know view, , doesn't know presenter either: it's container encapsulated data, simple logic enables both view , presenter execute code when of properties modified.

option explicit  private type tmodel     myproperty string     someotherproperty string     'todo: wrap members here end type  public enum modelproperties     myproperty     someotherproperty     'todo: add enum values here each monitored property end enum  public event propertychanged(byval changedproperty modelproperties) private tmodel  public property myproperty() string     myproperty = this.myproperty end property  public property let myproperty(byval value string)     if this.myproperty <> value         this.myproperty = value         raiseevent propertychanged(myproperty)     end if end property  public property someotherproperty() string     someproperty = this.someotherproperty end property  public property let someotherproperty(byval value string)     if this.someotherproperty <> value         this.someotherproperty = value         raiseevent propertychanged(someotherproperty)     end if end property  'todo: expose other model properties 

userform: myuserform

the userform strictly responsible visual presentation; event handlers to, change value of property in model - model tells presenter "hey i've been modified!", , presenter acts accordingly. form listens modified properties on model, when presenter changes model, view can execute code , update accordingly. here's example of simple form "binding" myproperty model property text of textbox1; added listener someotherproperty illustrate view can updated indirectly when model changes.

obviously view wouldn't reacting same properties changing presenter, otherwise enter endless ping-pong of callbacks blow stack... idea.

note form implements iview interface, presenter can talk without knowing inner workings. interface implementation refers concrete members, concrete members don't need exist, since won't used!

option explicit implements iview  private type tview     iscancelled boolean end type  private withevents viewmodel mymodel private tview  private property iview_model() object     set iview_model = model end property  private property set iview_model(byval value object)     set model = value end property  private property iview_iscancelled() boolean     iview_iscancelled = iscancelled end property  private sub iview_show()     show vbmodal end sub  public property model() mymodel     set model = viewmodel end property  public property set model(byval value mymodel)     set viewmodel = value end property  public property iscancelled() boolean     iscancelled = this.iscancelled end property  private sub cancelbutton_click()     this.iscancelled = true     me.hide end sub  private sub okbutton_click()     me.hide end sub  private sub userform_queryclose(cancel integer, closemode integer)     '"x-ing out" of form clicking cancel button     if closemode = vbqueryclose.vbformcontrolmenu         this.iscancelled = true     end if end sub  private sub userform_activate()     if viewmodel nothing         msgbox "model property must assigned before view can displayed.", vbcritical, "error"         unload me     else         me.textbox1.text = viewmodel.myproperty         me.textbox1.setfocus     end if end sub  private sub textbox1_change()     'ui elements update model properties     viewmodel.myproperty = me.textbox1.text end sub  private sub viewmodel_propertychanged(byval changedproperty modelproperties)     if changedproperty = someotherproperty         frame1.caption = someotherproperty     end if end sub 

module: macros

say spreadsheet had shape , wanted run logic when it's clicked. need attach macro shape - regroup macros in standard module (.bas) called "macros", contains nothing public procedures this:

option explicit  public sub dosomething()      dim presenter mypresenter      set presenter = new mypresenter      dim themodel mymodel     set themodel = new mymodel      dim theview iview     set theview = new myuserform      set presenter.model = themodel     set presenter.view = theview     presenter.show  end sub 

now, if want test presenter logic programmatically without showing form, need implement "fake" view, , write test method need:

class: myfakeview

option explicit implements iview  private type tfakeview     iscancelled boolean end type  private tfakeview  private property iview_model() object     set iview_model = model end property  private property set iview_model(byval value object)     set model = value end property  private property iview_iscancelled() boolean     iview_iscancelled = iscancelled end property  private sub iview_show()     iscancelled = false end sub  public property iscancelled() boolean     iscancelled = this.iscancelled end property  public property let iscancelled(byval value boolean)     this.iscancelled = value end property 

module: testmodule1

there other tools out there, since wrote 1 , how works without crap ton of boilerplate setup code or comments contain executable instructions i'm going warmly recommend using rubberduck unit tests. here's [very simple] test module might like:

'@testmodule option explicit option private module private assert new rubberduck.assertclass  '@testmethod public sub model_somepropertyinitializesempty()     on error goto testfail      'arrange     dim presenter mypresenter      set presenter = new mypresenter      dim themodel mymodel     set themodel = new mymodel      set presenter.model = themodel     set presenter.view = new myfakeview      'act     presenter.show      'assert     assert.istrue themodel.someproperty = vbnullstring  testexit:     exit sub testfail:     assert.fail "test raised error: #" & err.number & " - " & err.description end sub 

rubberduck unit tests allow use decoupled code test want test application logic - long keep application logic decoupled , write testable code, you'll have unit tests document how vba application supposed behave, tests document specs - have them in c# or java, or other oop language 1 can write unit tests with.

point is, vba can it, too.

---

overkill? depends. specs changes time, code changes accordingly. implementing application logic in spreadsheets' code-behind gets utterly annoying, because the project explorer doesn't drill down module members, finding what's implemented can annoying.

and it's worse when logic implemented in forms' code-behind , have button_click handlers making database calls or spreadsheet manipulations.

code that's implemented in objects have few responsibilities possible, makes code that's reusable, , that's easier maintain.

your question isn't precise mean "an excel file multiple userforms", if need to, have "main" presenter class receives 4-5 "child" presenters, each being responsible specific logic tied each "child" form.

that said, if have working code (that works intended) refactor , make more efficient, or easier read/maintain, can post on code review stack exchange, that's site for.

---

disclaimer: i maintain rubberduck project.