Home > Excel Help Portal > Programming Excel

<<  (Page 19)  Previous              Next  (Page 21)  >>

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.

951. Add a percentage to all cells in 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: VBA, Web

 
952. Highlight Exception Values This example displays a date less that the current date in red. It uses a Visual Basic for Applications procedure to set the color of the text. You can use the OnEntry property to run an exception check. The procedure can change the font color of the cell and can perform an operation on the... Keywords: Download, Colors, Fonts, Format, OnEntry, VBA

 
953. Animated Line Chart This example uses a Visual Basic for Applications procedure to update and display different values for a line chart. Each value is displayed for 1 second. You can use the Wait method in Visual Basic for Applications to animate the display of a chart. If you use the Wait method and change the... Keywords: Charts, SERIES, Download, Format, VBA

 
954. Click on Chart to Select a New Source Range In this example, you can click on a chart and enter a new source range. The chart will redraw to show the data in the range you select. You can create a Visual Basic for Applications procedure to prompt for a chart's input range. When you click on the chart, the prompt allows you to enter or select... Keywords: Charts, Download, Format, Object, VBA

 
955. Change recorded macro to import text file in next available row instead of using A1 every day. Change your recorded macro where it says something likeRange(“MyRange”)ToActiveCell 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... Keywords: Import Data, Macro, VBA, Web

 
956. Use Edit / Replace to Remove Decimal Points 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

 
957. Trapping Errors This is the conventional way of trapping errors as your VBA code runs. This method always displays a dialog box with the error message. As I run my code and find out how to handle each error, I change the code to tell me what I should have done. See also my example for skipping past errors and... Keywords: Errors, VBA, Macro, Object, Error Message

 
958. Advanced VBA Tutorial Exporting Excel Data in Special Formats You can use a VBA procedure to export data in comma delimited format. Or, change the delimiting character to export the data separated by semicolons, etc Importing Text Files into Open Workbook You can use either of two Visual Basic for Applications methods... Keywords: Sorting, Export, Format, Arrays, VBA, Files, Text Files

 
959. Custom Dialog and List Box Create a custom dialog to select items from a master list and enter a value in a worksheet cell. You can use a simple Visual Basic for Applications procedure to display a custom dialog and list box. If you use worksheet ranges to control the list box, it is easy to return the selected value to a... Keywords: Download, Sales, Format, VBA

 
960. Using a List Box to Change Data Displayed in a Workbook Learn to use a List Box to update values displayed on your worksheet. Use the OFFSET function to return values from a table. Select an item in the List Box and see values for that item appear on a worksheet. Download File: Click Here to download a copy of the example in Excel 95 format. Click Here... Keywords: Download, Format, VBA, OFFSET

 
961. List Box Lookup Select items from a List Box by entering the first few characters of the item’s name. The first matching item in the List Box is automatically highlighted. You can look up entries in a list box by entering the first few letters of the entry in an edit box. With a Visual Basic for Applications... Keywords: Download, Format, VBA

 
962. Change Case to Mixed There is a routine that adds a space after MC and changes an O' to O’. The first makes the name look correct and the second is just my housekeeping. If the space is not added, MCCULLOUGH becomes Mccullough; with this Mc Cullough. I would like to fix the DEs but there is no hard fast... Keywords: Formulas, Links, VBA, UPPER

 
963. Recorded Macro to a Usable Macro Keywords: Macro, VBA, Web, Comments

 
964. Pop Up List box Example of Visual Basic for Applications procedure that draws a list box aligned with the active cell and inserts value selected from list box into active cell. You can create a Visual Basic for Applications procedures that creates a new list box "on the fly" when a command button is clicked. The... Keywords: Download, Format, Command Button, VBA

 
965. Increment a Counter everytime a Userform is opened In the Workbook module (Or, if it’s a Userform, in the Initialize event of that Userform) put the... Keywords: Formulas, Macro, VBA, Web

 
966. Use Index and Match to lookup a cell to the left of your key cell “14 20 12 25 10 35 8 50 4 65 I have a cell at let say E5 = to 30 the next number up is 35 and i want to display the 10. If E5 = to 55 than i want it to display 4 What is the formula for this?” If the data is located in A2:B6, then use this formula:=INDEX($A$2:$A$6,MATCH(E5,$B$2:$B$6)+1) MrExcel.com... Keywords: Formulas, VBA, Web, INDEX

 
967. Macros - Custom Excel Macros in VBA from Mr Excel Excel is a registered trademark of the Microsoft Corporation.MrExcel is a registered trademark of the Tickling Keys, Inc. All contents Copyright 1998-2003 by MrExcel... Keywords: Macro, VBA, CD-ROM

 
968. Comparison of Three Sort Methods Comparison of the time required to sort a Visual Basic for Applications Array using three methods. You can sort an array by transferring it to the worksheet and using Excel's built in sort. Another sorting method is the Visual Basic for Applications "Bubble Sort". Quick Sort, adapted from the... Keywords: Sorting, Download, Format, Arrays, VBA

 
969. Custom Dialog Example This is an example of a simple Visual Basic for Applications procedure that displays a custom dialog box and updates a data base. You can create a custom dialog that updates a database with a Visual Basic for Applications procedure. The dialog can add new records, and edit or delete existing... Keywords: Download, Format, VBA, Comments

 
970. Rounding time to a specific value If you have 8:15:00 in A1, use this formula (And format as General, or as Number with 2 decimals)=A1*24 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... Keywords: Format, Formulas, VBA, Web

 
971. Add a Single Quote After talking to him awhile, I discovered he did not know what he really wanted but he needed to convert 5,000 Social Security numbers and put a tick (') mark in front of every one of them. This piece of code ran in about 9... Keywords: Formulas, Links, VBA

 
972. How to filter out duplicate data Sure, just select the range where the duplicates are. Go to data, Advanced Filter, select "Copy to new location", leave the "Critiera" field empty, and make sure you have chekced "Unique Entries Only". MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty... Keywords: VBA, Web

 
973. Working with time formats I always seem to find questions about going the other way. Anyway, if your time is in A1, use this formula:=TEXT(A1,"hhmm") MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied... Keywords: VBA, Web, TEXT

 
974. Counting Cells with specific content To count the number of numeric cells in a column, use =COUNT(A1:A99).To count the number of alpha or numeric cells in a column, use =COUNTA(A1:A99).You can combine functions in a formula: =SUM(A1:A99)/COUNTA(A1:A99) MrExcel.com provides examples of Visual Basic procedures for illustration only,... Keywords: VBA, Web, COUNT, COUNTA

 
975. Maximized view on open Jen asks "How can I make sure Excel opens my file maximized ?, I need this for visibility purposes"To make sure of this, right click in the little workbook (Next to File menu), and choose, View Code.You should see this, and the cursor standing between those... Keywords: VBA, Web

 
976. Number of Days, Months and Years Between Two Dates Create a User Defined Function that uses the DateSerial function to calculate the time elapsed between two dates. You can create a Visual Basic for Applications User Defined Function to return the number of years, months and days from a start date to an end date. Use the Function in formulas or to... Keywords: Dates, Days, Months, Years, Download, Format, Formulas, User Defined Function, VBA

 
977. Parsing with characters Cells. On the numeric tab, select Custom. In the custom box, type 99\/9999\/99When you are typing custom number formats, you can insert any single text character by preceeding it with a backslash. MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty... Keywords: Custom Number Formats, VBA, Web

 
978. Change Case My data comes from the mainframe in all UPPER case. We find it easier to read in Mixed or Proper case. There is a routine that adds a space after MC and changes an O' to O’. The first makes the name look correct and the second is just my housekeeping. If the space is not added, MCCULLOUGH becomes... Keywords: VBA, UPPER

 
979. MrExcel's Recommendations: Access help DreamBoat filled the Word void with TheOfficeExperts.com 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... Keywords: VBA, Web

 
980. Input Validation Excel's OnEntry property provides a way to validate data entered in a worksheet cell. You can set a worksheet's OnEntry property in an Auto_Open macro. When data is entered in a cell on the worksheet, the macro specified in the OnEntry property will run. If the data is not valid, the OnEntry... Keywords: Download, Format, Errors, Macro, OnEntry, Error Message, Validation

 
981. Inserting an image into a worksheet footer Unfortunately, this is only possible "directly" in Excel XP. Before that, you would have to "simulate" a footer by placing the image in a row at the end of each page. MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied,... Keywords: VBA, Web

 
982. Creating multiple lines of text in a messagebox Use this code:Msg = "This is line 1" & vbcr & "This is Line 2 " & vbcr & vbcr & "This is Line 4"Msgbox Prompt:=Msg 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... Keywords: VBA, Web

 
983. Programming a messagebox for user responses Use a MsgBoxIf MsgBox("My Question", vbYesNo,"Title") = vbYes then 'Do somethingElse 'Don't do anythingEnd If 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... Keywords: Macro, VBA, Hyperlinks, Web

 
984. Changing a cell's background color You have to use the Interior property of cells, and then, you can use either ColorIndex (To use one of the 56 "presets" colors in Excel) or Color, like thisActiveCell.Interior.ColorIndex = 36orRange("A1:A6").Interior.Color = RGB(200,160,35) MrExcel.com provides examples of Visual Basic procedures... Keywords: Colors, ColorIndex, VBA, Web

 
985. Add Data to Next Row Keywords: Charts, Formulas, Links, VBA

 
986. Maximum Sustainable Growth Excel's iteration feature solves circular references in the Maximum sustainable growth model You can use Excel's iteration feature to solve a circular reference. In a maximum sustainable growth model Excel can iterate to solve the circular formulas where maximum sales growth depends on profit... Keywords: Download, Sales, Format, Formulas, Iteration, Macro, VBA, Circular References, Auditing

 
987. Delete a worksheet in a macro without having to see the "This worksheet will be permanently deleted" This particular message is one that can be suppressed by using the Application.DisplayAlerts property. Use the following code:Application.DisplayAlerts = FalseWorksheets("MySheet").DeleteApplication.DisplayAlerts = True MrExcel.com provides examples of Visual Basic procedures for illustration only,... Keywords: VBA, Web

 
988. Bypass Delete Confirmation Bill sends today's question: How can I stop the confirmation box from needing to be clicked "OK" when deleting files or sheets called to be deleted within a MACRO under Excel 97.Use the DisplayAlerts property:Application.DisplayAlerts = FalseWorksheets("MySheet").DeleteApplication.DisplayAlerts =... Keywords: Macro, VBA, Files, Web

 
989. Data Validation Demonstration of custom dialog box procedure that checks for error and prompts user for correct input. A Visual Basic for Applications procedure and a custom dialog box can be used to prompt user to correct input errors. If you assign a Visual Basic for Applications procedure to the frame of the... Keywords: Download, Format, Errors, VBA, Validation

 
990. Change a Cell Value without Changing the Formula Well…. No. I mean, if you have in a cell=1+2The result will Always be 3. You can’t change the value to “4” without replacing or changing the formula. MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not... Keywords: Formulas, VBA, Web

 
991. Convert 11.5 to 11 minutes and 30 seconds Use this formula=A1/(24*60)Where A1 houses the 11.5. You may need to format this cell as mm:ss 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... Keywords: Format, VBA, Web

 
992. Rounding Numbers This is one function that I like very much ! If the number is in A2 just use this formula=CEILING(A2,500) 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... Keywords: VBA, Web, CEILING

 
993. Switching between active worksheets Sure, a couple listed below:Control Tab or Control Shift Tab: Switch between windows (Or workbooks)Control Pg Down or Control Pg Up: Roll between worksheets in a Workbook. MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied,... Keywords: VBA, Web

 
994. Run a Macro Automatically When the Workbook Opens Place something like this in the Workbook... Keywords: Macro, VBA, Web

 
995. Custom Document Properties To achieve these ends, we hold the items in the CustomDocumentProperties and pull them out as needed.Our spreadsheets are created from a master template and the Properties must be defined in the template before they can be used. ThisWorkbook.CustomDocumentProperties("ActMonth") = ForecastMon BUKRS... Keywords: Formulas, Links, VBA, Templates

 
996. GANTT Chart for Scheduling Resources Create a GANTT chart showing when resource is scheduled using a bar chart and Visual Basic for Applications procedure. You can create a Visual Basic for Applications procedure that uses a list of start and stop times on a worksheet to build a table of durations for a bar chart. The bar chart can... Keywords: Bar Chart, Charts, GANTT, Download, Format, VBA

 
997. Entering Time Without the Colon This workbook demonstrates the use of a VBA macro to speed data entry of time. You can use an OnEntry procedure to speed entry of time values. Each time you enter data in a cell, a VBA procedure can be run which converts the entry to a valid time value. For example, you can enter the value "1300"... Keywords: Download, Format, Macro, OnEntry, VBA

 
998. Mail from Excel In this example, there is a list on another page that contains BranchIDs and email addresses to send to. The list is read one Branch at a time and that branch’s data pulled... Keywords: Arrays, Formulas, Links, VBA

 
999. Count Numeric Values in String Public X As Long, Y As Long, D As Long, G As... Keywords: Formulas, Links, VBA, COUNT

 
1000. Macro to Save File as name in A1 Arcangelo from Italy asks: How can I write a macro to save the current file with a filename from cell A1? This macro is amazingly... Keywords: VBA, Macro, Web

 

<< (Page 19)  Previous              Next  (Page 21) >>