Databases Reference
In-Depth Information
SUMMARY
Functions are the building blocks of modern analyses. This chapter covers utilizing both Worksheet
functions and VBA code functions. It also delves into specialized use of functions, such as calling
VBA coded functions from Worksheet cells and populating Worksheet cells with custom functions
using VBA. The reader is also shown how to add additional “built-in” functions to Excel. It is
functions that allow specialized dynamic formatting of Worksheets, which add a new level of
complexity and flexibility when creating custom reports. The use of the Macro Recorder is also
covered. The Macro Recorder allows the user to capture the code required to perform a specific
process, provided the user knows how to carry out this process manually. Once the code required
for a certain task is known, the user is then free to create a generic function to accomplish the task
at will within VBA. The chapter concludes with two sample applications that automate two of the
more common analyses: linear regression and polynomial regression. Not only are functions utilized
to perform the regressions but the user is shown how to extract the parameters of interest generated
from the function calls that perform the regressions. Tools such as those shown can then be
constructed by the user to accomplish a variety of custom analyses quickly and efficiently.
