1 / 46

Introduction to Help Text

Introduction to Help Text. Online help to explain your program’s operation “Splash screen” Providing simple to understand prompts in dialog boxes Handling errors in a graceful, helpful way Adding help text to your Excel applications. Two Approaches to Online Help.

bryanne-kennedy
Download Presentation

Introduction to Help Text

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction to Help Text • Online help to explain your program’s operation • “Splash screen” • Providing simple to understand prompts in dialog boxes • Handling errors in a graceful, helpful way • Adding help text to your Excel applications

  2. Two Approaches to Online Help • Method 1: Add a Help button, link to display a dialog box with help text, or use a MsgBox • Method 2: “Official” help system provided by Windows

  3. Help Method 1 • Simple Stuff! • Add “comments” to spreadsheet cells • Add a “text box” • Put extra information in one of the worksheets, • providing instructions, explanations - rename the • worksheet to “Help”. • UserForms with help text

  4. Help Method 2: UserForms • Create a UserForm • Add a Frame control • Insert a Label control • Set the Frame properties to allow scrolling • Write a section of initialization code to add the help text to the Label, and set the Label to fit the frame and allow scrolling (see HelpExamples.xls)

  5. Help Method 2: Scrolling Label • Label controls cannot contain a scroll bar • A frame control can contain a scroll bar • So we use a trick: We define a frame control with a scroll bar. • Then we enlarge the size of the Label to equal the size of the text added to it (AutoSize = True) • This makes the label bigger than the existing frame So we will tell the Frame to scroll through the height of the label

  6. Help Method 3: WinHelp System Most Windows applications use the Windows Help Systemor the newer HTML Help Supports clickable links within help text Supports some text formatting Recommended: Use third-party help authoring products

  7. Help Method 3: How It Works You create a special “.hlp” help file that containshelp text items, each with a unique HelpContextID(e.g. 1001, 1002, 1003) On the properties list for a control, you enter the HelpContextID (and for form, “WhatsThis Button, True” When help is selected, Windows matches the HelpContextID(e.g. 1001) with the corresponding text in the .hlp fileand displays on screen. Details: Advanced topic! Also, in Office 2000, introducing HTML help

  8. Help Summary Method 1: Use comments, textboxes, “Help” sheet Method 2: Create UserForms with your help text,link to a “Help” button Method 3: Use Windows built-in help features

  9. Week 14 - Topics • Last Time (refer to Week13.ppt) • Built-in functions commonly used in business applications • String functions and formatting • Dates and Times • Formatting Data for output • Error handling • Importing data from text, splitting text into columns • Importing data from text files: In-Class Exercise #1 • Importing data using VBA: In-Class Exercise #2

  10. Importing Data Into Excel • Models are built from data • Data sources: market research reports, corporate data, • Internet web sites, government research • Entering data manually • Copying and pasting from IE (or other table) • How to convert text into columnar data • Reading “delimited ASCII” text files • Writing VBA code to import data

  11. Converting Text into Columns Example: Paste some data from an external source Select the range of cells that contains the pasted data. The range can be any number of rows tall, but no more than one column wide. On the Data menu, click Text to Columns. Convert Text to Columns Wizard

  12. Example

  13. Select the first column only Then choose Text to Columns… on the Data menu

  14. Text to Columns Wizard “Delimited” means that certain characters separate each field of data

  15. Text to Columns Wizard Preview

  16. Completed result

  17. Importing Text Files • You can also read text files directly into Excel. • Use Open on the File menu • At Files of type prompt, choose Text Files • This will launch the Text Import Wizard • (similar to the Text to Column Wizard) • Some programs refer to this as “Delimited ASCII” files. Many spreadsheet, database and custom programs canexport their data in “Delimited ASCII” format, including Excel

  18. Text Import Wizard 2 types of files: Delimited and Fixed Width

  19. Text Import Wizard

  20. Fixed-Width Data "Under 5 years" 28,314 25,743 30,885 "5 to 9 years" 29,606 25,471 33,741 "10 to 14 years" 32,292 28,624 35,960 "15 to 19 years" 29,854 25,300 34,408 "20 to 24 years" 23,750 19,056 28,444 "25 to 34 years" 53,725 49,419 58,032 "35 to 44 years" 64,333 59,210 69,456 "45 to 54 years" 57,700 53,900 61,500 "55 to 59 years" 19,520 16,128 22,912 "60 to 64 years" 13,618 10,481 16,755 "65 to 74 years" 27,821 23,849 31,793 "75 to 84 years" 18,323 15,092 21,554 "85 years and over" 4,384 2,952 5,816

  21. Fixed-Width Example See FixedWidth.Txt example file

  22. Reading & Writing Text Files • You’ve seen how to import certain types of data • You can write VBA programs to read any kind of data file(since not all data files come set up just right for the TextFile Import Wizard, and may use non-standard delimiters) • You can write VBA programs to output data to files • VBA also includes functions to obtain file directories,rename or delete files, create directories and other tasks • We will focus on VBA’s text file read & write features.

  23. Three Types of Files VBA supports 3 types of data files: 1. Sequential access (read or write from beginning to end) 2. Random access (read or write at any location in file) 3. Binary access (random access using internal binary data) Types 2 and 3 not usually used in VBA so will focus on “sequential access” used for reading and writing text files

  24. Working With Files • Named files store data on disk • OPEN: Before you can read from - or write to - a file, you must first have your program “open” the file. This tells VBA what file you wish to use. • READ/WRITE: You use VBA statements to read (or get “input”) from the file, or write (“print”) data outputto the file. • CLOSE: When finished the file must be “closed”.

  25. Opening a File To Read (also known as “opening for input”) Open “MyData.Txt” For Input As #1 Tells VBA to open the disk file “MyData.Txt”, that the file will be used for reading (input comes from the file) Subsequently refer to the file as #1 in other VBA statements. This associates the named file with a file number or file channel for input/output.

  26. Reading From the File The easiest way to read an entire line of text from the opened file is to use the LINE INPUT # statement. Example: Line Input #1, ReadDataString This reads one line of data from the file and stores it in to the string variable ReadDataString. Once the data is in the string variable, you can use the string as you would use any other string variable.

  27. Detecting the End of File When reading data from a file, your program will likely eventually encountered the end of the file - in other words, there is no more data to read from the file. This creates an Error condition that can be trapped with the On Error Goto handler, or use the special EOF() function to test if the end of file has been reached. While Not EOF(1) … Line Input #1, ReadDataString … Loop

  28. Example Data for File Demo Elizabeth Morley/Spokane,WA 99205 Bob Smith/Spokane,WA 99212 Shamir Amed/Cheney,WA 99208 Vladimir Ustov/Liberty_Lake,WA 99216 Note the non-standard data format using “/” so the text import wizard cannot be used - will see how to process this in VBA

  29. Example: ReadFileDemo1() Sub ReadFileDemo1() Dim StartRow As Integer, Row_Of_Data As String StartRow = 1 Open "FileReadSampleData.txt" For Input As #1 Do While Not EOF(1) Line Input #1, Row_Of_Data Cells(StartRow, 1) = Row_Of_Data StartRow = StartRow + 1 Loop Close #1 End Sub

  30. Result of Running The Program

  31. Processing the Input Data Line Input #1, Row_Of_Data 'Expected data format: first lastname/city,state zip using string functions BlankPos = InStr(Row_Of_Data, " ") SlashPos = InStr(Row_Of_Data, "/") FirstName = Left(Row_Of_Data, BlankPos - 1) LastName = Mid(Row_Of_Data, BlankPos + 1, SlashPos - BlankPos - 1) CommaPos = InStr(Row_Of_Data, ",") CityName = Mid(Row_Of_Data, SlashPos + 1, CommaPos - SlashPos - 1) Comma2Pos = InStr(SlashPos, Row_Of_Data, ",") ' Start at position of / StateName = Mid(Row_Of_Data, Comma2Pos + 1, 2) ZipCode = Right(Row_Of_Data, 5)

  32. Storing Data to The Worksheet Cells(StartRow, 1) = FirstName Cells(StartRow, 2) = LastName Cells(StartRow, 3) = CityName Cells(StartRow, 4) = StateName Cells(StartRow, 5) = ZipCode Example in ReadFileDemo2()

  33. Writing Data To Files To open a file to which text will be written, use Open “filename” for Output as #1 Note: If the file already existed, this will overwrite or destroy any previous data in the file. (See Append) Then use either the Print #1 statement or Write #1 statement (Print and Write are slightly different) and Close #1 when all finished.

  34. Writing Data To Files: Write # Use Write # as in: Write #1, “The answer is “ & Result If you use multiple variables, as, Write #1, “The answer is “, A, B, C where A and B are numeric values and C is a Boolean, this writes “The Answer is”,2.18,16384,#TRUE# where a comma character is inserted between each value (as for delimited text files)

  35. Writing Data To Files: Print # Print #1, “The answer is “ & Result If you use multiple variables, as in, Print #1, “The answer is “, A, B, C where A & B are numeric and C is a Boolean, this writes “The Answer is” 2.18 16384 #TRUE# where a TAB character is inserted between each value (as for delimited text files)

  36. Sample Program Output to files: See WriteFileDemo1() Reading the file: WriteFileDemo2()

  37. Other Read statements: Input Input # is demonstrated in WriteFileDemo2() Input #1, A, B, C, D Attempts to read each value from the file, separated by commas, into the matching variables. Very important that the variables match the data types of the data in the file. Example of input data: “some text”, 2.18, 100, #False# Input #1, StringVar, SingleVar, IntVar, BooleanVar

  38. Line Input versus Input Line Input reads an entire line or row of data from the input file, into a string variable. Used to read straight text or to read textual data that you will process with your own VBA code. Input reads individual values, each separated by a comma, from the file. Easiest way to read individual data values into your program. The data format matches the format produced by Write #. Makes it easy to write out data that is later read back into a Visual Basic or VBA Excel application (as well as other applications)

  39. Summary of File Reading • Open “filename” for Input as #1 (or other file number) • Line Input #1, stringvariable to read a full line of text or • Input #1, list of variables separated by commas • Close #1 when all finished • Use Eof(file number) to check for the end of file

  40. Summary of File Writing • Open “filename” for Output as #1 (or other file number) • Print #1, list of variables or • Write #1, list of variables • Close #1 when all finished

  41. Appending Data to a File Open “filename” For Append As #1 Print #1, A, B, C Close #1 “Append” opens the file for output, but new data is written starting at the end of the existing file so that existing data is not overwritten. Open for Output creates an entirely new file, deleting any old data.

  42. Multiple Files Example What does this set of program statements do? Open “file1.txt” for input as #1 Open “file2.txt” for output As #2 Do While Not Eof(1) Line Input #1, Line_Of_Text Print #2, Line_Of_Text Loop Close #2 Close #1

  43. In Class Exercise #2 Part 1: Following the example presented in lecture, create a subroutine named CreateFile that opens a file named “DataFile.txt” and writes the following items to the file: “This data file contains employment records” “Elizabeth Morley”,99205 “Bob Smith”,99212 “Shamar Amed”,99208 “Vladimir Ustov”,99216

  44. In-Class Exercise #2 (2) Part 2: Create a subroutine named ReadDataFile that opens the file named “DataFile.txt” for input and then reads the first line (Line Input #1, FirstLine) of data and displays that in a message box. Then, using the Do While Not Eof(1) statement, for each line of data, read the name into a EmployeeName String variable and the Zipcode into a Single-type variable. Display using MsgBox EmployeeName & “ “ & ZipCode

  45. Other File Operations Error conditions: A properly written program should detect if the Open statement fails because the file cannot be found. You can do this with On Error Goto. Other VBA functions (info in Excel/VBA Help) ChDir Kill (delete) CurDrive MkDir Dir Name (rename) FileDataTime RmDir (delete directory) FileLen

  46. Summary • At this point, you know more about using and applying • Excel to business problems than probably 98% to 99% of • all Excel users. • Solver business optimization models • Excel statistics and forecasting methods • Use of VBA to create custom applications

More Related