Home > Excel Help Portal > Programming Excel

<<  (Page 18)  Previous              Next  (Page 20)  >>

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.

901. Make the Header Row stay visible as you scroll through data set up you window so that the header row is the top row in the window. Then, put your cell pointer in column A, in the row just below the headers. From the menu, choose Window - Freeze Panes. Anything visible in the window above and to the left of the cell pointer will remain visible as you scroll... Keywords: VBA, Web, Window

 
902. Date and Time Tutorial Number of Days, Months and Years 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 create a text string: "As of yy/yy/yy, nn Years, mm Months and... Keywords: Dates, Days, Holidays, Months, Networkdays, Years, Format, Formulas, OnEntry, User Defined Function, VBA

 
903. On Error Resume Next Takes Longer to Run Than in Excel 97 When you repeatedly use the On Error Resume Next statement in Microsoft Visual Basic for Applications, Excel may experience performance... KB#262742. Keywords: Errors, VBA

 
904. Keeping a running total in an adjacent column If the data starts in Row 2, then put this formula in B2 and drag down !=SUM($A$2:$A2) 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... Keywords: Formulas, VBA, Web

 
905. Add a Certain Number to All Numbers in a Range Using Paste Special Add 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: Clipboard, VBA, Web

 
906. Setting a font name with VBA ActiveCell.FormulaR1C1 = "x" I want to know how to change the font from arial to wingdings in my macro. Can you help me figure out the code to format my active cell with a specific font and assign my "*" symbol??? Thank you for your help."Ranges have a Font object, that handle all the things that... Keywords: Fonts, Format, Macro, VBA, Web

 
907. Freeze pane for select rows and columns Row 1 and Column A should be frozen 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... Keywords: VBA, Web

 
908. Stamp Auction application -- Dialogs, input boxes, UserForms Sometimes when I am feeling real mellow, I will write a whole application contained in a spreadsheet. This one exceeded all of my expectations. It is an elegant solution to a thorny problem. My stamp club conducts an auction at each meeting. Before the newsletter goes out, everyone lets our... Keywords: VBA, Window

 
909. Automatically add a character in a cell For example, I put 12345 into cell A1. Then in cell B1, I want it to automatically say *12345* Ive tried doing a formula in cell B1 like ="*"=B1"*" or =("*"(=B1)"*") but i cant think or a way to do it. The reason im trying to do this is because im trying to make a bar code and it needs a * at the... Keywords: Formulas, VBA, Web

 
910. Subtotal without grouping For more details and an image about Data Subtotals, read method #4 in Tip 035.-------- 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... Keywords: VBA, Web

 
911. Parsing Dates One option is to use an event handler, somewhat like the military time tip at http://www.mrexcel.com/tip029.shtml. Another method would be a formula. The Date() function allows you to specify a date by giving the Year, Month, Day. The following formula with parse your value and convert it to a... Keywords: Dates, Formulas, VBA, HTML, Web, Parse

 
912. Ranking without repetitive ranks Here's a question I see often. "I'm trying to use the RANK function to sort the values in Column A, but, the problem arises when I have two or more cells with the same value. Then, the RANK also repeats some positions. For example, if I have, 50, 60, 62, 62, 67 in Column A I would get 5,4,2,2,1 in... Keywords: Rank, Sorting, Formulas, VBA, Web, COUNTIF

 
913. How to calculate and display the largest value try with this one instead:=INDEX(C:C,MATCH(MAX(Q:Q),Q:Q,0)) 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.... Keywords: Formulas, VBA, Web, INDEX

 
914. Color Unlocked Cells Option Explicit Public IteM As Variant, SheetR As Integer, DdD As Integer, SheetC as... Keywords: Colors, Fonts, Formulas, Links, Password, VBA, Macro, IS

 
915. Trap Those Errors See also my example for skipping past errors and simply writing them to a log for later retrieval. Module is already in progress On Error GoTo ErrHandler 'Tell VBA to use your trapping routine Module continues then Selection.Value = 123 Exit Sub 'You must exit the sub or the 'Error Handler is... Keywords: Errors, Formulas, Links, VBA, Macro, Object, Error Message

 
916. Have an IF statement return "Yes"-"No" Functions can only return a value, they can not modify their "environment". So, the "workaround" for this problem would be to put this formula:=IF(A2=5,"YES","NO")in A3 instead of A1. MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed... Keywords: VBA, Web

 
917. Protecting specified data using VBA Regards"Well, this can be solved using some events. One, using the Workbook_Open event, or two, using the Sheet_Activate event, like this:In the workbook... Keywords: VBA, Web, Window

 
918. Split the contents of one column into two columns To do this, use Text to Columns. Select the column where you have the names, go to Data, Text to Columns, select Delimited in Step 1, in Step 2 make sure that the comma checkbox is activated. Finally, in step 3 select a Destination cell (It defaults to the current cell). Hit Finish and that's it.... Keywords: VBA, Web

 
919. Custom Dialogs ------- Print List chosen from... Keywords: Formulas, Links, Printing, VBA, Object

 
920. Using the Analysis ToolPack Check the selection for Analysis ToolpackClick OKFrom that point on, XIRR will be available to you. If Analysis Toolpack is not in the list of available Add-Ins, then someone did a custom or minimal install of your Excel. Get the install CD's, do a custom install, and be sure to check the Analysis... Keywords: VBA, Web, XIRR

 
921. Freeze the Headers so they are always visible as the cell pointer scrolls deeper into the 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: Printing, VBA, Web, Window

 
922. Change the worksheet tab name to reflect the value in cell A1 WORKBOOK... Keywords: VBA, Web

 
923. Keeping Excel from changing data to dates Two options, format the Column (Or range) where you will input the data as TEXT, Before inputting the data. Second, input the text with a ' starting, like this'1-2and Excel won't translate it. MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either... Keywords: Format, VBA, Web

 
924. Link Chart to Worksheet Range Demonstration of two methods of linking a chart to a worksheet range. When data in the range changes, the chart updates automatically. When you create a chart, the source range for the chart will not change as new data is entered into the worksheet. If you specify a named range as the source for... Keywords: Charts, Download, Format, PivotTables, VBA, Links

 
925. Apply Styles to PivotTables This example worksheet demonstrates how to apply special styles to parts of a pivot table. When you change a pivot table it will lose its formatting. You can create a Visual Basic for Applications procedure that will apply styles to each portion of a pivot table. If you use the Format, Styles... Keywords: Download, AutoFormat, Styles, PivotTables, VBA

 
926. Returning specific data on examining a range Assuming this data is in A1:A4{"Juan";"Pablo";"Gonzalez";"Juan Pablo"}And you want to test for the existance of "Juan", (Located in C1), you could use, for example:{=IF(LEN(C1)*OR(ISNUMBER(SEARCH("*"&C1&"*",A1:A4))),"Exists","Doesn't... Keywords: Formulas, VBA, Web

 
927. Hiding a range depending upon user selectons Thank you"Being a Control checkbox, you would have code that looks like... Keywords: Errors, VBA, Web

 
928. MrExcel's Recommendations: Word help Many times, I have jokingly suggested that a questioner should seek out MrAccess.com or MrWord.com or MrOutlook.com only to lament that no one has taken up that role yet. Well, I am glad to announce that an old friend has taken on the role of offering expertise in MIcrosoft Word. DreamBoat's new... Keywords: VBA, Web

 
929. Importing Text Files into Open Workbook Use either the OpenText or Dialogs methods to import text into an open workbook. Select the file to import from a built-in dialog box. You can use either of two Visual Basic for Applications methods to automate the process of importing text into a workbook. The OpenText method controls all of the... Keywords: Download, Format, VBA, Files, Text Files

 
930. Select Custom Header Custom Dialog box and Visual Basic for Applications procedure provides a choice of three custom headers that can be assigned to a worksheet. You can have Excel store frequently used custom headers and choose them from a menu option in a worksheet. A Visual Basic for Applications procedure and... Keywords: Add-In, Download, Format, Printing, Object, VBA

 
931. Conversion of Feet and Inches to Decimal Values Two Visual Basic for Applications User Defined Functions, one to convert from decimal inches to feet, inches and fractions. The second function converts from feet and inches to decimal inches. You can create these User Defined Functions: CFeet converts from decimal inches to feet, inches and... Keywords: Download, Format, User Defined Function, VBA, Comments

 
932. Working with Userform controls Use the .SetFocus command:Me.TextBox3.SetFocus 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... Keywords: Text Box, Command Button, VBA, Web

 
933. PivotTable to Calculate Budget Variances A Visual Basic for Applications procedure useful in displaying Budget variances. The procedure hides columns in a PivotTable based on the values in the PivotTable data fields. When you create a Pivot Table field to show Budget Variance with the "% of" calclulation option, the PivotTable will show... Keywords: Download, Format, PivotTables, VBA

 
934. Splitting the contents of a cell/column into two distinct cells/columns There are a couple functions you will use to do this.=FIND("@",A2) will tell you which position contains the @ in a cell. In your example, this would return position 6.You can then use =LEFT(A2,FIND("@",A2)-1) to return emailand =MID(A2,FIND("@",A2),LEN(A2)+1-FIND("@",A2)) to return @domain.comP.S.... Keywords: VBA, Web, FIND, LEFT, MID

 
935. Splitting the contents of a cell/column into two distinct cells/columns There are a couple functions you will use to do this.=FIND("@",A2) will tell you which position contains the @ in a cell. In your example, this would return position 6.You can then use =LEFT(A2,FIND("@",A2)-1) to return emailand =MID(A2,FIND("@",A2),LEN(A2)+1-FIND("@",A2)) to return @domain.comP.S.... Keywords: VBA, Web, FIND, LEFT, MID

 
936. Trapping VBA Errors {Part 2} Let’s say that you are getting errors but you know there are errors and do not want to be reminded. Here is a little ditty that I wrote that still traps the errors but writes them to a Log file for later retreival. Please note: This is a hypothetical macro I wrote to prove my code. It always... Keywords: Errors, Printing, VBA, Macro

 
937. Use MathEasy Add-In to simplify entering math formulas The product is straight forward; a "Math" button is visibly added to the Excel toolbar, performing "Addition", "Subtraction", "Average", etc. No prior Excel experience is required. Hopefully beginners can use Math Easy as a stepping-stone to learning more advanced math. Visit... Keywords: Add-In, Download, VBA, Web

 
938. Exit a worksheet with or without saving Try this... Keywords: Macro, VBA, Templates, Web

 
939. Removing a set number of characters from a cell What I need to do throughout a big worksheet was to edit the cell, move the cursor to the end of the cell and then delete the last 4 characters if the characters.This macro should do... Keywords: Macro, VBA, Web

 
940. Saving a chart as a GIF file Q. How can I save a chart as a separate GIF file? You can save your worksheet as an HTML file, and Excel will automatically convert any charts to GIF files. If that seems like overkill, you can write a simple macro that will do the job. Press Alt-F11 to activate the Visual Basic editor. Select your... Keywords: Charts, Format, Errors, Formulas, Printing, VBA, Macro, Files, HTML

 
941. Learn How to Add Running Totals to a Worksheet You can add a running total to a single cell or a column of data. Includes a visual basic macro and example formulas. Excel provides several methods for creating running totals.You can use a worksheet function like =SUM($A$1:A1) where the values to total are in column A and the formula is copied... Keywords: Download, Format, Formulas, Macro, VBA

 
942. Returning Zero instead of #N/A One common issue I face is "How can I have VLOOKUP return a 0 or a empty string "" instead of #N/A! ?"Let's assume that the current formula is=VLOOKUP(A1,$B$2:$D$100,3,False)or more condensed=VLOOKUP(A1,$B$2:$D$100,3,0)one approach to do this is as follows=IF(ISNA(VLOOKUP(A1,$B$2:$D$100,3,0)),””,... Keywords: Formulas, VBA, Web, VLOOKUP

 
943. Using Inputboxes )You can see the Online help for more Types of this Inputbox. 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.... Keywords: VBA, Web

 
944. Dialog Box to Filter Data Use a button, dialog box and simple VBA macro to filter data on a worksheet. When you click the button and enter values for year and month, the list is automatically filtered. You can create an AdvancedFilter with a criteria range on a hidden worksheet. If you control the values in the hidden... Keywords: Criteria, Download, Format, Macro, VBA

 
945. Working with Dates To do this you’ll have to use a formula. If the starting date (30-june-2002) is in A2, put this formula in A3 and drag down.=DATE(YEAR(A2),MONTH(A2)+2,0) MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not... Keywords: Formulas, VBA, Web, DATE

 
946. Formatting Time Values For example, if I have 16548 seconds it should show 4:35:48"First, it's important to remember that Excel keeps dates informatios as day fractions. Therefor, to show the value it first needs to be converted like this:Divide el value in seconds (16548) in (60 s / min * 60 min/hr * 24 hr/day) to... Keywords: Dates, Format, VBA, Web

 
947. Removing hyperlinks from a worsheet In Excel 2002:Highlight all the cells.Right click, select hyperlink from the pop-up menu.Click the Remove button.In Excel 2000 they did not offer this functionality. You could do it with... Keywords: Links, VBA, Hyperlinks, Web

 
948. Tabbing through specific cells Lock all cells, unlock those cells where the user will enter data and then protect the sheet. That way the use can press TAB to move between unprotected cells.You could also set the EnableSelection property of the sheet to 1 – xlUnlockedCells to prevent the user selecting anything not allowed. This... Keywords: VBA, Web, IS

 
949. Removing hyperlinks from a worsheet In Excel 2002:Highlight all the cells.Right click, select hyperlink from the pop-up menu.Click the Remove button.In Excel 2000 they did not offer this functionality. You could do it with... Keywords: Links, VBA, Hyperlinks, Web

 
950. Change Cell Formatting Use a VBA subroutine to shade selected cells on the worksheet. This example show how to add a Do Loop and With statement to a recorded macro. You can modify recorded macros with Do Loop and With, End With to create Visual Basic for Applications procedures that perform an operation on a block of... Keywords: Download, Format, Macro, VBA

 

<< (Page 18)  Previous              Next  (Page 20) >>