Home > Excel Help Portal > Programming Excel

<<  (Page 15)  Previous              Next  (Page 17)  >>

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.

751. How to add a Digital Signature to a Custom Macro Project in an Office Program This article describes how to add a digital signature to your macro project. You digitally sign a file or a macro project, so that users who run the macro can be assured of the identity of the creator of the macro and that the macro has not... KB#307731. Keywords: Macro, VBA

 
752. "Out of Virtual Memory" When You Open and Close Many Workbooks with Controls While working in Microsoft Excel, available virtual memory decreases and you may experience one or more of the following symptoms: A slowdown in computer performance Out of Virtual Memory messages Microsoft Excel may stop responding... KB#248180. Keywords: ActiveX, Errors, VBA, Object

 
753. Return End Of Month Date There are a few ways to solve many problems in Excel. I am sure someone will come up with something simpler, but at the moment, this formula comes to mind:=DATE(YEAR(E1),1+MONTH(E1),1)-1The Date() function generally accepts three arguments: A year, a month, and a day. So, December 13 2001 could be... Keywords: Formulas, VBA, Web, DATE

 
754. UserDefined Function to Place Sheet Name in a Cell To display the name of a worksheet in a cell, create a user-defined function similar to the sample functions in the More Information section of this... KB#213475. Keywords: VBA, Macro, File Name, Web

 
755. How to Create a Macro That Opens Multiple Selected Files In the Open dialog box in Microsoft Excel, you can select more than one file to open at a time. If you are using Microsoft Visual Basic for Applications with the GetOpenFileName method, you must use a macro to loop through the selected files to... KB#213635. Keywords: Arrays, Macro, VBA, File Name, Files, Web

 
756. Bus Schedule You cannot specify boldface in regular cell formatting. Conditional Formatting could do the boldface but that is all. A macro is needed to change the time and formatting so that that 1:00 and 13:00 both appear as 1:00 with the PM appearing in bold, and both without AM or PM. Example: Original Bus... Keywords: Fonts, Format, VBA, Macro, Web, Comments

 
757. How to Use a Visual Basic Macro to Open and Retrieve a File on the Internet In Microsoft Excel 2000, you can use a Microsoft Visual Basic for Applications macro to open and retrieve files that are located on computers on the Internet or an... KB#213245. Keywords: VBA, Macro, Files, Web, Window

 
758. Custom Function to Average a Range Without Highest and Lowest Values For statistical purposes, you may want to calculate the average of a data sample without its highest and lowest values. In Microsoft Excel, you can write a Visual Basic for Applications (VBA) macro to do this, based on the following... KB#213495. Keywords: Arrays, Formulas, VBA, Macro, Web

 
759. Creating a database table from a summary table Many users are familiar with Excel's pivot table feature, which creates a summary table from a database table. But what if you want to perform the opposite operation? This document describes how to create a database table from a simple two-variable summary table.The worksheet below demonstrates.... Keywords: Charts, Sorting, Format, Formulas, PivotTables, PivotChart, Printing, Macro, VBA, Menu Command

 
760. How to Count Rows Displayed After Data Has Been Filtered This article contains a sample Microsoft Visual Basic for Applications procedure that counts the number of rows that remain after you run AutoFilter or Advanced Filter on a... KB#148621. Keywords: Advanced Filter, AutoFilter, VBA, Macro, Web

 
761. How to Attach a Custom Toolbar to a Specific Workbook When you create a custom toolbar in Microsoft Excel, you may want the toolbar to be visible when a particular workbook is open. This article explains how to configure the workbook to display the toolbar when the workbook is... KB#304419. Keywords: Errors, VBA, Toolbar, Web

 
762. Move, Copy, Replace, Populate ThisWorkbook.VBProject.VBComponents("Module1").Export "Test.bas" Workbooks("Book3.xls").VBProject.VBComponents.Import "Test.bas" Kill "Test.bas" ActiveSheet.Copy After:=Sheets(Sheets.Count) 213548 - XL2000: "255 Characters in Cell" Error When Copying Worksheet Compare to macro I created below,... Keywords: Errors, Formulas, Clipboard, VBA, Macro, Web, Comments, LEN

 
763. How to Automate Excel from an HTML Web Page Using JScript This article demonstrates how to create and manipulate a Microsoft Excel workbook from an HTML... KB#234774. Keywords: Format, Arrays, VBA, File Name, HTML, Internet Explorer, Web

 
764. Run a macro when any cell changes Several readers have asked questions which require Excel to run a section of macro every time a value changes. First, the improved method available only in XL97: Excel 97 has some new event handlers that allow a macro to be run every time a cell changes. Let's say that anytime a value greater than... Keywords: Format, Macro, OnEntry, VBA, Web

 
765. Multiple Euro Icons or "Subscript out of Range" Error When You Start Excel When you start Microsoft Excel, you may see one of the following symptoms: You see more than one Euro formatting button on the Formatting toolbar. -or- You receive the following Microsoft Visual Basic error message: Run-time error '9': Subscript... KB#257452. Keywords: Add-In, Format, Errors, VBA, Toolbar

 
766. Cannot Set the Orientation Property of a Pivot Field When you try to use Microsoft Visual Basic for Applications to set the Orientation property of a pivot field in a PivotTable, you receive the following error message: Unable to set the Orientation property of the PivotField class NOTE : You... KB#318063. Keywords: Download, Errors, Class, VBA, Error Message, File Name, Files

 
767. Leave Chart Corner Blank in Excel The solution is incredibly simple. I asked Mark if he had a title in the upper left corner of his chart data. Like the example shown, he did. If you can re-arrange your data so that this upper left cell of the chart data is blank, (in the example at the right, I moved the work "Census" up one... Keywords: Charts, SERIES, Years, VBA, Web

 
768. How to Compare Two Cells Ignoring Leading and Trailing Spaces By using the TRIM worksheet function in Microsoft Visual Basic for Applications, you can compare the contents of two cells in such a manner that leading and trailing spaces are... KB#142588. Keywords: VBA, Macro, Web, TRIM

 
769. CRLF - Lines Split by Carriage Return, Line Feed Keywords: VBA, Macro, HTML, Web, Comments, CODE

 
770. Group Object Containing Excel Object Changes Size When OleFormat.DoVerb Opens Excel Object In a PowerPoint 2000 presentation, if you create an object group that contains a Microsoft Excel chart object and another object, and you run a macro that uses the DoVerb method to open the Excel chart object, the size of the group object... KB#257428. Keywords: Charts, Format, Macro, Object, VBA

 
771. Workspace Doesn't Retain Window Size When you open a workspace (*.xlw) file in Microsoft Excel, the window state is not retained. Instead, the windows appear in the restored... KB#160264. Keywords: VBA, Macro, Web, Window

 
772. Prompted to Save Changes After Setting the Saved Property to True When you close your workbook, Microsoft Excel prompts you to save changes even though you had previously set the Saved property of the workbook to True in a Microsoft Visual Basic for Applications macro. For example, your macro contains a line of... KB#238488. Keywords: ActiveX, Macro, VBA, Toolbar

 
773. Removing external links I have tried the German version. It is only 20KB versus the 168KB of the Microsoft version. It is a very simple add-in and doesn't have the bells and whistles of the other one. However it clears the error message I found with the MS one. It also clears links very simply as well. How it works:A... Keywords: Add-In, Charts, SERIES, Download, Errors, Links, VBA, Error Message, Files, Web

 
774. Excel -- Pivot Tables -- Clear Items Clear Old Items Old Items Remain in Pivot Field Dropdowns The data in the pivot table source may change, and items that were previously in the source are no longer included. For example, some sales reps may leave the company, and the names of their replacements appear in the source table. Even... Keywords: Download, Sales, Errors, PivotTables, VBA, Macro

 
775. Pasted Text is Inexplicably automatically parsed 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 procedures on this web site are provided "as... Keywords: VBA, Web, Parse

 
776. Event handler to play midi file Dan from Wauconda posed this week's question. I have a worksheet that calculates a value. If the cell value is 100 I need a midi file to be played. This tip makes use of the event handlers discussed here on December 23rd. If you are not familiar with event handlers, please review that tip first.... Keywords: VBA, Macro, Object, Web

 
777. Error Occurs when you Exit Internet Explorer or Excel When You Use a Pivot Table Control with a Visual Basic User Defined Function If you use a Microsoft Visual Basic User Defined Function in a calculated member, and then the calculated member is used in a Pivot Table control in either Microsoft Excel or Microsoft Internet Explorer, an Application Error may occur when you... KB#291265. Keywords: Download, SQL, Errors, PivotTables, Object, User Defined Function, VBA, Internet Explorer, Web

 
778. Gantt Chart Sample AddIn Available for Download The Gantt Chart Sample Add-in is a sample COM add-in designed for use with Microsoft Excel. The purpose of this sample is to illustrate how to create a COM add-in using the Add-in Designer with Microsoft Visual Basic 6.0. The following file... KB#254008. Keywords: Add-In, Charts, GANTT, Download, Colors, Format, VBA, Files, Templates

 
779. Paste Rows Keywords: AutoFilter, Sorting, Errors, Printing, VBA, Macro, Web, Comments

 
780. Excel -- Pivot Tables -- Field Settings Field Settings Manually Hide or Show Subtotals To manually hide subtotals for a field: To manually show subtotals for a field: Programmatically Hide Subtotals You can use a macro to hide subtotals in a PivotTable. This example uses the PivotField property, to hide all the subtotals. To hide only... Keywords: Download, Colors, PivotTables, VBA, Macro

 
781. How to Enter a Second Line in a Message Box In a Microsoft Visual Basic for Applications macro (Sub procedure), to display more than one line of text in a message box, use the line feed character (character code 13) as in the following sample... KB#124213. Keywords: VBA, Macro, Web

 
782. A macro to fill in blank rows Thanks to Mike who provided this week's question: I'm trying to write a macro and am new at this. I have a spreadsheet that is hundreds of pages long and many columns wide. I would like the macro to search only the "A" column for ""(no data) and then use the data from the previous row, selecting... Keywords: Formulas, VBA, Macro, Web

 
783. How to add the Workbook Path to the Header and Footer in Excel This article describes how to add the path of the Microsoft Excel workbook to the header of footer of the workbook. The feature described in this article is not available in versions of Excel earlier than Excel 2002. In earlier versions, the only... KB#287482. Keywords: Page Setup, Macro, VBA, File Name

 
784. Delete Links Wizard Available The Delete Links Wizard is an add-in file for Microsoft Excel that you can use to delete links to external files in one or more workbooks. Currently, the only way to perform this task is to manually delete the links or to use a Microsoft Visual... KB#188449. Keywords: Add-In, Query, Download, Formulas, Links, Macro, VBA, Files, Text Files

 
785. Expanding a chart by dropping new data onto the chart Here is a cool & easy charting tip for those of us who must re-do charts each week, month, quarter or year to reflect a new data point. Return to the Mr Excel Home Page. Thanks for visiting Mr Excel.com! Proudly serving the web since November 21, 1998. You are visitor # . Originally published April... Keywords: Charts, SERIES, Months, Sales, Borders, Format, Patterns, Errors, Printing, VBA, Web

 
786. Excel -- Data Entry -- Fill Blanks Fill Blank Cells Fill Blank Cells Some worksheets contain cells that have been left blank, in order to make the headings and subheadings easier to read. However, if you want to sort or filter the list, you need to fill in the blanks, by copying the value from the first filled cell above the blank.... Keywords: Sorting, Errors, Formulas, VBA, Macro

 
787. Formula to add years to a given date In Bob's case, you would not need a formula. If he has 1-Jan-2001 in A2 and 1-Feb-2001 in A3, he can highlight A2:A3, then click the fill handle in the lower right corner of the selection, drag down, and the series should correctly extend. MrExcel.com provides examples of Visual Basic procedures... Keywords: SERIES, Dates, Years, Formulas, VBA, Web, MONTH, DAY

 
788. How to Enter a Second Line in a Message Box In a Microsoft Visual Basic for Applications macro, to display more than one line of text in a message box, use the line feed character (character code 13), as in the following sample... KB#213444. Keywords: VBA, Macro, Web

 
789. Excel Saves XML Files in the XML Spreadsheet Format When you save a file in the Extensible Markup Language (XML Spreadsheet) format, the file is not saved in a generic XML file format. -and- XML Spreadsheet (*.xml) is the only XML format listed in the Save As dialog... KB#287734. Keywords: XML, Format, Styles, VBA, Files, Save A File, Web

 
790. Naming a Chart A common question people have is "How can I name an Excel chart?" When using VBA, you can activate a chart by name, but charts just get a default name like "Chart 3" or "Chart 88", in the order in which they are created. A recorded macro will capture that sequential name, but it's no good when you... Keywords: Charts, Formulas, Macro, Object, VBA

 
791. Determine Parse Position Keywords: Arrays, Formulas, Links, Printing, VBA, Parse

 
792. VBA Macro Causes RunTime Error 1004 If AutoComplete Uses IME In Microsoft Excel, when you run a Microsoft Visual Basic for Applications (VBA) macro after you use AutoComplete with the Input Method Editor (IME), you receive an error message that is similar to the following: Run-time error... KB#279248. Keywords: Dates, Errors, Macro, VBA, Error Message, File Name, Files

 
793. Use Cells(Row, Column).Name = "MyRange" to Simplify Naming a Range 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 procedures on this web site are provided "as... Keywords: Defined Name, Macro, VBA, Web

 
794. Use Workbook_Open to open a Userform when a workbook is opened With apologies to Brigitte (question too complicated) and Marie (I never answer questions which are *obviously* typed verbatim from your school homework assignment), the first lucky reader is Judy who asks: "I would like to know how you link dialog box from... Keywords: Sounds, Macro, VBA, Links, Web

 
795. How to Install an Excel AddIn (XLA or XLL) with Automation This article illustrates how to install an Excel Add-in (xla or xll) with... KB#280290. Keywords: Add-In, Errors, Class, Object, VBA

 
796. Changing Cell References 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 procedures on this web site are provided "as... Keywords: Formulas, VBA, Links, Web, ROW, ADDRESS, INDIRECT

 
797. Drag and Drop new data on an existing chart This is an amazing tip. A common problem faced by Excel users is that have a chart of time-series data and need to extend the chart with new figures. Editing all of the data series for a number of charts can be time consuming. Somewhere around Excel 97, the wizards at Microsoft put in a seldom used... Keywords: Charts, SERIES, Months, Borders, VBA, Web

 
798. Excel Abruptly Quits When You Save a Workbook When you save an Excel 2002 workbook, Excel may abruptly quit with an Application... KB#822380. Keywords: ActiveX, Errors, VBA

 
799. Use Cells(Row, Column) instead of Range() when looping through several columns 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 procedures on this web site are provided "as... Keywords: VBA, Web

 
800. Sharing AutoCorrect shortcuts Q. I've set up approximately 200 Excel AutoCorrect shortcuts that represent various products and services offered by my company. What's the best way to transfer these shortcuts to other systems so that my coworkers can use them? AutoCorrect, which debuted in Excel 95, can correct common spelling... Keywords: Charts, AutoCorrect, Format, Errors, Formulas, Printing, VBA, Macro, File Name

 

<< (Page 15)  Previous              Next  (Page 17) >>