VBA and comments

Comments in Excel are a convenient way to leave notes about the results of the calculations you made or otherwise explain your work within the worksheet. VBA allows you to easily add and delete comments. The following text will describe how to create programs by which you add or delete comments from within an active cell.

VBA and SQL Server

In several previous “recipes” I wrote about how to connect to Microsoft SQL Server from Excel in order to retrieve data to a worksheet. These were all “innocent” operations, since you could only read the data from the database, not to change them. Sometimes it is necessary to create a program that performs more complex manipulation over the database by running appropriate SQL query, which will be discussed in the text that follows.

Excel functions in VBA

The VBA language can use two types of functions: those inherent to it and functions that already exist in Excel. Although VBA has a large number of functions in Excel there are even more of them. And since we don’t want,  as the saying goes, to „invent the hot water“ it’s often better to use one of the existing functions to finish the job. This is accomplished using the Application.WorksheetFunction method that allows us to call any available Excel function.

Cells and ranges manipulation

Range and Cells objects are used to manipulate Excel ranges and cells. These objects have many useful properties and methods that can affect the content and formatting of individual cells or ranges within a worksheet, whether the active ones or those which we explicitly specify. VBA allows you to manipulate individual cells, ranges or named ranges …

Worksheets and Sheets

Worksheets and Sheets are object collections that are identical with a few small exceptions. Their task is to manipulate objects within a worksheet, and which one we’ll use is often a matter of personal choice. In the text that follows you will become familiar with the most commonly used properties and methods that allow counting, adding, changing names and other tools for manipulating worksheets.