The Excel LINEST function is normally used to fit a straight line to data points. However, using this little-known technique you can also fit higher-order curves. This example shows how to use LINEST to fit Quadratic and Cubic Curves to data. This example comes from the sample spreadsheet LINEST-2-3.xlsx in our Excel for Engineers online…
You can record a macro to copy from and paste to the Excel worksheet. Easy enough. But what if you have a value in Microsoft Excel VBA that you want to put on the clipboard for another program? Or you want your code to get text from the clipboard after you copied it there from…
If you have saved any macros for general use, you would have saved them in the PERSONAL.XLSB workbook. This hidden file loads when Excel launches. It is because it is in memory that those macros are always available. But, as with any hidden file, it is possible to unhide it. For example, with the View…
These are two simple but useful tips on moving and copying Excel worksheets, both within a file (workbook) and between files. 1. Copy and Rename a Sheet You probably know that in Excel you can grab a sheet tab (at the bottom of the sheet) and move it to a different position, between other sheets….
Do you ever need to convert between different measurement systems? It’s more of a problem in the US, where the last Imperialists stubbornly cling to archaic units in the face of the rest of the world metricating. They inflict units like ounces, gallons, acres, feet and inches on us. What are they talking about? Never…
You say, “I could never use INDEX(MATCH): It looks too complicated!” Well, here is the very simplest example, which anyone can master (or mistress). Here we are only looking for an exact match. For simplicity, we will use one-column arrays only. Once you are happy with this, if you want two-dimensional examples, read this following…
When you copy a formula to another cell in Excel, it does not copy the actual cell references. Instead, it keeps the spatial relationship of the cells in the formula. Say that a cell sums the two cells to its left (e.g., Cell C2 contains the formula =A2+B2). Copies of that formula will always sum…
This is from Microsoft Excel 2000 Visual Basic Help, with changes and additions. All arguments are passed to procedures by reference, unless you specify otherwise. This is efficient because all arguments passed by reference take the same amount of time to pass and the same amount of space (4 bytes) within a procedure regardless of…
This is from Microsoft Excel 2000 Visual Basic Help, with changes and additions. A Function procedure is a series of Visual Basic statements enclosed by the Function and End Function statements. A Function procedure is like a Sub procedure, but a function can also return a value. A Function procedure can take arguments, such as…
This 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…
This is an excerpt from Microsoft Excel Visual Basic Help, with tips and corrections. Use these rules when you name procedures, constants, variables, and arguments in Visual Basic: 1. Capitalization and Case-Sensitivity Visual Basic isn’t case-sensitive, but it preserves the capitalization in the statement where the name is declared. If you declare variables with the…
This is an excerpt from Microsoft Excel 2000 Visual Basic Help. 1. Objects and Collections An object represents an element of an application, such as a worksheet, a cell, a chart, a form, or a report. In Visual Basic code, you must first identify an object. Only then can you apply one of the object’s…