Excel Visual Basic Variables, Part 2: Declaring and Naming Variables, Scope and Lifetime
Subscribe to continue reading
Subscribe to get access to the rest of this post and other subscriber-only content.
Similar Posts
The Excel Object Model in Detail
ByRickWe had an earlier post, How to Use Objects, Collections, Properties, Events and Methods in Excel Visual Basic. There, we introduced the idea of Objects and Collections. We listed the most useful Objects, and Collections of Objects. Below we have more extensive diagrams, specifically for the Excel Object Model. It helps to understand the hierarchy:…
How to Iterate for a Solution with an Excel Macro: The Manning Formula for Open-Channel Flow
ByRickThe Manning formula for open-channel flow is: whereV= Velocityn = Manning’s “n”,r = Hydraulic radius = area / wetted perimeter = b*y / (2*y + b) for a rectangular channel (where y = depth and b = width), ands = slope of channel (m/m). Discharge Q = V*A = V*b*y. We cannot solve this…
Built-in Functions in Excel Visual Basic for Applications (VBA)
ByRickBefore Excel 2003, you could get a complete list of Visual Basic Functions by looking in Visual Basic Help > Contents > Functions. In Excel 2010, entering “Functions” into the Help Search box produces nothing useful. Using a specific category, e.g. “Math Functions”, may be more productive. Please be aware of what references to “Excel…
Should I use a VBA Macro Function or Call a Worksheet Function?
ByRickIf the function you want to use exists in Excel VBA, use that. It will be faster and cleaner, and work in all Microsoft Office programs, not only in Excel. Here is a full list of Excel VBA Functions. If the function you want is not there, but exists in the Worksheet, you may be…
Meet the Excel Visual Basic Macro Editor
ByRickThe macros you recorded in the previous post are stored in an Excel file. To edit it you use what looks like a separate program, the Visual Basic Editor. Open it with the Developer ribbon (Code group) > Visual Basic Editor (Alt+F11). You will find newly-recorded macros under Modules in the explorer on the left…
How to Understand Visual Basic Syntax
ByRickThis is an excerpt from Microsoft Excel Visual Basic Help, with extra Notes. The syntax in a Visual Basic Help topic for a method, statement, or function shows all the elements needed to use the method, statement, or function. The examples in this topic explain how to interpret the most common syntax elements. 1. Activate…