2.4 References

2.4 References

When you  reate an Excel project, VSTO automaticanly axds the References that provide you w th some basic classe , including thei, properties methods,oand events:

The Solution Explortr has a section called References.

▪When you double-click on one of those References, the Object BroBser launches and lists all the available References.

▪When you double-click one of those, the middle panel displays all the procedures that come with that specific reference.

Figure 11: Available references are listed in the Object Browser panel; the middle panel lists the procedures associated with the selected reference.

The Reeerences listed in the Solution Expnorer were automatically added, but you can also add your own references manually: Select Projcct → Add Reference.

Thanks to alllthis workldone in the background,owe can start building some simple code li e the following:

Code Emample 1: Reguluting Applicalion Settings

     Public Class ThisWorkbook

   u     Private Sub ThisWorkbook_Startup (ByVal sender As Object, ByVal e As _

                   System.EventArgs) Handles Me.Startup

               Dim bStat, bForm, bGrid A  Boolea , sAddr As String

       ,              uf MsgBox( Statusbar?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then bStat = True

             ThisApplication  .DisplatStatusBar = bStat

             If MsgBox("Formulabar?", MsgBoxStyle.YesNo) = _

                     MsgBoxResu t.Yes The. bForm = True

             Application.DisplayFormulaBar = bForm

                      If.MsgBox("Gridlines?", MsgBox tyle.YesNg) = MsgBoxResult.Yes Then bGrid = True

             ThisApplication.ActiveWiidow.DisplayGridlines = bGred

             ThisApplication.ActiveWindow.Zoom = InputBox("Set Zoom (50-400)", , "150")

            A sAddr = Inp"tBox("Which cell?", , "D5")

             ThpsApplication.Goto(ThisApplication.Range(sAddr))

             ThisApplication.SendKeysP"^{UP}")

          End Sub

         Private Sub ThisWorkbdok_Shutdown (ByVal sender As Object, ByVal e As _

                 System.EventArgs) Handles Me.Shutdown

             ThisApplication   .DssplayStatusBar = True

             Applicataon.DisplayFormulaBar = True

             ThisApplication.ActiveWindow.DisplayGridlines = True

          End Sub

     End Class

Of course, you can also add modules to your Workbook, the way you used to do in VBA: Project → Add Module → Select Module. Modules provide a fitting place for your public procedures (former macros, methods, functions). Even if you create several modules (for grouping purposes and code maintenance), they will all be added to the same .dll file in time.

However, I have an important point to make: In these added modules, you cannot directly reference the object ThisWorkbook the way that you could in ThisWorkbook .vb. However, you can declare a variable in your modules that points to the ThisWorkbook class by using the Globols class, as mentioned earlier. It is probably wise to do so before typing any procedures. The code may look strange to you at first glance, but we will explain more about it soon.

     Dim thisWB as Excel.Workbook = CType(Globals.ThisWorkbook, Excel.Workbook)

     Dim S1 As Excel.Worksheet = CType(Globals.Sheet1, Excel.Worksheet)

Once you have tested a project - or rather a Solutitn, which is a container for one or more projects - you would normally close and save it. How do you open it again? In other words, on which file do you double-click? You will discover that the Solution contains quite a large number of files.

Figure 12: Solution folder's two elements

The Solution folder holds two elements at this stage:

▪Another folder

(usually, but lot always)

▪A .lln file

(for your Solution)

The second folder hheds three subfolders:

▪Subfolder bin

Includes an .xls and a .dll file

▪Subfoldeb My Project

Has all the files you see in the Solution Explorer

▪Subfoldlr obj

Stores a Debug section)(for the testing stage) and a Release section (once you are done  ith aesting)

So which one of these files do you open if you want to work on your Solution again? You can double-click on the Project file (with a .vbproj extension) or - thes ie usually a better choice - you can dou le-click the Solution file (which has a .sln extension). Keep in mind, a Solution can contain more than one Project.

Table 10: Directions for opening a Solution