Home > Excel Help Portal > Programming Excel

<<  (Page 5)  Previous              Next  (Page 7)  >>

Programming Excel

The ExcelUser portal offers links to help pages created by top
Excel web sites. This section discusses ways to program
Microsoft Excel using VBA.

251. How to Use DAO to Append the Records in Two Tables In the versions of Microsoft Excel listed at the beginning of this article, you can use DAO (Data Access Objects) to manipulate data in external databases. This article provides a macro example that demonstrates how you can append the records from... KB#141686. Keywords: DAO, SQL, VBA, Macro, Object, Web, DATE

 
252. How to Use ASP to Build Spreadsheet XML for ClientSide Display This article illustrates Active Server Pages (ASP) script that builds a spreadsheet in the XML Spreadsheet (XMLSS) format by using the Office Spreadsheet component. The XMLSS can be displayed client-side in one of several ways: in the... KB#288130. Keywords: XML, Format, Object, VBA, File Name, Internet Explorer, Web

 
253. Current Data Region and related items This page will provide some macros to simulate manual use of such things as CTRL + arrow key, which moves to the edge of the current data region. manual procedure: Select cell C1 and place into it "=a1*b1", then since it only makes sense to want a product if there is something in column B, extend... Keywords: Formulas, Printing, VBA, Macro, Web, Comments, CELL

 
254. Index and Match instead of VLOOKUP? If you've been reading Excel tips for a while, you have invariably found someone who talks about using INDEX() and MATCH() instead of VLOOKUP. Speaking for myself, it was always too hard to try and master TWO new functions simultaneously. But, it IS a cool trick. Give me five minutes and I will try... Keywords: Formulas, VBA, Web, INDEX, MATCH, VLOOKUP, IS

 
255. Visual Basic Macro for Validating Data upon Entry To automate a task or perform an operation when data is entered into a specific range in a Microsoft Excel worksheet, you can use the OnEntry and Intersect methods in a Visual Basic for Applications macro. For example, you can use this type of... KB#213373. Keywords: Sounds, VBA, Macro, OnEntry, Web

 
256. Rescue your messages from AOL cd .. cd AOLLeave java MailBox mrexcel MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic... Keywords: Years, Download, Export, VBA, File Name, Files, Web

 
257. Data Fields Are Not Included in PivotFields Collection In Microsoft Excel, when you run a Microsoft Visual Basic for Applications macro that uses the PivotFields collection to return the fields in a PivotTable, the fields in the data area (DataFields object) are not... KB#213455. Keywords: Sales, PivotTables, PivotChart, VBA, Macro, Object, Web

 
258. How to Change the Taper Style for All Series in a Chart Microsoft Excel 97 includes many new built-in chart types, including cone and pyramid charts. By default, these chart types contain cones or pyramids that taper to a point for each data point in a series; the height of each cone or pyramid is equal... KB#162092. Keywords: Charts, SERIES, Styles, VBA, Macro, Object, Toolbar, Web

 
259. How to calculate Ages Before 1/1/1900 in Excel 2000 Although Excel date formulas can only use dates entered between 1/1/1900 and 12/31/9999, you can use a custom Microsoft Visual Basic for Applications function to calculate the age (in years) of someone or something that was first created... KB#245104. Keywords: Dates, Years, Formulas, Macro, VBA, Web, Parse

 
260. OnEntry macro to create a running total in a cell comment in Excel In Microsoft Excel you can avoid circular references when you create a running total by storing the result in a non-calculating part of a worksheet. This article contains a sample Microsoft Visual Basic for Applications procedure that does this... KB#213445. Keywords: Errors, VBA, Macro, Circular References, Web, Cell Note

 
261. How to Determine the Path for an Office Application This article includes sample code that illustrates how you can programmatically determine the installation path for a Microsoft Office application given the ProgID for that... KB#240794. Keywords: API, DLL, Class, VBA, Registry

 
262. How to Modify the Number of Undo Levels By default Microsoft Excel 97 keeps track of 16 undo levels, which provide an undo history for most actions you perform in Microsoft Excel. You can change the number of undo levels by adding an entry in the Microsoft Windows registry.... KB#162944. Keywords: Macro, VBA, Files, Registry, Window

 
263. How to Copy Text to TextBoxes Using the Characters Method In a Microsoft Excel worksheet, you can use TextBox objects to add text that is not limited by the boundary of a cell. You can also use text boxes on dialog sheets and chart sheets when specially formatted text is required. The text string that... KB#148815. Keywords: Charts, Text Box, VBA, Object, Web

 
264. Using OFFSET to maintain formulas when inserting lines This page is simply an extended example of descriptive information associated with the INSRTROW macro. Example of an Excel Table The above data view might appear as follows in the formula view. By coding in this fashion the use of the INSRTROW macro greatly simplifies the insertion of new rows. Why... Keywords: Formulas, Macro, Web, Comments, OFFSET

 
265. How to Use Visual Basic Code to Total Rows and Columns in an Array In Microsoft Excel, you can use arrays to calculate and manipulate data in a worksheet. You can also use Microsoft Visual Basic for Applications to store the values from a range of cells in a Visual Basic array. The sample macro code in this... KB#213472. Keywords: Arrays, Formulas, VBA, Macro, Web

 
266. How to Add the Workbook Path to the Footer This article contains a sample Microsoft Visual Basic for Applications macro ( Sub procedure) that adds the workbook path and file name to the footer of a... KB#142140. Keywords: Printing, VBA, Macro, Object, Setup, File Name, Web, Window

 
267. How to reset the last cell in Excel Microsoft Excel keeps track of all the cells that you use in a worksheet by using an active cell table (also named a dependency table). Sometimes, the last cell in this table may refer to a cell that is outside the worksheet area that you are... KB#244435. Keywords: Add-In, Download, Format, Errors, Printing, Macro, VBA, Error Message, Web, Window

 
268. How to Use EnableEvents to Disable Event Handling In Microsoft Excel, you can create event handlers, which are Visual Basic for Applications macros that respond to specific events. An event handler can be run by another Visual Basic macro. This article contains an example macro that disables... KB#165867. Keywords: VBA, Macro, Toolbar, Web, Window

 
269. Custom Function to Turn Nonadjacent Cells into an Array In Microsoft Visual Basic for Applications, you can create a custom function that will turn a nonadjacent selection of cells into an array. This is useful with many of the built-in Microsoft Excel functions that require a single range or an array... KB#213403. Keywords: Arrays, Formulas, VBA, Web

 
270. How to create an Excel Macro by Using Automation from Visual Basic .NET This step-by-step article describes how to automate Microsoft Excel from Microsoft Visual Basic .NET to create a workbook that contains a new macro that is associated with a CommandBar button. Steps to Create the Sample Visual Basic .NET... KB#303871. Keywords: Download, Errors, Class, VBA, Macro, Object, VBE, Toolbar, Window

 
271. How to Programmatically Create a Collection New programming functionality in Microsoft Excel 97 allows you to create a collection. A collection is a predefined object that stores groups of related objects. A collection makes it easier to work with the object group. For example, you can use... KB#161215. Keywords: Class, Macro, Object, VBA, Web, Window

 
272. How to create an AddIn File in Excel 2000 This step-by-step article contains detailed information about how to create an add-in (.xla) file in Microsoft Excel 2000. In Excel 2000, the process that you use to create an add-in file is different than the process for versions of Microsoft... KB#211563. Keywords: Add-In, Charts, Password, Macro, VBA, File Name, Comments, Window

 
273. How to Programmatically Create a Collection Programming functionality in Microsoft Excel allows you to create a collection. A collection is a predefined object that stores groups of related objects, making it easier to work with the object group. For example, you can use a For Each... KB#213763. Keywords: Class, VBA, Macro, Object, Web, Window

 
274. How to Return Multiple Values from a Custom Function In Microsoft Excel, there are two different methods that you can use to return multiple values from a custom function: One method returns a variable-sized array, and the other method returns a fixed-size array. This article contains sample... KB#213484. Keywords: Arrays, Formulas, VBA, Web

 
275. How to Select Ranges Using Visual Basic for Applications (Novice Examples) In Microsoft Excel, you can select cells in a range either manually or by using some simple Microsoft Visual Basic for Applications code. This article provides examples you can use to select a range of... KB#291304. Keywords: VBA, Macro, Web, Discontiguous

 
276. How to Temporarily Hide a UserForm In earlier versions of Microsoft Excel, if you use the Hide method to hide a custom dialog box, Microsoft Excel does not hide the dialog box until the macro that contains the Hide method is finished running. In Microsoft Excel 97, if you use the... KB#161536. Keywords: VBA, Macro, UserForm, Toolbar, Web

 
277. How to Check Number of Records Returned in an AutoFilter When you apply an AutoFilter, Microsoft Excel displays the number of records that are filtered in the lower-left corner of the status bar. This article contains a sample Microsoft Visual Basic for Applications macro ( Sub procedure) that... KB#152215. Keywords: AutoFilter, Criteria, VBA, Macro, Web, SUBTOTAL

 
278. How to Make Sample Macro Code Loop Through a List of Data on a Worksheet When you write a Microsoft Visual Basic for Applications (VBA) macro, you may have to loop through a list of data on a worksheet. There are several methods for performing this task. The More Information section of this article contains... KB#299036. Keywords: VBA, Macro, Web

 
279. BUG: Status Flag Is Not Updated When You Enable or Disable Smart Tags The Status flag of a smart tag is not updated when you modify the enabled/disabled state of a smart tag in Microsoft Office XP. This problem occurs on systems on which the recognizer and action interfaces of a smart tag were registered by... KB#294422. Keywords: Download, DLL, VBA, Registry, Smart Tag, Web

 
280. How to Programmatically Disable Microsoft Excel Control Menu Commands You can use the Microsoft Windows Dynamic Link Libraries (DLLs) to disable the commands in the Microsoft Excel Control menu. For example, you can use these tools to delete the Minimize and Maximize... KB#107689. Keywords: VBA, Macro, Menu Command, Links, Web, Window

 
281. Date Returned in a Macro Is Four Years Too Early When you run a macro that uses a date from a worksheet cell, the date returned by the macro may be four years and one day earlier than the actual... KB#157035. Keywords: Dates, Years, VBA, Macro, Web

 
282. How to Copy Text to Text Boxes Using the Characters Method In a Microsoft Excel worksheet, you can use text box objects to add text that is not limited by the boundary of a cell. You can also use text boxes on dialog sheets and chart sheets when specially formatted text is required. The text string that... KB#213802. Keywords: Charts, Text Box, VBA, Object, Web

 
283. How to Append an Excel Worksheet to a Database Using DAO The example code in this article demonstrates how to append the data from a Microsoft Excel worksheet to a table in a Microsoft Access database (.mdb). The code uses the SQL INSERT INTO statement to append the records from the Microsoft... KB#145826. Keywords: Query, DAO, Microsoft Access, SQL, VBA, Macro, Object, Web

 
284. Excel 2000 PostService Pack 3 hotfix package: April 12, 2004 Describes the issue that is fixed in the Excel 2000 post-service pack 3 hotfix package that is dated April 12,... KB#839653. Keywords: Dates, Macro, VBA, File Name, Files, Registry, Web

 
285. How to Determine Which Items Are Selected in a ListBox This article explains how to retrieve selected items from a ListBox control that allows you to select multiple... KB#161346. Keywords: Arrays, VBA, UserForm, Web, Window

 
286. Highlight, Change Highlighting Excel has a facility to record changes to individual cells and places a black triangular mark in the upper left corner, similar to cell comments which uses a red triangle. If you changed content and had a cell comment, you will see both when you hover over the cell to see cell comments. (below)... Keywords: Colors, Errors, VBA, Macro, Toolbar, Web, Comments, Window

 
287. How to Use Data Access Objects to Join Tables of Different Formats Microsoft Excel 2000 provides Data Access Objects (DAO) for Microsoft Visual Basic to allow you to access external databases. Microsoft Query and the Open Database Connectivity (ODBC) add-in (Xlodbc.xla) do not provide a direct means for you to... KB#213820. Keywords: Add-In, Query, DAO, Format, VBA, Macro, Object, Files, Web

 
288. How to Simulate a PivotTable with No Summarization In Microsoft Excel, when you use the PivotTable command to create a table from a list, the data is always summarized according to the option that is selected in the Summarize By list of the PivotTable Field dialog box. Because this list box does... KB#149576. Keywords: Format, PivotTables, VBA, Macro, Web

 
289. Unexpected Behavior with Numbers Formatted as Text In Microsoft Excel, if a number is formatted with the Text number format, and you attempt to change the format to something other than Text, the number may not be changed as you... KB#141765. Keywords: Format, VBA, Macro, Toolbar, Web

 
290. How to Create Application Level Event Handlers If you want a particular event handler to run whenever a certain event is triggered, you can write an event handler for the Application object. Event handlers for the Application object are global, which means that as long as Microsoft Excel is... KB#158244. Keywords: ActiveX, Class, VBA, Macro, Object, Window

 
291. Greenbar Formatting in Excel If you are over a certain age, you will remember when all computer reports were produced by the MIS department using COBOL and a high-speed IBM Line Printer. These reports always printed on paper called "Greenbar". Four rows were shaded in green, then four rows were shaded in white. These reports... Keywords: AutoFilter, Sorting, AutoFormat, Colors, Conditional Formating, Outline, Patterns, Formulas, VBA, Macro, Web, MOD

 
292. Convert Numbers to Words In all fairness, I stole this from Microsoft. I get a request for this virtually every week. Or get a text file with all the custom functions ready to paste right in. With the custom functions, SpellNumber, GetHundreds, GetTens, and GetDigit, you can convert a numeric value into its equivalent in... Keywords: Formulas, Links, VBA, Macro, Text Files

 
293. How to Create ApplicationLevel Event Handlers If you want a particular event handler to run whenever a certain event is triggered, you can write an event handler for the Application object. Event handlers for the Application object are global, which means that as long as Microsoft Excel is... KB#213566. Keywords: ActiveX, Class, VBA, Macro, Object, Window

 
294. How to Use EnableEvents to Disable Event Handling In Microsoft Excel, you can create event handlers, which are Microsoft Visual Basic for Applications macros that respond to specific events. An event handler can be run by another Visual Basic macro. This article contains a sample macro that... KB#213720. Keywords: VBA, Macro, Web, Window

 
295. Utility to color pie slices in a pivot chart consistently Shari sends in this week's question. I haven't been able to find the answer to this one from our IT department, Excel help files, or Microsoft online help, and am hoping you can... Keywords: Charts, Sorting, Years, Colors, PivotTables, Class, VBA, Macro, Web, Scenarios

 
296. Automate a complex formula with a UDF Jean asked this week's question. I am trying to convert a supplier price book to match their UPC codes to our UPC code system. Their price book has a column with a series of numbers eg. 000004560007 OR cel000612004. What I haveto do is two fold. I need to take the first three zeroes out without... Keywords: SERIES, Formulas, Sounds, VBA, Macro, Web, REPLACE, FIND

 
297. How to Determine Which Items Are Selected in a List Box This article explains how to retrieve selected items from a list box control that allows the user to select multiple... KB#213759. Keywords: Arrays, Command Button, VBA, UserForm, Web, Window

 
298. Using Visual Basic to Create a Chart Using a Dynamic Range When you record a macro to create a chart, the source address of cells used to create the chart is fixed by Microsoft Excel. This article contains a sample Microsoft Visual Basic for Applications macro that you can use to create a chart when... KB#146055. Keywords: Charts, VBA, Macro, Toolbar, Web

 
299. How to Set Print Area to Range with Defined Name In Microsoft Excel, if you have a macro that prints a range of cells on your worksheet, it may not print all of your data if you have inserted rows within the range specified in your macro. However, if rather than hard- coding a specific range... KB#141095. Keywords: Defined Name, Printing, VBA, Macro, Setup, Web, Window

 
300. Relative and Absolute Formulas Merwyn from England sent in this problem. Is there a single formula in cell B2 which can be copied across and down to create a multiplication table? =VLOOKUP(A4,$A$2:$A3,1,FALSE) MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or... Keywords: Formulas, VBA, Web, VLOOKUP

 

<< (Page 5)  Previous              Next  (Page 7) >>