Home > Excel Help Portal > Programming Excel

<<  (Page 1)  Previous              Next  (Page 3)  >>

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.

51. How to Create a Visual Basic Automation Addin for Excel Worksheet Functions In Microsoft Excel 2000, you cannot call a function directly in a Component Object Model (COM) Add-in from a worksheet cell formula. Instead, you must create a Visual Basic for Applications (VBA) wrapper for the COM Add-in function so that... KB#285337. Keywords: Add-In, DLL, Arrays, Errors, Formulas, VBA, Object, Registry, Web, RAND

 
52. ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel Workbooks The ExcelADO.exe sample illustrates how you can use ActiveX Data Objects (ADO) with the Microsoft Jet OLE DB 4.0 Provider to read and write data in Microsoft Excel... KB#278973. Keywords: Query, Download, ActiveX, DAO, Microsoft Access, OLE, Format, Formulas, Defined Name, Macro, Object, VBA, Files, Templates, Web, Window, DB, OFFSET

 
53. How to develop DatabaseDriven Smart Tags for Office by Using Visual Basic for Windows Smart tag providers, for use in Office applications, are developed as ActiveX dynamic-link libraries (DLLs). Smart tag providers expose a visual, on-screen graphical user interface (GUI) when a user types a word or phrase that the provider... KB#305799. Keywords: Download, ActiveX, DLL, SQL, XML, Symbol, Class, VBA, Macro, Object, Registry, Smart Tag, HTML, Internet Explorer, Web, Window

 
54. How to Work with Random Access Files By itself, a file consists of nothing more than a series of related bytes located on disks. When your application accesses a file, it must make assumptions about what the bytes are supposed to represent (integers, strings, or other data... KB#150700. Keywords: SERIES, VBA, Macro, Files, Web, Window

 
55. Excel FAQ - Macros A macro has been added and then removed, leaving an empty module. Empty modules trigger the macro query, as does an actual macro. A macro to remove all VBA code in a workbook is available at Chip Pearson's web site: http://www.cpearson.com/excel/vbe.htm Application.DisplayAlerts = False 'code to... Keywords: Export, Fonts, Errors, Formulas, Password, Class, VBA, Macro, Object, UDF, User Defined Function, UserForm, VBE, Files, Toolbar, Web

 
56. How to configure Office applications for automation from a COM+/MTS package Lists the problems you should be aware of when you automate from within a COM+/MTS package informaiton, and the provides steps to configure an Office application for automation from a COM+/MTS... KB#288368. Keywords: DLL, Microsoft Access, Errors, Password, Startup, Printing, VBA, CD-ROM, Registry, Web

 
57. Extraction of a Group of Digits and Dashes, from postings by Harlan Grove Keywords: Sorting, Patterns, Arrays, Errors, Class, VBA, Macro, Object, UDF, Web, Comments, FIND, LEFT, COUNTIF

 
58. How to Dynamically Create Excel 5.0 Charts in OLE 2.0 Control This article demonstrates how to create new Excel 5.0 charts at run-time to be displayed in the Visual Basic OLE 2.0 container... KB#147803. Keywords: Charts, Legend, SERIES, OLE, AutoFormat, Arrays, Class, VBA, Object

 
59. How to Transfer Data from an ADO Recordset to Excel with Automation You can transfer the contents of an ADO recordset to a Microsoft Excel worksheet by automating Excel. The approach that you can use depends on the version of Excel you are automating. Excel 97, Excel 2000, and Excel 2002 have a... KB#246335. Keywords: Dates, ActiveX, DAO, OLE, Format, Arrays, Errors, Class, VBA, Object, Setup, Links

 
60. How to Create a RealTimeData Server for Excel Microsoft Excel provides a new worksheet function, RTD, that allows you to call a Component Object Model (COM) Automation server for the purpose of retrieving data real-time. This article describes how to use Visual Basic to create a... KB#285339. Keywords: Dates, ActiveX, DLL, Arrays, Errors, Formulas, Class, VBA, Object, Window, RTD

 
61. Quick Chart VBA Examples When you record a macro to add a chart object to a worksheet, Excel comes up with the following... Keywords: Charts, SERIES, Outline, Arrays, Errors, Defined Name, VBA, Macro, Object, Error Message, Window, EVEN

 
62. Number Conversions This page contains some VBA macros. Assistance to Install a Macro or User Defined Function on my Formula page. Conversion of numbers which appear correct but are not validly interpreted in Excel. Numbers with a right minus appear correct but are treated as Text. Numbers in US use period as decimal... Keywords: Query, Sorting, Dates, Colors, Format, Errors, Formulas, VBA, Macro, User Defined Function, Toolbar, Web, Comments, TEXT, LEN, CHAR, TRIM

 
63. Build TOC Another Approach This page is not concerned with Hyperlinks, if you want a Table of Contents with hyperlinks then see my original page Build Table of Contents with BuildTOC dealing with creating a Table of Contents with hyperlinks, and with making, revealing, and destroying Hyperlinks. Extra code/column added to be... Keywords: Charts, Consolidate, Sorting, Format, Errors, Formulas, Links, VBA, Macro, User Defined Function, Hyperlinks, Web, Comments, INDIRECT, OFFSET

 
64. Internet Explorer Cannot Open an Excel AddIn (*.xla) File When you navigate to a Microsoft Excel Add-in (*.xla) file from a Web page while running Internet Explorer, the add-in fails to load and Internet Explorer displays a warning that the page you are looking for is currently unavailable. The... KB#268016. Keywords: Add-In, ActiveX, OLE, Startup, Class, VBA, Setup, Files, Registry, Toolbar, Text Files, Hyperlinks, Internet Explorer, Web, Window, CHAR

 
65. How to Identify and Remove PLDT/CAR/SGV Macro Viruses In November 1997, Microsoft identified a new macro virus, the PLDT macro virus, that infects workbooks in Microsoft Excel for Windows. This macro virus is also known as PLDT97 or Laroux E. In April 1998, Microsoft identified another new macro... KB#176807. Keywords: Add-In, Export, Third-Party, Format, Styles, Macro, VBA, Files, Web, Window

 
66. How to Use Visual Basic or ASP to Create an XML Spreadsheet for Excel 2002 Excel 2002 supports an XML format with which it can both load and save workbooks (or XML spreadsheets). By using this XML spreadsheet format, you can create multi-sheet, formatted workbooks to Excel without using Automation. This approach may... KB#285891. Keywords: Download, ActiveX, XML, Borders, Colors, Format, Gridlines, Styles, Errors, Formulas, Printing, Object, VBA, File Name, Files, Templates, Internet Explorer, Web, Column Width, Window

 
67. Shapes A shape can be assigned a hyperlink, by RClick on shape and assign hyperlink. A shape can be created from a cell, from the Drawing Toolbar, from the Forms Toolbar among what I presume are many other places where shapes can be created. More information on bitmaps in Excel can be found at More on... Keywords: Charts, Colors, Errors, Links, VBA, Macro, Toolbar, Hyperlinks, Web, Comments

 
68. You are prompted to grant permission for ActiveX Controls when you open an Office 2003 document When you open a Microsoft Office Excel 2003 workbook, a Microsoft Office PowerPoint 2003 presentation, a Microsoft Office Word 2003 document, you may be prompted to grant permission for ActiveX Controls to be loaded or to permit the controls... KB#827742. Keywords: ActiveX, VBA, Registry, Links, Web

 
69. Excel 2000 SR1 Macro Modification Security Update Microsoft has re-released an update to Microsoft Excel 2000 SR-1. This update addresses a vulnerability that might allow malicious code to run in an Excel file without warning. Under typical circumstances, you will see a warning in Excel 2000 when... KB#306604. Keywords: Charts, Download, AutoFormat, Colors, Outline, Errors, Formulas, Password, PivotTables, Printing, Macro, Object, VBA, CD-ROM, Error Message, Files, HTML, Internet Explorer, Links, Web, Window

 
70. How to configure Office applications to run under a specific user account We do not recommend or support automation to a Microsoft Office application from an unattended user account. on why Microsoft does not recommend automation under this context 257757 Considerations for server-side automation of Office For... KB#288367. Keywords: Microsoft Access, Errors, Password, Startup, Printing, Object, VBA, CD-ROM, Registry, Web

 
71. How to query and Display Excel Data by Using ASP.NET, ADO.NET, and Visual Basic .NET This step-by-step article demonstrates how to display data from an Excel spreadsheet through an ASP.NET (.aspx) page by using Visual Basic .NET. Create Sample Excel Worksheet Start Microsoft Excel, and create a new worksheet. Add the... KB#311731. Keywords: Text Box, Query, OLE, SQL, XML, Class, Object, VBA, File Name, Files, Web, Comments, Window, DB

 
72. How to convert Text to Numbers in Excel 2003 This step-by-step article describes how to convert cells that contain text to cells that contain numbers. When you import a file that was created in another program (such as dBASE or Lotus 1-2-3) or that was downloaded from a mainframe, Excel 2003... KB#822665. Keywords: Third-Party, Format, Errors, Formulas, Macro, VBA, Web, AVERAGE, LEFT, TRIM, CLEAN, VALUE

 
73. How to import Data into SQL Server from Excel This step-by-step article demonstrates how to import data from Microsoft Excel worksheets into Microsoft SQL Server databases by using a variety of methods. Description of the Technique The samples in this article import Excel data by using:... KB#321686. Keywords: Query, ActiveX, DAO, Import Data, Microsoft Access, OLE, SQL, Format, Object, VBA, Links, DB

 
74. How to use the Forms Controls on a Worksheet in Excel 2000 This step-by-step article describes how to use the forms controls in a worksheet in Microsoft Excel 2000. Microsoft Excel provides several controls for dialog sheets. These controls can be used on worksheets to help you select data. For... KB#214262. Keywords: Format, Formulas, Combo Box, Object, VBA, Toolbar, Links, INDEX

 
75. Recording and customizing a macro This is the 19th weekly tip at www.MrExcel.com. Many of the tips involve some sort of macro trick. This week, for the Excel users who have never written a macro, I offer a primer on how to record and then customize a useful Excel macro. This simple problem will be used to illustrate how to record,... Keywords: Sorting, Format, Errors, VBA, Macro, Object, Toolbar, Web

 
76. Event Macro to Add Path and Filename to Excel Header Now, on to writing the VBA code. There are a couple of useful variables that you can use. ActiveWorkbook.Path will return the path of the workbook. It might look like "C:\My Documents\MrExcel". ActiveWorkbook.FullName will return the path and filename of the workbook. It might look like "C:\My... Keywords: Add-In, Years, Errors, Printing, VBA, Macro, Object, Setup, File Name, Toolbar, Web

 
77. How to use the Forms Controls on a Worksheet in Excel 2002 Microsoft Excel provides several controls for dialog sheets. You can use these controls on worksheets to help select data. For example, drop-down boxes, list boxes, spinners, and scroll bars are useful for selecting items from a list. By adding... KB#291073. Keywords: Format, Formulas, Combo Box, Object, VBA, Toolbar, Links, INDEX

 
78. How to handle events for Excel 2003 by using Visual Basic .NET This step-by-step article describes how to handle Microsoft Office Excel 2003 events from an Automation client that you develop by using Visual Basic... KB#822750. Keywords: Arrays, Errors, Class, VBA, Object, Error Message, Web, Window

 
79. How to Programmatically Perform a Web Query A Web query is a new feature in Microsoft Excel 97 that allows you to retrieve data stored on an intranet, the Internet, or the World Wide Web. A Web query can use static parameters, dynamic parameters, or a combination of both. Queries with... KB#162080. Keywords: Query, Symbol, VBA, Macro, Object, Files, Toolbar, Web

 
80. How to Return Data from a Dialog Box to a Worksheet In Microsoft Excel, each type of control on a custom dialog box (a drop- down list, an edit box, and others) has a distinct return value that can be returned to a worksheet. This article describes how to create a custom dialog box and contains... KB#141685. Keywords: Fonts, Arrays, Formulas, VBA, Macro, Object, Toolbar, Web

 
81. How to handle Events for Excel by Using Visual Basic .NET This step-by-step article describes how to handle Excel events from an Automation client that is developed with Visual Basic .NET. Overview of Event Handling You can construct an event handler with Visual Basic .NET in the following two... KB#302814. Keywords: Download, Arrays, Errors, Class, VBA, Object, Templates, Web, Window

 
82. How to Create a Monthly Calendar This article contains a sample Microsoft Visual Basic for Applications macro (Sub procedure) that prompts you for the month and year and creates a monthly calendar using a... KB#150774. Keywords: Dates, Days, Borders, Fonts, Format, Gridlines, Errors, VBA, Macro, Web, Window

 
83. Headings for Columns, and Page Numbering Keywords: Text Box, Download, Colors, Fonts, Page Setup, Printing, Macro, VBA, Web, Comments, ROW

 
84. How to retrieve Metadata from Excel by Using the GetOleDbSchemaTable Method in Visual Basic .NET This step-by-step article describes how to retrieve table and column metadata from Microsoft Excel data sources by using the GetOleDbSchemaTable method with the Microsoft OLE DB Managed Provider and the Microsoft OLE DB Provider for Jet.... KB#318373. Keywords: Query, ActiveX, OLE, Format, Styles, Class, VBA, Object, DB

 
85. How to eliminate redundant data in an Excel list Bill asked this week's question about redundant data. I build a monthly transaction list. At the end of the month, I need to eliminate the redundant data and come up with a total by account code. Each account code can occur multiple times. Bill then described his current methodology which is... Keywords: Advanced Filter, Consolidate, Sorting, Days, Years, Arrays, Formulas, Defined Name, PivotTables, Printing, VBA, Web

 
86. How to Return Data from a Dialog Box to a Worksheet In Microsoft Excel, each type of control on a custom dialog box (a drop-down list, an edit box, and so forth) has a distinct return value that can be returned to a worksheet. This article explains how you can create a custom dialog box; it... KB#213632. Keywords: Fonts, Arrays, Formulas, VBA, Macro, Object, UserForm, Toolbar, Web

 
87. HELP in and for Excel Now everyone can do their own research, does their own typing so is their own secretary, and maybe even their own publisher. If you learn to use the search engines and only use them with the most advanced features, can find practically anything on any subject. In all MS Office products you can... Keywords: Years, Formulas, Links, Macro, Object, VBA, VBE, Setup, Setup Program, HTML, Web, Comments, Window

 
88. Barhopper -- fixup for Restored Toolbars (also List of Menu Items) There are two macros shown on this page. The Barhopper macro starts of the recursive BarHop macro. The barhopper macro will pick out toolbars containing "Tool" as part of the name and which are not builtin menus, and will call the barhop macro. When running this macro suggest opening up the... Keywords: Add-In, Sorting, Clipboard, VBA, Iteration, Macro, Object, User Defined Function, Files, Toolbar, HTML, Hyperlinks, Web, Comments, Window, INDIRECT

 
89. How to Display a Custom Office Assistant Balloon You can use the Office Assistant to display a custom message (balloon) with the options you specify. The Assistant can display a simple message with buttons or check boxes. You can display several types of buttons (OK, Cancel, Retry, and so on) at... KB#162079. Keywords: Sales, Arrays, Printing, VBA, Macro, Object, Toolbar, Web

 
90. How to Create a Monthly Calendar This article contains a sample Microsoft Visual Basic for Applications macro ( Sub procedure) that prompts you for the month and year and creates a monthly calendar using a Microsoft Excel 2000... KB#213795. Keywords: Dates, Days, Borders, Fonts, Format, Gridlines, Errors, VBA, Macro, Web, Window

 
91. How to transfer XML Data to Microsoft Excel 2002 by Using Visual Basic .NET Excel 2002 introduces functionality for opening files in the Extensible Markup Language (XML) format. An XML file that is well-formed can be opened directly in Excel 2002 or Excel 2003 by using either the user interface or code. With Visual... KB#307021. Keywords: Export, Microsoft Access, XML, Format, Styles, Errors, Object, VBA, Files, HTML, Window

 
92. How to Programmatically Perform a Web Query A Web query is a feature in Excel that allows you to retrieve data stored on an intranet, the Internet, or the World Wide Web. A Web query can use static parameters, dynamic parameters, or a combination of both. Queries with static parameters send... KB#213730. Keywords: Query, VBA, Macro, Object, Web

 
93. How to Use Looping Structures in Visual Basic for Applications When you create macros or applications in Microsoft Visual Basic for Applications, it is often necessary to run through a section of code several times. Visual Basic for Applications provides several methods with which to repeat, or loop through,... KB#141762. Keywords: Symbol, Errors, VBA, Iteration, Macro, Object, Web, Comments

 
94. Quicken Utilties for Excel Option Explicit Dim Flag, Flag2 As Boolean 'Flags to keep the Find Procedures running Dim HeaderRow, TotalRow As Integer 'Number of the Header and Total Rows Dim Counter As Integer 'Counter to ensure searches are relative to activecell Dim TempString, TempTest As String 'Strings that are used as... Keywords: Add-In, Sorting, Months, Format, Outline, Errors, Printing, Clipboard, VBA, Macro, Web

 
95. How to reset Personal Menus in Office XP and Office 2000 Programs This step-by-step article describes how to reset a menu in a Microsoft Office program back to its default first-run condition. In addition, this article describes the following: What the Personalized Menus and Toolbars feature is. Two methods... KB#193006. Keywords: Errors, VBA, Setup, CD-ROM, Error Message, Files, Toolbar

 
96. Summary of Methods to Recover Data from Corrupted Files There are several methods that you can use to recover information from damaged or corrupted files. This article discusses those methods and points you to other Microsoft Knowledge Base articles that discuss the methods in greater detail. Methods... KB#142117. Keywords: Add-In, Charts, Download, Format, Formulas, Class, Macro, VBA, CD-ROM, Corrupted, Files, Links, Web

 
97. How to troubleshoot Custom Smart Tags in Office This article describes how to troubleshoot a custom-created smart tag that does not work as you expect. Smart tag technology gives Microsoft Office programs the ability to recognize data that you enter. Office then offers on-the-spot access to... KB#300950. Keywords: AutoCorrect, DLL, Class, VBA, File Name, Registry, Smart Tag, Templates, Internet Explorer, Links, Web

 
98. How to convert Text to Numbers in Microsoft Excel 2002 This step-by-step article describes how to convert cells that contain text to cells that contain numbers. When you import a file that was created in another program (such as dBASE or Lotus 1-2-3) or was downloaded from a mainframe, Microsoft Excel... KB#291047. Keywords: Third-Party, Format, Errors, VBA, Macro, Web, AVERAGE, TRIM, CLEAN, VALUE

 
99. How to use Visual Basic .NET for Binding for Office Automation Servers When you automate an application such as a Microsoft Office application, the calls to the properties and methods of the Office application's objects must be connected in some way to those objects. The process of connecting property and method calls... KB#304661. Keywords: Download, Class, VBA, Object, Web, Window

 
100. Running Subroutines and Macros from Visual Basic In Microsoft Excel, you can run Microsoft Visual Basic for Applications Sub procedures and Microsoft Excel version 4.0 macros from a Visual Basic procedure by using the Application.Run and Application.ExecuteExcel4Macro methods. You can also... KB#213837. Keywords: VBA, Macro, Web

 

<< (Page 1)  Previous              Next  (Page 3) >>