|

How to Iterate for a Solution with an Excel Macro: The Manning Formula for Open-Channel Flow

The 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…

|

How to Store Program Settings in the Registry from Excel Visual Basic

Windows stores program settings in the Registry, a Windows internal database.  You can use this to remember previous user choices and use them as defaults the next time.  We typically remember window positions, file paths, and other settings with the Registry. These three commands work with items stored in the Registry: SaveSetting appname, section, key,…

|

Useful Keywords to Know in Excel Visual Basic

1.        The Application Keyword Use “Application” to refer to Excel, e.g.: Application.ActivePrinter Name of the Active Printer.Application.Name            Name of the program running (“Microsoft Excel”).Application.Path            The path from which the program is running. 2.        ActiveControl Instead of the name of a specific control (e.g. txtEdit), you can use the keyword ActiveControl.  This will let your program refer…

|

A List of the Excel Visual Basic Editor Shortcut Keys

While you can pick these Shortcut Keys up from the menus in the Excel Visual Basic Editor, it is useful to have them all in one place. F1                         Help on the highlighted itemF2                         Object BrowserShift+F2               View Definition (Go to highlighted Sub or Function)F3                         Repeat last FindF4                         Display Properties WindowF5                         Run / Continue RunF8                         Single Step…

|

How to Make Your Own Excel Dialog Box with a Visual Basic UserForm

The built-in dialog boxes, MsgBox and InputBox$, are very useful for getting input from the user.  But sooner or later you will want to go further.  You might, while running code, want the user to make a choice from a list, or between several options.  For this, you want a UserForm.  For example: The program…

|

Other ActiveX Controls in Excel Worksheets

In an earlier post, we looked at connecting VBA code to command buttons on the Worksheet, the Quick Access Toolbar and the Ribbon.  What about the other ActiveX Controls: The Combo Box, List Box, or Check Box?  How about Option Buttons, the Scroll Bar, Spin button, Toggle button, etc.?  That’s coming up in this post….

|

How to Use Objects, Collections, Properties, Events and Methods in Excel Visual Basic

1.        What are Objects and Collections? Visual Basic defines a number of what it calls Objects. Examples of Objects are: In object-orientated terminology, Objects belong to Classes. Collections are groups of Objects. Here are some examples of Collections that Visual Basic defines: 2.        The Excel Object Model Excel, too, has objects of its own.  When…

| |

Built-in Functions in Excel Visual Basic for Applications (VBA)

Before 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…