Some computer fundamentals and jargon

1. Some computer fundamentals and jargon Memory: Basic element is a bit – value = 0 or 1 Collection of “n” bits is a “byte” Collection of several bytes is a “word”

2. Byte The smallest collection of bits that can hold a character. Usually 8 bits, but on some old or special computers may be 4 or fewer bits. We will assume 8 bit / byte Trivia: 4 bits in an 8 bit/byte world is called a “nibble” Number of distinctly different characters that can be held in a byte: 256 00000000 through 11111111

3. Byte Two major schemes (in the US anyhow) for encoding characters: ASCII -- American Standard Code for Information Interchange Originally designed for use with teletype machines and used only 128 characters.

5. Byte Was limited, so extended two ways: Extensions to ASCII --- define characters to occupy values 128 – 255. Not really standardized EBCDIC (IBM) Extended Binary Coded Decimal Interchange Code

6. Byte Need to be aware of differences as same bit pattern gives different characters. Some computers / languages use ASCII, some use EBCDIC 0110 1111 ASCII: o (lowercase “O”) EBCDIC: ? (Question mark) The PC uses ASCII

7. Word Word typically contains 4 bytes, but can be as few as two or as long as 8. IBM used 4 bytes / word and this has become the standard. Also standardized is the convention that the first bit represents the sign – 1 < 0, 0 >0

8. Word 0111 1111 1111 1111 = 32767 A 2 byte word 1111 1111 1111 1111 = -32768 But numbers come in two flavors: Integers or whole numbers, as above Real, or floating point, numbers

9. Word Floating point numbers are scaled to fall between -1 and +1 Bits in the word are divided to represent the sign of the number, the exponent, and the sign of the exponent. 0.24505 * 105 24505.0 And gets translated as:

10. Value of Exponent (5) Value of number (245050) 0 000 0101 0 000 0011 1011 1101 0011 1010 Sign of Exponent ( + ) Sign of Number ( + ) Word The lead decimal point in 245050 is implied and taken care of by the computer

11. A couple of things to note: • The “numeric” part of a floating point number • only has 27 bits, so is of limited precision (about • 7 digits --- any thing else is garbage. • The exponent has 7 bits, so can range roughly from • 2 –127 to 2 127 (10 -38 to 10 38 )

12. Why should you care???? When we start doing Visual Basic in Excel (VBA), will need to define the type of variable we are using. VBA recognizes several types: Byte A single byte (8 bits) of data String A sequence of bytes containing characters Int 2 byte integer Long 4 byte integer Single 4 byte real number Double 8 byte real number

13. Real (or floating point) numbers can be entered in a variety of ways With the decimal point: 1234.0 With the power of 10 (formula): =1.234*10^3 With the “E” notation (Scientific notation) 1.234e3

14. Characters & Stings For Excel, anything that is not a number or a formula is a string. If want a set of digits as a string, need to enclose in quote or quotation marks. For VBA, need to define variables that will hold strings as string data types As you might expect, both Excel and VBA have functions to convert strings to numbers and numbers to strings. But use with care.

15. Some programming concepts Computers are dumb! Will do only what you tell them Will do it very fast Will do it very accurately A “program” is a set of very explicit instructions

16. Some programming concepts A good analogy is developing a set of driving directions Need to be accurate Go 2.5 miles and turn right on M 40 Need to be unambiguous (clear) Turn right just after the bank on the corner Useful if can correct from error If reach the T intersection, you have gone too far

17. Some programming concepts Programming languages have a variety of constructs from which to build the program Assignment Statement: Variable = Variable or value Logic statement Boolean Expressions: A > B, etc. If (Boolean Expression) then …… Looping instructions For …. Next Do Until ….

18. Some programming concepts Need to “Declare” variable types In BASIC, declaration done as: Dim var as Type And can invoke functions and other prewritten modules Print, read, sqrt,…..

19. A trivial example Dim i as integer Dim A as single Dim B as single Dim S as string S = “Loop Done” A = 5. For i = 1 to 10 B = A*i if B > 20 then B = A +i end if Print B Next i Print S

20. A trivial example Would result in output that looks something like 5 10 15 20 10 11 12 13 14 15 Loop Done

21. Programs usually broken into “modules” Each module does one specific set of tasks e.g. Read / verify input data Perform specific operations Prepare / print output

22. Modules --- two flavors Functions --- return a value e.g. Sqrt(x) Subprograms --- do not return a value

23. Visual Basic • Is the language for programming in Word, Access,& Excel • Is the “Environment” in which the programming is done (called the Integrated Development Environment or IDE)

24. Visual Basic Objects / Properties / Methods Object Noun Property Adjective Method Verb

25. Visual Basic Range (“A3”).select Rangeis an object (“A3”) is a modifier for the object select is a method Note that object separated from method by a .

26. Visual Basic Activesheet.name Activesheet is an object name is a property The result is the name of the active sheet

27. Visual Basic All objects have properties Most objects have methods Will work with only a few of the many objects, methods and properties Will introduce them as needed

28. Visual Basic Some of the objects Workbook Worksheet ActiveSheet Range Form Chart Note --- a “Cell” is not an object, but is a type of range object

29. Visual Basic • And to further confuse the issue • objects can have objects as properties • And objects can be grouped into collections • Workbook = collection of worksheets

30. Visual Basic -- The Environment From Excel --- toggle with alt F11 Notice 3 parts to the IDE The Project area The Properties area The blank (or work area) We will use primarily the properties and work areas

31. Macros are a type of VBA module “Recorded Macros” are exactly that A record of the key strokes converted to VB code

32. Keyboard Macros Quick way to develop a macro Captures keystrokes Assigns a name & shortcut Two places to store Current workbook Personal workbook

33. Current workbook Macro only available in the workbook Stays with the workbook Personal workbook Available to all workbooks you create in current session. Does not travel with current workbook Stored in a special area and needs to be opened.

34. Recorded keystrokes macros, if saved in current workbook, are VBA subprograms in the “modules” component of the IDE. Good way to look at how Excel is manipulated. If saved in the Personal Workbook, will see the Personal Workbook available in the IDE Project window. Are in a module of the Personal workbook

35. Can record macros in two modes: Absolute address mode – referenced cells are absolute addresses (i.e. $col$row) Relative address mode – referenced cells are relative to the selected cell when the macro is recorded. That is, cell addresses may change each time macro is run.

36. We will use recorded macros primarily as a way to introduce VBA programming. But you should be able to find a good use for them – great for repeated tasks.