Home > Excel Help Portal > Programming Excel

<<  (Page 4)  Previous              Next  (Page 6)  >>

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.

201. Using a Visual Basic Macro to Sort Arrays in Microsoft Excel In Microsoft Excel, there is no direct method for sorting an array of values with a Microsoft Visual Basic for Applications macro or procedure. This article discusses two different algorithms that you can use to sort arrays: Selection Sort and... KB#133135. Keywords: Sorting, Arrays, VBA, Macro, Web

 
202. How to Remove All Items from a ListBox or ComboBox There is no single method that you can use to remove all items from a ListBox or ComboBox control on a UserForm. The method that you use to remove an item depends on whether the ListBox or ComboBox control is bound to a worksheet. This... KB#165632. Keywords: Arrays, VBA, UserForm, Toolbar, Web, Window

 
203. Macro Doesn't Function on Multiple Sheets in Group Mode In Microsoft Excel, when you use a Microsoft Visual Basic for Applications procedure to perform an action while worksheets are selected in group mode, the action performed may only be performed on one of the worksheets instead of on every worksheet... KB#213431. Keywords: Arrays, VBA, Macro, Object, Menu Command, Web, Window

 
204. How to Run a Macro When Certain Cells Change In Microsoft Excel, you can create a macro that is called only when a value is entered into a cell in a particular sheet or in any sheet that is currently open. Note, however, that you should not call macros unnecessarily because they slow down... KB#142154. Keywords: Colors, ColorIndex, Formulas, VBA, Macro, Object, Web

 
205. Goto Special Blanks to clean up a pivot table This tip is an extension of last week's tip. You can use the Go To Special command to select the blank cells from a range. This is great for cleaning up data in a pivot table. Pivot tables are wonderful tools. But what if your pivot table is just an intermediate step in your data manipulations and... Keywords: Sorting, Months, Format, Outline, Styles, Formulas, PivotTables, VBA, Web

 
206. EnableEvents Property Does Not Work in Automation Server When Microsoft Excel 2000 is used as an Automation server during an Automation session, the EnableEvents property may not work as expected. Attempts by the client application to set this property to False through code in the client application... KB#211626. Keywords: VBA, Macro, Object, Toolbar, Web

 
207. Fill in the Empty Cells Here is a Deja posting with some better answers using copy/paste than the solution I provide... Keywords: Fonts, Errors, Formulas, VBA, Macro, Web, Comments

 
208. How to set the Mask Property and the Picture Property for an Office 2003 CommandBar Button This step-by-step article describes how to set the Mask and the Picture properties of a Microsoft Office 2003 CommandBar control from a managed code extension that is created with Visual Studio Tools for the Microsoft Office System. The... KB#824017. Keywords: OLE, Colors, Styles, Class, VBA, Templates, Window

 
209. How to Set the Mask and Picture Properties for Office XP CommandBars Microsoft Office XP introduces two new properties to the CommandBarButton object: the Mask and Picture properties. You can use these properties to place pictures on custom generated command bar controls. Note These properties are also available... KB#286460. Keywords: Add-In, ActiveX, OLE, Errors, Startup, VBA, Object, Web

 
210. Limitations for Naming Visual Basic Modules In Microsoft Excel, the names of Microsoft Visual Basic for Applications modules are subject to certain limitations. This article explains these limitations and how to avoid problems when module names include illegal... KB#213560. Keywords: Export, Errors, Macro, Object, VBA, Web, Window

 
211. How to Remove All Items from a ListBox or ComboBox There is no single method that you can use to remove all items from a ListBox or ComboBox control on a UserForm . The method that you use to remove an item depends on whether the ListBox or ComboBox control is bound to a worksheet. This... KB#213721. Keywords: Arrays, VBA, UserForm, Web, Window

 
212. How to Prevent the Automatic Creation of Hyperlinks When you type an entry in your worksheet that begins with any of the following prefixes, Microsoft Excel automatically creates a hyperlink: http:// www. ftp:// mailto: file:// news: \\ Excel also creates a hyperlink when you type an e-mail address... KB#291209. Keywords: AutoCorrect, AutoFormat, Errors, Links, Startup, Class, VBA, Macro, Files, Smart Tag, Hyperlinks, Web, Window

 
213. How to Retrieve a Table from Access into Excel Using DAO In the versions of Microsoft Excel listed above, you can use Data Access Objects (DAO) in Visual Basic for Applications to retrieve a table from Microsoft Access. To provide an example of how you can use DAO to retrieve a table from Microsoft... KB#146406. Keywords: DAO, Microsoft Access, Fonts, Errors, VBA, Macro, Object, Web

 
214. RTD Server Does Not Send Update Notifications to Multiple Excel Instances When you use multiple instances of Excel with your RealTimeData (RTD) server, you may receive the following message: The real-time data server 'servername.classname' is not responding. Would you like Microsoft Excel to attempt to restart the... KB#284883. Keywords: Dates, ActiveX, Errors, Class, VBA, Object, RTD

 
215. Macro to Extract Data from a Chart In Microsoft Excel, you can retrieve data from a chart even when the data is in an external worksheet or workbook. This is useful in situations where the chart was created from, or linked to, another file that is unavailable or has been damaged... KB#300643. Keywords: Charts, SERIES, Symbol, Errors, Formulas, Links, VBA, Macro, Object, Error Message, Web

 
216. How to Display a Progress Bar with a User Form If you have a Microsoft Visual Basic for Applications macro that takes a long time to complete, you may want to give the user an indication that the macro is progressing normally. This article shows you how to create a progress bar with a user... KB#170782. Keywords: Format, VBA, Macro, UserForm, Toolbar, Web, Window

 
217. How to Display Top 10 Records with a Macro In Microsoft Excel, you can use the AutoFilter feature to filter a list based on simple criteria. For example, if you have a list of salespeople that has a column of names and a column containing each person's total sales for the month, you can... KB#141771. Keywords: AutoFilter, Criteria, Sales, VBA, Macro, Web

 
218. Conditional Formatting to Make Due Dates Stand Out =(E2-TODAY()) We are almost there. Once you go through all of these steps to get the conditional format set up for the first cell, it is easy to use the Paste Special feature to copy the formats to your entire range of dates. When you Paste Special - Formats, the conditional formatting will be... Keywords: Dates, Days, Borders, Colors, Conditional Formating, Fonts, Patterns, Formulas, Sounds, Macro, VBA, Web

 
219. How to Display Top 10 Records with a Macro In Microsoft Excel, you can use the AutoFilter feature to filter a list based on simple criteria. For example, if you have a list of salespeople that has a column of names and a column containing each person's total sales for the month, you can... KB#213627. Keywords: AutoFilter, Criteria, Sales, VBA, Macro, Web

 
220. How to Get Prime Numbers or Factors A prime number is any integer that can be divided evenly only by itself and one. A factor of a number is an integer that can be evenly divided into that number. This article contains Microsoft Visual Basic for Applications macros for Excel that... KB#246802. Keywords: VBA, Macro, Web

 
221. Function to Convert Degrees/Minutes/Seconds Angles to or from Decimal Angles Angular measurements are commonly expressed in units of degrees, minutes, and seconds (DMS). One degree equals 60 minutes, and one minute equals 60 seconds. To simplify some mathematical calculations you may want to express angular measurements... KB#213449. Keywords: Format, Formulas, VBA, Web

 
222. Swap as in Swap Columns or Swap Rows This page contains some VBA macros. If you need assistance to install or to use a macro please refer to Getting Started with Macros. For more depth see Install a Macro or User Defined Function on my Formula page. Speed and efficiency considerations can be seen in Proper, and other Text changes and... Keywords: Formulas, VBA, Macro, User Defined Function, Web, Comments

 
223. Automation AddIn Function Binds to Excel BuiltIn Function with the Same Name When you create an Automation Add-in that is targeted for Microsoft Excel and you insert a function from your add-in into an Excel worksheet by using the Insert Function dialog box, Excel binds to the built-in function instead of your custom... KB#286305. Keywords: Add-In, DLL, Errors, Formulas, VBA

 
224. How to Use the ApplyCustomType Method to Format Charts When you run a Microsoft Visual Basic for Applications macro in Microsoft Excel 97, you can use the ApplyCustomType method to apply standard or custom chart types to a chart or a series in a chart. This article explains how to use the... KB#167279. Keywords: Charts, SERIES, Format, Errors, Macro, Object, VBA, Error Message, Toolbar, Web

 
225. Function That Uses the ActiveCell Property Returns Incorrect Result When you enter a formula that calls a custom function, the formula may return an incorrect result when you recalculate the worksheet. The formula returns the correct result only when you select the cell that contains the formula and then... KB#213684. Keywords: Formulas, VBA, Object, Web

 
226. How to Programmatically Save a Worksheet as HTML An updated version of the Internet Assistant Wizard for Microsoft Excel 97 is available. This updated wizard allows you to export a worksheet to a Hypertext Markup Language (HTML) file by using a Visual Basic for Applications macro. NOTE: The... KB#168561. Keywords: Add-In, Charts, Download, Export, Arrays, VBA, Macro, Files, HTML, Web

 
227. Visual Basic Editor (VBE) Window Project Explorer (ctrl+R) displays a hierarchical list of the projects and all of the items contained and referenced by each project. I have no idea of what options below came with installation, or if anything was changed, and if changed for the better or not. This is what I have.... Keywords: Colors, Fonts, Errors, Class, Object, VBA, VBE, Files, Registry, Text Files, Web, Comments, Window

 
228. Gantt chart on a worksheet 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: Charts, GANTT, Dates, Years, Borders, Colors, Conditional Formating, Patterns, Formulas, VBA, Web

 
229. Excel - a VBA function to extract text If you import a text file of data into an Excel worksheet you may wish to extract a particular range of characters from each record. For example if each data record (or row) looks something like 'A100 John 23.5 South' you may wish to extract the number (23.5) which is the third out of four items in... Keywords: SERIES, Format, Formulas, VBA, Macro, User Defined Function, Text Files, Parse, MID, FIND, VALUE

 
230. Copying Worksheet Programmatically Causes RunTime Error 1004 In Microsoft Excel, when you run a macro that copies worksheets, and then places the worksheets into the same workbook that they originated from, you may receive the following (or similar) run-time error message: 1004: Copy Method of Worksheet... KB#210684. Keywords: Charts, Errors, Defined Name, Class, VBA, Macro, File Name, Templates, Web

 
231. How to Force Macro Code to Wait for Outside Procedure In Microsoft Excel, you can use a Visual Basic for Applications macro to run other Windows and MS-DOS applications and procedures. The macro code in Microsoft Excel continues to execute even after the external procedure has been initiated. You... KB#214248. Keywords: Errors, VBA, Macro, Files, Text Files, Web

 
232. How to Send Form Mail That Uses Excel Data This article contains a Microsoft Visual Basic for Applications macro for Excel that uses Microsoft Outlook 2000 to generate and send a form e-mail to individuals that are listed in an Excel... KB#241498. Keywords: Sorting, Errors, VBA, Macro, Object, Web

 
233. Query to Find Unmatched Records Between Two Files This article describes how to create a Subtract query that compares two tables and returns a result set that includes only those records from the first table that do not have matching records in the second... KB#114150. Keywords: Criteria, Query, SQL, VBA, Setup, Files

 
234. How to Trap Events for an Embedded Chart In Microsoft Excel 97, you can associate a chart sheet in a workbook with a Visual Basic module. This means you can create a macro that runs when certain events occur on the chart sheet. However, this functionality does not apply to charts that... KB#161858. Keywords: Charts, Borders, Class, VBA, Macro, Object, Web, Window

 
235. How to Display a Progress Bar with a User Form If you have a Microsoft Visual Basic for Applications macro that takes a long time to complete, you may want to give the user an indication that the macro is progressing normally. This article shows you how to create a progress bar with a user... KB#211736. Keywords: Format, VBA, Macro, UserForm, Web, Window

 
236. How to Assign a Macro to an ActiveX Worksheet Control In Microsoft Excel 97, there is no menu command that allows you to assign a macro to an ActiveX control that is on a worksheet. This article provides an example that demonstrates how to create an ActiveX control and assign a macro to the... KB#157416. Keywords: ActiveX, Command Button, VBA, Macro, Menu Command, Toolbar, Web

 
237. How to Assign a Macro to an ActiveX Worksheet Control Microsoft Excel does not have a menu command that allows you to assign a macro to an ActiveX control that is on a worksheet. This article provides an example that demonstrates how to create an ActiveX control and assign a macro to the control. NOTE... KB#213578. Keywords: ActiveX, Command Button, VBA, Macro, Menu Command, Toolbar, Web

 
238. How to Set Page Setup Attributes for More Than One Sheet In Microsoft Excel, page setup attributes, such as margins, sheet orientation, and print titles, are set for each worksheet, individually. This article describes three methods that you can use to set some of these attributes... KB#213257. Keywords: Page Setup, Printing, VBA, Object, Templates, Web

 
239. Properties Function Property(aaa) 'Excel Maintains: Title, Subject, Author, Keywords, Comments, ' Last Author, Application Name, Last Print Date, Creation Date, ' Last Save Time, ' Security, Category, Manager, Company 'Some DejaNews threads: AN=393501043, AN=371066133 'also see Chip Pearson's... Keywords: API, Formulas, Links, Printing, VBA, Macro, Setup, Hyperlinks, Web, Comments

 
240. How to Add Data to a ComboBox or ListBox in Excel or Word This article describes methods of populating a ComboBox or ListBox... KB#161598. Keywords: Arrays, Combo Box, Macro, Object, UserForm, VBA, Links, Web, Window

 
241. Solving Common Setup Problems This document describes the solution to several common problems involving Excel setup.Excel crashes when it starts When Excel is started, it opens an *.xlb file, which contains your menu and toolbar customizations. If this file is damaged, it may cause Excel to crash when it it started. Also, this... Keywords: Add-In, Charts, Format, Styles, Formulas, Startup, Printing, Macro, VBA, Setup, Files, Toolbar

 
242. How to Trap Events for an Embedded Chart In Microsoft Excel, you can associate a chart sheet in a workbook with a Visual Basic module. This means that you can create a macro that runs when certain events occur on the chart sheet. However, this functionality does not apply to charts that... KB#213738. Keywords: Charts, Borders, Class, VBA, Macro, Object, Web, Window

 
243. How to Prevent the Automatic Creation of Hyperlinks When you type an entry in your worksheet that begins with any of the following prefixes, Microsoft Excel creates a hyperlink: http:// www. ftp:// mailto: file:// news: \\ Excel also creates a hyperlink when you type an e-mail address in the... KB#233073. Keywords: Errors, Links, Startup, Class, VBA, Macro, Hyperlinks, Web, Window

 
244. Prevent duplicate entries How can I make sure that duplicate invoice numbers are not entered?. In Excel 97, you can use the new Data Validation feature to do this. In our example, the invoice numbers are being entered in column A. Here is how to set it up for a single cell:The next cell to be entered is A9. Click in cell... Keywords: Errors, Formulas, VBA, Web, Validation, VLOOKUP

 
245. Macro Examples to Delete Duplicate Items in a List In Microsoft Excel, you can create a macro to delete duplicate items in a list. You can also create a macro to compare two lists, and delete items in the second list that are also in the first (master) list. This is helpful if you want to merge... KB#240077. Keywords: Sorting, VBA, Macro, Web

 
246. How to retrieve the Names of Macros from an Excel Workbook by Using Visual Basic 6.0 This step-by-step article describes how to use Visual Basic 6.0 to retrieve the names of macros from an Excel workbook. Requirements The following items describe the recommended hardware, software, network infrastructure, skills and knowledge,... KB#315731. Keywords: Errors, Command Button, VBA, Macro, Object, Error Message, Web

 
247. How To Retrieve a QueryDef from MS Access Using DAO In Microsoft Excel versions 97 and 7.0, you can use data access objects (DAO) in Visual Basic for Applications to retrieve the results of QueryDef from Microsoft Access. This article demonstrates how to do... KB#147739. Keywords: Criteria, Query, DAO, Microsoft Access, Fonts, VBA, Object, Toolbar, Web

 
248. How to Retrieve Records into Specific Columns Using DAO In earlier versions of Microsoft Excel, you can use SQLBIND to specify where the results of a query are placed. However, if you use Data Access Objects (DAO), and you use the CopyFromRecordset method, data is always returned to a contiguous section... KB#147779. Keywords: Query, DAO, Errors, VBA, Iteration, Object, Toolbar, Web, DB

 
249. Sorting TCP/IP Addresses This page contains or refers to some text pages containing VBA macros and User Defined Functions. If you need assistance to install or to use a macro or function please refer to my «Getting Started with Macros« or delve into it deeper on my Install page. Frequently you have a column of codes that... Keywords: Sorting, Outline, Arrays, Formulas, VBA, Macro, Toolbar, Web, Comments, TEXT, LEFT

 
250. How to simulate a PivotTable with No Summarization in Excel 2000 This step-by-step article shows you how to use Microsoft Visual Basic for Applications code to simulate a PivotTable that does not summarize data. In Microsoft Excel, when you use the PivotTable command to create a table from a list, the data... KB#213799. Keywords: Format, PivotTables, VBA, Macro, Web

 

<< (Page 4)  Previous              Next  (Page 6) >>