Title: 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
2Byte
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
3Byte
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.
4(No Transcript)
5Byte
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
6Byte
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
7Word
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 lt 0,
0 gt0
8Word
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
9Word
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
10Word
The lead decimal point in 245050 is implied
and taken care of by the computer
11A 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 )
12Why 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
13Real (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.234103
With the E notation (Scientific notation)
1.234e3
14Characters 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.
15Some 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
16Some 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
17Some 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 gt B,
etc. If (Boolean Expression) then
Looping instructions For . Next Do Until .
18Some 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,..
19A 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 Ai if B gt 20 then B A
i end if Print B Next i Print S
20A trivial example
Would result in output that looks something like
5 10 15 20 10 11 12 13 14 15 Loop Done
21Programs usually broken into modules Each
module does one specific set of tasks e.g.
Read / verify input data Perform specific
operations Prepare / print output
22Modules --- two flavors
Functions --- return a value e.g. Sqrt(x)
Subprograms --- do not return a value
23Visual 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)
24Visual Basic
Objects / Properties / Methods
Object
Noun
Property
Adjective
Method
Verb
25Visual Basic
Range (A3).select
Range is an object
(A3) is a modifier for the object
select is a method
Note that object separated from method by a .
26Visual Basic
Activesheet.name
Activesheet is an object name is a property
The result is the name of the active sheet
27Visual 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
28Visual 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
29Visual Basic
And to further confuse the issue objects can
have objects as properties And objects can be
grouped into collections Workbook collection of
worksheets
30Visual 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
31Macros are a type of VBA module Recorded
Macros are exactly that A record of the
key strokes converted to VB code
32Keyboard Macros
Quick way to develop a macro Captures
keystrokes Assigns a name shortcut
Two places to store Current workbook Personal
workbook
33Current 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.
34Recorded 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
35Can record macros in two modes
Absolute address mode referenced cells are
absolute addresses (i.e. colrow)
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.
36We 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.