Saturday, September 13, 2014

VBA Language

Visual Basic for Applications (VBA)
Microsoft's event-driven programming language Visual Basic 6
OLE Automation interface
ActiveX controls
 project: A set of modules.
 module: a set of declarations followed by procedures.
 procedure: プロシージャ。a named sequence of statements executed as a unit. For example, Function, Property, and Sub are types of procedures. A procedure name is always defined at module level. All executable code must be contained in a procedure. Procedures can't be nested within other procedures.
 Sub procedure: a procedure that performs a specific task within a program, but returns no explicit value. A Sub procedure begins with a Sub statement and ends with an End Sub statement. Sub stands for Subroutine.
constant 定数
macro マクロ
objects オブジェクト
properties プロパティ
methods メソッド

Byte: (1 byte) from 0 to 255.
Integer: (2 bytes) from -32,768 to 32,767.
Long: (4 bytes) from -2,147,483,648 to 2,147,483,647.
Single: (4 bytes) from -3.402823E38 to -1.401298E-45 for negative values and 1.401298E-45 to 3.402823E38 for positive values.
Double: (8 bytes) from -1.79769313486231E308 to -4.94065645841247E-324 for negative values and
 4.94065645841247E-324 to 1.79769313486232E308 for positive values.
Date: (8 bytes) Stores date/time values.
Boolean: (2 bytes) Stores logical values—true or false.
Currency: (8 bytes) Stores currency values ranging from -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
Variant: (16 bytes) Stores any values.
String: Stores text values. Its use of memory depends on the length of text to store.

A And B: True if both A and B are true; False otherwise.
A Or B: True if at least one of A and B are true; False otherwise.
A Xor B: False if both A and B are true or if both A and B are false; True otherwise.
Not A: True if A is false; False if A is true.
Arithmetic Operators

Addition: 10 + 5
Subtraction: 10 - 5
Negation: -10
Multiplication: 10 * 5
Division: 10 / 5
Integer division: 11 \ 5
Exponentiation: 10^5
Modulus (remainder): 10 Mod 5
Comparison Operators

Equal to: 10 = 5
Greater than: 10 > 5
Less than: 10 < 5
Greater than or equal to: "a" >= "b"
Less than or equal to: "a" <= "b"
Not equal to: "a" <> "b"
Sub Hello()
   MsgBox ("Hello, world!")
End Sub
temp = Application. _
to split a single command over more than one line you need to break the line with a space and then an underscore.
Variables can be declared as one of the following data types: Boolean, Byte, Integer, Long, Currency, Single, Double, Date, String (for variable-length strings), String * length (for fixed-length strings), Object, or Variant. If you do not specify a data type, the Variant data type is assigned by default.

Dim a As Integer, b As Integer, c As Integer
Dim a, b, c As Integer
Public a As String
Private b As String
Static a As String

Private variables can be used only by procedures in the same module.
Public variables can be used in any procedures in the project. If a public variable is declared in a standard module or a class module, it can also be used in any projects that reference the project where the public variable is declared.
When you use the Static statement instead of a Dim statement, the declared variable will retain its value between calls.
Dim x As Integer, y As Integer
Note If you declare more than one variable by using a single Dim statement, you must specify the data type for each variable.
If you do not specify the data type for each variable, as in the following Visual Basic code, only the variable y is set up as an integer variable. The variable x will be a variant type:

Dim x, y As Integer
macro: a procedure that can be executed from outside the project.
procedure: sub, function or properties
a module contain procedures.
a project contain modules.
inside procedure:
・Dim: visible only to the procedure
・Static: retains its value

inside a module, outside procedures
・Public: visible to the project
・Private (or Dim):  visible only to the module.
Note If you have a module-level variable, its lifetime is the same as if it were a static procedure-level variable.
The following statements create Variant variables:

Dim a
Dim b As Variant
c = "This is some text."

Variables declared as the Variant data type can contain string, date, time, Boolean, or numeric values, and can convert the values they contain automatically.
Dim is short for "Dimension".
Variable declarations are optional in VBA.
The difference between a function and a sub in Excel VBA is that a function can return a value while a sub cannot.
Function Area(x As Double, y As Double) As Double
  Area = x * y
End Function

Dim z As Double
z = Area(3, 5) + 2
MsgBox z
Sub Area(x As Double, y As Double)
  MsgBox x * y
End Sub

Area 3, 5
All arguments are passed to procedures by reference, unless you specify otherwise. This is efficient because all arguments passed by reference take the same amount of time to pass and the same amount of space (4 bytes) within a procedure regardless of the argument's data type.

You can pass an argument by value if you include the ByVal keyword in the procedure's declaration. Arguments passed by value consume from 2 – 16 bytes within the procedure, depending on the argument's data type. Larger data types take slightly longer to pass by value than smaller ones. Because of this, String and Variant data types generally should not be passed by value.

Passing an argument by value copies the original variable. Changes to the argument within the procedure aren't reflected back to the original variable. For example:

Function Factorial(ByVal MyVar As Integer)    ' Function declaration.
    MyVar = MyVar - 1
    If MyVar = 0 Then
        Factorial = 1
        Exit Function
    End If
    Factorial = Factorial(MyVar) * (MyVar + 1)
End Function

' Call Factorial with a variable S.
S = 5
Print Factorial(S)    ' Displays 120 (the factorial of 5)
Print S    ' Displays 5.
If a = 0 Then
   Msgbox "aa"
   Msgbox "bb"
End If

If c1 Then
ElseIf c2 Then
End If
Do While a <> ""
   Msgbox "aa"
While (file <> "")
   Msgbox "bb"
Dim i As Integer
For i = 1 To 20
   Msgbox i
Next i
For Each c In Worksheets("Sheet1").Range("A1:D10").Cells
   Msgbox c.Value

"This is fun"
& operator: to concatenate (join) two strings.
vbNewLine: to start a new line.
"Line 1" & vbNewLine & "Line 2"
Dim a As String, b As String
a = "Hi"
b = "Tim"
MsgBox a & " " & b
Dim t As String
t = "example text"
MsgBox Left(t, 4)
MsgBox Right("example text", 2)
MsgBox Mid("example text", 9, 2)
MsgBox Len("example text")
MsgBox Instr("example text", "am")
You can declare a string to be a fixed length with a declaration like
Dim S As String * 20
This declares a string of 20 characters.

Dim e As Date
e = DateValue("Jun 19, 2010")
MsgBox Year(e)
Dim a As Date, b As Date
a = DateValue("Jun 19, 2010")
b = DateAdd("d", 3, a)
MsgBox b
MsgBox Now
MsgBox Hour(Now)
MsgBox TimeValue("9:20:01 am")
Dim y As Double
y = TimeValue("09:20:01")
MsgBox y
DateAdd ( interval, number, date )
number is the number of intervals that you wish to add.
date is the date to which the interval should be added.
y: Day of the year
d: Day
w: Weekday
ww: Week
h: Hour
n: Minute
s: Second
DateAdd("yyyy", 3, "22/11/2003")
Result: '22/11/2006'

DateAdd("q", 2, "22/11/2003")
Result: '22/05/2004'

DateAdd("m", 5, "22/11/2003")
Result: '22/04/2004'

DateAdd("n", 51, "22/11/2003 10:31:58 AM")
Result: '22/11/2003 11:22:58 AM'

DateAdd("yyyy", -1, "22/11/2003")
Result: '22/11/2002'

Dim s(1 To 5) As String
s(1) = "Lord of the Rings"
s(2) = "Speed"
s(3) = "Star Wars"
s(4) = "The Godfather"
s(5) = "Pulp Fiction"
MsgBox s(4)
Dim Films(1 To 5, 1 To 2) As String
Dim i As Integer, j As Integer
For i = 1 To 5
    For j = 1 To 2
        Films(i, j) = Cells(i, j).Value
    Next j
Next i
MsgBox Films(4, 2)
Option Explicit
to force explicit declarations of variables before they are used. Put at the beginning of the module.
Procedures in separate modules can have the same name.
Most naming conflicts can be resolved by preceding each identifier with a qualifier that consists of the module name and, if necessary, a project name. For example:
YourProject.YourModule.YourSub MyProject.MyModule.MyVar

The preceding code calls the Sub procedure YourSub and passes MyVar as an argument.
To call a Sub procedure:
・ no Call, no parentheses
・ with Call and parentheses.
Procedure1 99800, 43100
Call Procedure1(380950, 49500)
to call a function:
・ with parentheses and return value assigned.
・ no parentheses and no return value assigned
Answer3 = MsgBox("Are you happy with your salary?", 4, "Question 3")
MsgBox "Task Completed!", 0, "Task Box"

If you include parentheses in the preceding example, the statement causes a syntax error.
You can list named arguments in any order.
MsgBox Title:="Task Box", Prompt:="Task Completed!"
answer3 = MsgBox( Title:="Question 3", Prompt:="Are you happy with your salary?", Buttons:=4)

type the word VBA followed by a period, and you’ll see a drop-drop list of these functions:

Asc: Converts the first character of string to its ASCII value
Chr: Converts an ANSI value to a character
InStr: Returns the position of a string within another string
InStrRev: Returns the position of a string within another string, beginning at the back end of the string
Join: Returns a string created by joining a number of substrings contained in an array

UCase: Converts a string to uppercase
LCase: Returns a string converted to lowercase

Right: Returns a specified number of characters from the right of a string
Left: Returns a specified number of characters from the left of a string

Len: Returns the length of a string, in characters

Trim: Returns a string without leading and spaces and replaces multiple spaces with a single space
RTrim: Returns a copy of a string with no trailing spaces
LTrim: Returns a copy of a string with no leading spaces

Mid: Returns a specified number of characters from a string
MidB: Returns a specified number of bytes from a specified position in a string string
Replace: Returns a string in which one substring is replaced with another
Space: Returns a string with a specified number of spaces
Split: Returns an array consisting of a number of substrings
Str: Returns a string representation of a number
StrComp: Returns a value indicating the result of a string comparison
StrConv: Returns a string variant converted as specified
String: Returns a repeating character or string
StrReverse: Returns the characters of a string in reverse order
Val: Returns the numbers contained in a string

Abs: Returns the absolute value of a number

Sin: Returns the sine of a number
Cos: Returns the cosine of a number
Tan: Returns the tangent of a number
Atn: Returns the arctangent of a number

Exp: Returns the base of the natural logarithms (e) raised to a power
Log: Returns the natural logarithm of a number

Fix: Returns the integer portion of a number
Int: Returns the integer portion of a number

Hex: Converts from decimal to hexadecimal
Oct: Converts from decimal to octal

Rnd: Returns a random number between 0 and 1
Round: Rounds a number to a specific number of decimal places
Sgn: Returns an integer that indicates the sign of a number
Sqr: Returns the square root of a number

DateAdd: Returns a date with a specific date interval added to it
DateDiff: Returns the difference between two dates as a time interval
DatePart: Returns an integer containing a specific part of a date
DateSerial: Returns a date for a specified year, month, and day

DateValue: Converts a string to date
TimeValue: Converts a string to a time serial number

Year: Returns the year of a date
Month: Returns the month of a date
Day: Returns the day of the month of a date
Hour: Returns the hour of a time
Minute: Returns the minute of a time
Second: Returns the second of a time

Now: Returns the current system date and time
Time: Returns the current system time
Date: Returns the current system date

MonthName: Returns a string indicating the specified month

Timer: Returns the number of seconds since midnight
TimeSerial: Returns the time for a specified hour, minute, and second
Weekday: Returns a number representing a day of the week
Weekday Name: Returns a string indicating the specified weekday

Dir: Returns the name of a file or directory that matches a pattern
CurDir: Returns the current path
EOF: Returns True if the end of a text file has been reached
FileAttr: Returns the file mode for a text file
FileDateTime: Returns the date and time when a file was last modified
FileLen: Returns the number of bytes in a file
FreeFile: Returns the next file number available for use by the Open statement
GetAttr: Returns a code representing a file attribute
Input: Returns a specific number of characters from an open text file
Loc: Returns the current read or write position of a text file
LOF: Returns the number of bytes in an open text file
Seek: Returns the current position in a text file

IsArray: Returns True if a variable is an array
IsDate: Returns True if a variable is a date
IsEmpty: Returns True if a variable has not been initialized
IsError: Returns True if an expression is an error value
IsMissing: Returns True if an optional argument was not passed to a Procedure
IsNull: Returns True if an expression contains no valid data
IsNumeric: Returns True if an expression can be evaluated as a number
IsObject: Returns True if an expression references an OLE Automation object

LBound: Returns the lower bound of an array
UBound: Returns the upper bound of an array
Array: Returns a variant that contains an array

CBool: Converts to Boolean
CByte: Converts to byte
CCur: Converts to currency
CDate: Converts to date
CDbl: Converts to double
CDec: Converts to decimal
CInt: Converts to integer
CLng: Converts to long
CSng: Converts to single
CStr: Converts to string
CVar: Converts to variant
CVDate: Converts to date
Choose: Selects and returns a value from a list of arguments
CreateObject: Creates an OLE Automation object
CVErr: Returns a user-defined error type
DoEvents: Yields execution so the operating system can process other events
Error: Returns the error message that corresponds to an error number
Format: Returns an expression in a particular format
FormatCurrency: Returns a number as a string, formatted as currency
FormatDateTime: Returns a number as a string, formatted as a date and/or time
FormatNumber: Returns a number as a formatted string
FormatPercent: Returns a number as a string, formatted as a percentage
GetAll: Returns a list of key settings and their values (originally created with SaveSetting) from an application’s entry in the Windows registry
GetObject: Retrieves an OLE Automation object from a file
GetSetting: Returns a key setting value from an application’s entry in the Windows registry
IIf: Returns one of two parts, depending on the evaluation of an expression
InputBox: Displays a box to prompt a user for input, and returns the value entered
MsgBox: Displays a modal message box and returns the ID of the button clicked
RGB: Returns a number representing an RGB color value
Shell: Runs an executable program
Switch: Evaluates a list of expressions and returns a value associated with the first expression in the list that is True
Tab: Positions output in an output stream
TypeName: Returns a string that describes the data type of a variable
VarType: Returns a value indicating the subtype of a variable

Saturday, September 6, 2014

We need to resurrect the hyphen

We need to resurrect the hyphen.
Lots of people out there, especially native speakers, seem to be ignoring the hyphen.
The hyphen is important to keep the sentence  syntactically coherent.

Let's see some examples:
A 21-year-old girl.
A face-to-face meeting.
An out-of-the-question issue.
A state-of-the-art device.
An out-of-stock book.
An up-to-date account
An out-of-date curriculum
An I-told-you-you smile
A step-by-step explanation
A day-by-day basis

Each of the hyphenated expressions acts as an adjective and can be put without problems before a noun.
But if you remove the hyphen, the syntax of the sentence changes:
A face to face meeting: a facial expression to attend a meeting.
A state of the art device: the state of devices of art.

With the hyphen you are indicating that the whole expression acts as an adjective and can be put before a noun.
If the syntax of the sentence doesn't change, the hyphen in unnecessary.
For example should it be "egg beater" or "egg-beater"?
"Egg" is a noun, "beater" is also a noun. In terms of syntax, it is perfectly possible to have a noun followed by another noun. The first noun acts as an adjective. So in my opinion the hyphen in unnecessary because it doesn't change the syntax.

Another example: man-eating shark.
Here we are clearly talking about a shark that eats human beings.
But without the hyphen the meaning changes completely. It becomes a man that is eating shark meat.

Other examples:
A good-looking girl (a girl that looks good)
A money-making machine (a machine that makes money)
A pain-inducing pill (a pill that induces pain)
A bed-wetting child (a child that wets the bed)
A movie-watching audience (an audience that watches movies)

You can also make hyphenated sentences with the past participle:
A pill-induced pain (a pain induced by pain)
A machine-made money (money made by a machine)
A child-wetted bed (a bed wetted by a child)
An audience-watched movie (a movie watched by an audience)