Visual Basic Archives Page 1

-       Selva V Pasupathy , Hyderabad, India

   1. Creating a Table of Contents with hyperlinks to the sheets in the workbook is simple using the BuildTOC() macro. XL2000 provides hypertext links in your Excel spreadsheets. The links generated here will be to sheets within your workbook…. Keywords: A dd-In, Charts, AutoCorrect, Sorting, Export, OLE, AutoFormat, Colors, Custom Number Formats, Fonts, Outline, Styles, Arrays, Errors, Formulas, Links, Printing, Class, Clipboard, Command Button, VBA, Macro, Object, User Defined Function, VBE, Files, Toolba r, HTML, Hyperlinks, Internet Explorer, Web, Comments, Window, MID, INDIRECT, VLOOKUP, OFFSET, CELL, MATCH, INDEX

   2. Rearranging Data in Columns Many of the macros here are useful for reformatting name and address listings, and others for creating test data. … Keywords: Add-In, SERIES, Sorting, Dates, Colors, Fonts, Format, Styles, Arrays, Errors, Formulas, Defined Name, PivotTables, Printing, VBA, Macro, UDF, User Defined Function, Toolbar, HTML, Hyperlinks, Web, Comments, TRIM, REPT, TEXT, LEFT, TRANSPOSE, MID, RIGHT, LEN, CODE, CLEAN, CHAR, REPLACE, ADDRESS, CELL, ROW, INDIRECT, FIND, LOWER, UPPER

   3. Date & Time There is enough confusion between US and UK dates without dealing with 2-digit years. Suggest you make changes such as the following so that all of your dates will display as four digit years. If you do not do this you may not be able to di stinguish which part of the date is the year, the month, day; and have further difficulty knowing whether you actually have a 19xx year or a 20xx year…. Keywords: Add-In, Analysis ToolPak, Charts, SERIES, Advanced Filter, Query, Dates, Days, Holidays, M onths, Networkdays, Years, API, Colors, Custom Number Formats, Fonts, Styles, Symbol, Arrays, Errors, Formulas, Sounds, Page Setup, VBA, Macro, User Defined Function, Templates, HTML, Links, Web, Comments, Window, TEXT, NOW, MOD, DATEVALUE, TIME, COUNTIF,

   4. Shortcut Keys in Excel 2000 (Excel Version 9) Create your table of builtin shortcut keys as follows: HELP — Index — Shortcut keys — built-in keyboard shortcuts copy and paste each category into a ShortCutKeys spreadsheet Put topics in Red 12 point b old font, the rest Black 10 point Normal (all Arial) Alternatives to shortcut keys: Toolbar… Keywords: Add-In, Charts, SERIES, AutoShapes, AutoFilter, Sorting, Borders, Colors, Fonts, Format, Outline, Styles, Symbol, Arrays, Errors, Formulas, Links, Spe ech, Defined Name, PivotChart, PivotTables, Printing, Clipboard, Command Button, VBA, Macro, Object, Error Message, Files, Macintosh , Menu Command, Toolbar, HTML, Hyperlinks, Internet Explorer, Web, Cell Note, Comments, Window, FIND, RIGHT, SIGN, LEFT

   5. How to use Visual Basic for Applications examples to control UserForms in Microsoft Excel Explains how to use ActiveX controls in UserForms in Excel. Examples of Visual Basic for Applications code are… KB#829070. Keywords: Query, Days, Colors, Fonts, Format, Styles, Arrays, Errors, Password, Startup, Defined Name, Combo Box, Command Button, VBA, Macro, Object, Spin Button, UserForm, Files, Web, Validation, Window

   6. Slow Response, Memory Problems, and Speeding up Excel Don’t use this feature if you don’t need it. Results reported in XL2000, from XL97 would be similar If you are saving as Microsoft Excel 97-2000 & 5.0/95 Workbook *.xls you will see the following Mi crosoft Excel 5.0/7.0 saving XXXXXXX.xls on your status bar where you normally see Ready toward the… Keywords: Add-In, Charts, Dates, Days, API, DLL, Export, Microsoft Access, Colors, Custom Number Formats, Fonts, Patterns, Styles, Arrays, Errors, Formu las, Links, Password, Speech, Startup, Page Setup, Printing, VBA, Macro, Object, VBE, AutoSave, CD-ROM, Circular References, Error Message, Files, Registry, Templates, Toolbar, Text Files, Hyperlinks, Internet Explorer, Web, Comments, Window, INFO, CELL,

   7. Event Macros, Worksheet Events and Workbook Events Excel 2002 includes format as a change Event so possibly this should change the colorindex at the end and turn Event off/on around it. But I don’t have Excel 2002…. Keywords: Criteria, Sorting, Dates , Days, Borders, Colors, ColorIndex, Conditional Formating, Fonts, Gridlines, Styles, Arrays, Errors, Formulas, Links, Printing, VBA, Macro, Object, VBE, CD-ROM, Error Message, Files, Toolbar, HTML, Hyperlinks, Internet Explorer, Web, Comments, Validation , Window, MIN, NOW, TIME, ROW, SUBTOTAL, MATCH, COUNTIF, PROPER, OFFSET

   8. Onsite pages and related off-site material for Excel pages Keywords: Add-In, Analysis ToolPak, Charts, SERIES, Graphic, AutoFilter, Criteria, Query, Rank, Sorting, Wildcard, Dates, Days, Months, Years, Download, API, DLL, Export, OLE, Statistics, Borde rs, Colors, Custom Number Formats, Fonts, Gridlines, Patterns, Styles, Symbol, Arrays, Errors, Formulas, Links, Sounds, Startup, PivotTables, Printing, Class, Clipboard, Macro, VBA, VBE, CD-ROM, Corrupted, Files, Registry, Templates, Toolbar, Text Files, HTML, Hyperlinks, Internet Explorer, Web, Auditing, Column Width, Comments, Validation, Window, FIND, COUNTA, COUNTIF, DATEDIF, OFFSET, INDIRECT, MAX, HLOOKUP, VLOOKUP, PROPER, REPT, INFO, CELL, NOW, RAND, TODAY

   9. Show FORMULA or FORMAT of another cell Not satisfied with the methods of showing formulas in Excel: (#getformula) Tools — Options — View — (formula on/off) Ctrl+` is the equivalent shortcut (toggle on/off) — accent grave to left of the 1,2,3 on the top row Also see Select cells with formulas or constants which you can tab through or… Keywords: Add-In, Criteria, Sorting, Days, Download, API, Statistics, Borders, Colors, Custom Number Formats, Fonts, Outline, Patterns, Styles, Symbol, Arrays, Error s, Formulas, Links, Printing, Class, VBA, Macro, Object, UDF, User Defined Function, Files, Toolbar, HTML, Hyperlinks, Web, Auditing, Comments, Validation, Window, NOW, VALUE, DATEDIF, REPT, COUNTA, COUNTIF, OFFSET, LARGE, INDEX, BAHTTEXT, LEN, CODE, TRIM

   10. Backup your files, always take backups See Workbook Recovery for files left behind from Excel without a file extension such as 7655D900 or 2CB4D900 with a file type of “File” instead of “Microsoft Excel Workbook”, Immediate solutions — Backup on Dema nd — Excel oriented (#demand) Don’t forget to check if UNDO will work for you, but you… Keywords: Add-In, AutoCorrect, Months, Download, Export, OLE, Colors, Fonts, Format, Styles, Errors, Formulas, Links, Password, Sounds, Startup, Printing, VBA, Macr o, UDF, AutoSave, CD-ROM, Corrupted, Error Message, Files, Registry, Save A File, Toolbar, HTML, Web, Comments, Window, LEFT