Literature Review
Chapter 1. Basic Language
How to Create an Array in BASIC Language To create an array, the DIM (dimension) command is used. The DIM statement has the following syntax for a one dimensional array: DIM arrayName(n). Where “n” is a whole number.
For example DIM Score (5) will reserve 6 spaces,
Score (0) Score (1), Score (2), Score (3), Score (4) and Score (5) in the memory to hold Numeric values.
While DIM NAME$ (10) will reserve 11 memory locations to store string values, that is,
NAME$(0) … NAME$(10).
The number inside the parentheses of the individual variables are called subscripts, and each variable is called a subscripted variable or element.
The syntax for a two dimensional array is: DIM arrayName(m,n). “m,n” are whole numbers. Operations on an Array
The following operation can be performed on an array. They are; a. Input operation b. Output operation c. Arithmetic operation Example 1: Create and access array of 10 integers REM An array to create and access 10 integers DIM IN (10) IN (1) = 10 IN (2) = 11 IN (3) = 12 IN (4) = 13 IN (5) = 14 IN (6) = 15 IN (7) = 10 IN (8) = 11 IN (9) = 12 IN (10) = 13 PRINT IN (5) PRINT “THE SUM OF IN (2) AND IN (7) IS”; IN (2) + IN (7) END [run] OUTPUT: THE SUM OF IN (2) AND IN (7) IS 21
7. Arrays are indexed collections of numbers or strings. Array elements can be manipulated by scalar numeric and string operations In addition, entire arrays may be manipulated by matrix statements.
1.1 Array Declarations
1.1.1 General Description
An option in the option-statement may be used to define the lower bound for all array subscripts within a program-unit that are not explicitly stated. By use of an option-statement the subscripts of all such arrays may be declared to have a lower bound of zero or one; if no such declaration occurs, the lower bound shall be one. Arrays may have one, two, or three dimensions. The number of dimensions and subscript bounds for each dimension are declared in the declare-statement or dimension-statement. All array-names, except those appearing in a function-parm-list or a procedure-parm-list, shall be declared in one and only one such statement. If not explicitly declared, the lower subscript bound for a given dimension is one or zero, depending on the BASE option.
Upper bounds shall always be explicitly declared. A one-dimensional array with subscripts 1 to 10 or 1980 to 1989 or -9 to 0 contains 10 elements. A two-dimensional array with subscript bounds 1 to 10 for each dimension contains 100 elements. Similarly, a three-dimensional array with subscript bounds 1 to 10 for each dimension contains 1000 elements. A declare-statement can be used to dimension numeric-arrays as well as to declare maximum lengths for string-variables and string-arrays, and to dimension string-arrays. A dimension statement can be used to dimension arrays, but not to declare the maximum length of strings in string-arrays.
1.1.2 Syntax
1. dimension-statement
2. dimension-list
3. array-declaration
4. numeric-array-declaration – DIM dimension-list = array-declaration (comma array-declaration)* = numeric-array-declaration / string-array-declaration
5 . bounds
— 6. bounds-range =
7. signed-integer =
8. string-array-declaration =
9. option >
10. string-declaration >
11. numeric-declaration >
12 . numeric-function-ref >
13. maxsize-argument —
14. bound-argument = The number of bounds-ranges or three. left-parenthesis bounds-range (comma bounds-range)* right-parenthesis signed-integer TO signed-integer / signed-integer sign? integer string-array bounds BASE ( 0 / 1 ) string-array-declaration length-max? numeric-array-declaration MAXSIZE maxsize-argument / SIZE bound-argument / LBOUND bound-argument / UBOUND bound-argument left-parenthesis actual-array right-parenthesis left-parenthesis actual-array (comma index)? right-parenthesis in a bounds shall be one, two. An array that is named as a formal-array of a definedfunction, a subprogram, a program, or a picture-def shall not be declared in a declare-statement or dimension-statement (since the formal-array in the function- or procedure-parm-list serves as its declaration). Any other array shall be so declared in a lower numbered line than any reference to that array or one of its elements.
Any reference to an array and its elements shall agree in dimensionality with the declaration of that array in a declare-statement, a dimension-statement, or as a function- or procedure-parameter. No numeric- or string-array shall be dimensioned or declared more than once in a program-unit. If the optional lower bound (the first signed-integer) is included in the bounds-range, it shall be less than or equal to the upper bound (the second signed-integer). If the lower bound is not specified, then the upper bound shall not be less than the default lower bound, which may be zero or one, depending on the BASE option. An option-statement with a BASE option, if present at all, shall occur in a lower-numbered line than any declare-statement or dimension-statement or any MAT statement that uses a numeric
array-value in the same program-unit. A program-unit shall contain at most one BASE option. If a bound-argument does not specify an index, the actualarray shall be declared as one-dimensional.
1.1.3 Examples
DIM A(6), B(10,10), B$(100), D(1 TO 5, 1980 TO 1989)
DIM A$ (4,4), C(-5 TO 10) 10.
A$(3 TO 21) * 8 12. SIZE(A,1)
SIZE(B$,2)
SIZE(X) LBOUND(A) UBOUND(C$,2)
1.1.4 Semantics
Each array-declaration declares the named array named to be either one-, two-, or three-dimensional, according to whether one, two, or three bounds-ranges are specified in the bounds for the array. In addition, the bounds specify the maximum and optionally minimum values that subscripts for the array shall have. If a minimum subscript is not explicitly declared and no BASE option occurs within the program-unit, then it shall be implicitly declared to be one.
The BASE option in an option-statement is local to the program-unit in which it occurs and declares the minimum value for all array subscripts in that program-unit that are not explicitly declared. If the execution of a program reaches a line containing a dimension-statement, then it shall proceed to the next line with no further effect. String-array-declarations appearing in a string-declaration may include a length-max, which sets the maximum length of each element of the string-array. As with simple-string-variables, if there is no length-max in the string-declaration, then the length-max, if any, of the string-type shall take effect.
If there is no length-max in either, then the implementation-defined length-max, if any, shall take effect. The value of SIZE(A,N) in which A is an actual-array and N is an index shall be the current number of permissible values for the Nth subscript of the array named by A (the value of N is
rounded to the nearest integer, and the subscripts of A are indexed from left to right, starting at one). The value of SIZE (A) shall be the current number o.f elements in the entire array A. The value of MAXSIZE(A) shall be the total number of elements of the entire array named by A permitted by the arraydeclaration . The value of LBOUND(A,N), where A is an actual-array and N is an index, shall be the current minimum value allowed for the Nth subscript of the array named by A. The value of UBOUND(A,N) shall be the current maximum value allowed for the Nth subscript of array A. As in the SIZE function, the value of N is rounded to the nearest integer, and the subscripts of array A are indexed from left to right, starting at one. The LBOUND and UBOUND functions may be called with a single argument, provided that argument is a vector, in which case the values of LBOUND and UBOUND are the current minimum and maximum values allowed for the subscript of the vector.
the word “vector” shall mean a “one-dimensional array” and the word “matrix” shall mean a “two-dimensional array”.
Overview
Index notation is used to specify the elements of an array.[1] Most current programming languages use square brackets [] as the array index operator. Older programming languages, such as FORTRAN, COBOL, and BASIC, often use parentheses () as the array index operator.
Discussion
Example:
| Language | Example |
| C++ | int ages[] = {49, 48, 26, 19, 16}; int myAge = ages[2]; |
| C# | int[] ages = {49, 48, 26, 19, 16}; int myAge = ages[2]; |
| Java | int[] ages = {49, 48, 26, 19, 16}; int myAge = ages[2]; |
| JavaScript | var ages = [49, 48, 26, 19, 16]; int myAge = ages[2]; |
| Python | ages = [49, 48, 26, 19, 16] my_age = ages[2] |
| Swift | var ages:[Int] = [49, 48, 26, 19, 16] var my_age = ages[2] |
As an operator, square brackets either provide the value held by the member of the array (Rvalue) or change the value of member (Lvalue). In the above example, the member that is two offsets from the front of the array (the value 26) is assigned to the variable named myAge. The dereference operator of [2] means to go the 2nd offset from the front of the ages array and get the value stored there. In this case, the value would be 26. In most current programming languages, the array members (or elements) are referenced starting at zero. The more common way for people to reference a list is by starting with position one. Consider:
| Position | Index | Miss America | Other Contests |
| zero offsets from the front | ages[0] | Winner | 1st Place |
| one offset from the front | ages[1] | 1st Runner Up | 2nd Place |
| two offsets from the front | ages[2] | 2nd Runner Up | 3rd Place |
| three offsets from the front | ages[3] | 3rd Runner Up | 4th Place |
| four offsets from the front | ages[4] | 4th Runner Up | 5th Place |
Saying that my cousin is the 2nd Runner-Up in the Miss America contest sounds so much better than saying that she was in 3rd Place. We would be talking about the same position in the array of the five finalists.
ages[3] = 20;
This is an example of changing an array’s value by assigning 20 to the 4th member of the array and replacing the value 19 with 20. This is an Lvalue context because the array is on the left side of the assignment operator.
Key Terms
array member
An element or value in an array.
index
An operator that allows us to reference a member of an array.
offset
The method of referencing array members by starting at zero.
VBA FUNCTIONS AND ARRAYS Subroutines versus Functions . The VBA code that you write in Visual Basic module is known as a procedure. . You can write two types of procedures: A subroutine procedure: A group of VBA statements that perform actions with Excel, i.e. like the one we used in trapezoidal rule. A function procedure:
A group of VBA statements that perform a calculation and return a single value. Common Types of Functions in Excel Built-in Functions: . Excel includes many worksheet functions that we often use. . Examples include SUM, AVERAGE, MEAN, SIN, … etc. . Each function takes one or more arguments and returns a single value.
VBA or User-defined Functions: . Similar to what a build-in function can do but can do more. . Simply, it is a piece of programming code that gives you a value back whenever you use it. ١٧٦ Looking at Functions . Every function must start with the keyword Function and end with an End Function statement. Function Multiply (x , y) Statements Multiply = x * y End Function . This function, named “Multiply”, takes two arguments (named x and y). .
Arguments are a list of variables representing values that are passed to the function. . Functions can have no argument, one argument, or multiple arguments. . Statements are various line of code, one of which will be of the form “Multiply = expression”, i.e. Multiply = x*y. . This is the same name as on the first line and the value of the expression is assigned to it. This is becomes the return value of the function . When you run the function, it returns a single value that is the value of ‘x’ multiplied by ‘y’.
ARRAYS . An array is a group of variables that have a common name. . You refer to specific variable in the array by using the array name and an index number. Array example: . Assume that you want to store the months of the year in your subroutine. . You may define an array of 12 string variables to hold the months of the year. .
If the array name is MonthNames, you can refer to the first element of the array (i.e. January) as MonthNames (1), the second element as MonthNames (2), and so on. ١٨١ DeclaringArrays .
You declare an array with a Dim statement, just like you declare regular variable. . However, you also need to specify the number of elements in the array. . You do this by specifying the first index number, the keyword to, and the last index number as shown in the following: Dim MyArray (1 to 100) as Integer .
When you declare an array, you can specify only the upper index. . VBA assumes that 0 is the lower index. .
Therefore, the following statements both declare the same array: Dim MyArray (0 to 100) as Integer Dim MyArray (100) as Integer
Note: . If you want VBA to assume that 1 is the lower index for the arrays include the following statement before any sub or Function in your module Option Base 1 . This statement forces VBA to use 1 as the first index number.
If this statement is present, the following statements are identical: Dim MyArray (1 to 100) as Integer Dim MyArray (100) as Integer Declaring Multi-dimensional Arrays . The arrays created above are all one-dimensional arrays. . Arrays you create in VBA can have as many as 60 dimensions. . The following example declares a 100-integer array with two dimensions: Dim MyArray (1 to 10, 1 to 10) as Integer .
You can think of this array as a 10 by 10 matrix. . To refer to a specific element in this array, you need to specify two index numbers. . Again, if you are thinking of the array in terms of a 10 by 10 matrix, the following example shows how you can assign a value to an element located in the third row and fourth column of the array: MyArray (3, 4) = 125
Dynamic arrays: . It can be hard to know ahead of time exactly how many items you are going to put onto an array. . When you solve a continuous beam, for example, you don’t know how many spans you are going to use. It could be 2 or even 10. . So, a dynamic array is simply an array whose size is not predetermined. To use it, you specify a size, and then it acts just like any other array. Declaring Dynamic arrays: . A dynamic array is declared just like any other kind of array: with the Dim statement. . You declare it with a blank set of parentheses: Dim MyArray ( ) as Integer . Before you can use this array, you must use the ReDim statement to tell VBA how many elements the array has. . You can use the ReDim statement any number of times, changing the array’s size as often as you need. ReDim MyArray (1 to 10)
Warning . When you redimension an array by using ReDim you delete the values stored in the array elements. . You can avoid this by using the Preserve keyword. ReDim Preserve MyArray (1 to 20) . If this array has 10 elements stored on it and you execute the preceding statement, the first 10 elements will remain unchanged, and the array will have room to store 10 more elements. Example: For a part of continuous beam analysis with any number of spans, create a Sub to read and store the length of each span
https://feng.stafpu.bu.edu.eg/Civil%20Engineering/2469/crs-14261/Files/Lecture%204.pdf
Significance of the Study
Chapter 2 –
Introduction
Introduction Visual Basic 2008 Visual Basic 2008 is the latest version of Visual Basic. It is almost similar to Visual Basic 2005 but it has added many new features. Visual Basic 2008 is a full-fledged Object-Oriented Programming (OOP) Language, so it has caught up with other OOP languages such as C++, Java, C# and others. However, you don’t have to know OOP to learn VB2008. In fact, if you are familiar with Visual Basic 6, you can learn VB2008 effortlessly because the syntax and interface are similar.
Visual basic evolved from BASIC (Beginners’ All-purpose Symbolic Instruction Code). The BASIC language was created by Professors John Kemeny and Thomas Kurtz of Dartmouth College in the mid1960. It is a carefully constructed English-Like language basically used by the programmers to write simple computer programs. Some of the versions are Microsoft QBASIC, QUICKBASIC, GWBASIC, IBM BASICA, Apple BASIC and etc. The popularity and widespread use of BASIC with different types of computers brought further enhancements of the language, and eventually led to the GUIbased Visual Basic in tandem with the development of Microsoft Windows.
Visual Basic made programming even easier for beginners and season programmers a like as it save considerable programming time by providing many ready-made components. Since then, Visual Basic has also evolved into many versions, until recently, Visual Basic 2008. VB2008 is so far the most powerful version of Visual Basic.
What is the Offset Function
The OffSet() returns the value of the cell, at an offset that you’ve specified. The syntax of this function looks like this:
OffSet(Cell reference, rows, columns, height, width)
Here’s what each parameter means
- Cell reference: refers to a single cell or range of cells.
- Rows: specify the number of rows away from the given cell. If the value entered is negative, the given cell shifts to the left.
- Columns: specify the number of columns away from the given cell. If value is negative, the cell shifts up.
Note that height and width are optional. They are used to specify the height and width of the returned range in cells. You can insert the OffSet() formula in any cell in the worksheet, except obviously, the cells from which you want the offset.
Combining Range() with OffSet()
The OffSet property is usually used in tandem with the Range property to specify a new location. Let’s take a look at this example
Range(“B1”).Offset(RowOffSet:=1, ColumnOffset:=1).Select
This code will select C2. The parameter RowOffSet is used to move 1 row from cell B1. Similarly, the parameter ColumnOffSet is used to move 1 column from cell B1. Note that the parameters have to be separated by a comma.
An easier way to accomplish the same result is to use a shorthand method, like we show below
Range(“A1”).Offset(1, 1).Select.
Here we only specified the numbers for the row and column offset. VBA will internally “know” that the first one is for the row offset, while the second one is for the column offset. If you’d like to explore the Range() function more, you can take a look at our course on VBA macros. Now, let’s move on to some more examples.
How to Specify Only Rows
It’s possible to specify just the rows and not the columns. Here’s how you can do it.
Range(“B1”).Offset(1).Select
Here we’ve omitted the column parameter and its comma. This code results in only moving 1 row down from the original cell location. It will select the entire row, not just a single cell.
How to Specify Only Columns
Similarly it’s possible to just specify the columns and omit the rows. The code will look like this:
Range(“B1”).Offset(, 1 ).Select
The comma is necessary for Excel to know that only column offset has been set.
How to Specify Negative OffSet
Can you guess how to do this? It’s simple enough. Here’s the code
Range(“D2”).Offset(-1, -1 ).Select
This makes the selection go up 1 row and 1 column from the starting position. That is from cell D2 we move up to cell C1.
How to OffSet a Range of Cells
It is also possible to specify more than one cell for the Range. Say you want to select or get the offset for a range of cells
Range(“B1:D4”).Offset(1, 1).Select
Here, we start off with the range of cells from B1 to D4. The objective is to offset this complete range of cells by 1 row and 1 column. The new range of cells will be from C2 to E5.
Combining Active Cell with Offset
The active cell refers to the currently selected cell. Let’s check a few examples that involve active cells. Here is the code to move one cell down from the currently selected cells is
ActiveCell.Offset(1,0).Select
Combining Active Cell with Range() and Offset()
The following code selects a cell in addition to four more to the right which are to be copied/pasted in another location.
Range(ActiveCell,ActiveCell.Offset(0,4)).Copy
Take note that there is a comma after the first ActiveCell instance and a double closing parenthesis before the Copy. Let us now move on to cover simple programs that use OffSet functionality to obtain desired results.
Example 1: To Count the Rows in an Excel Worksheet
Sub CountNumRows()
Dim Count1 as Long
Count1 = 0
Do
Count1 = Count1 + 1
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
MsgBox “There are” Count1 “Rows”
End Sub
In this program, we declared Count1 as a variable of type long. Count is incremented by 1. Then OffSet() is used to loop through the rows. When an empty row is encountered the loop terminates. The variable count1 contains the total number of rows.MsgBox() displays a pop-up box with the total number of rows.
Example 2: Combining Range with OffSet to Fill a Range of Cells with Even Numbers from 1 to 100.
Sub prog1()
Dim Num
Num = 0
For Row = 0 To 9
For Col = 0 To 9
Range(“A1”).Offset(Row, Col).Value = Num
Num = Num + 2
Next Col
Next Row
End Sub
In this program, A1 is the given cell and the resultant range is from A1 to J10. Variable Num is initialized to zero and is incremented by the value 2 each time in the loop. The resultant range of cells contain each and every even number without duplicates from 1 to 100.
VBA OFFSET is a command used in Visual Basic for Applications (VBA) that adjusts a cell reference by a specified number of rows and columns from a starting point. In finance, this tool is commonly used in Excel for financial modeling to dynamically refer to different cells within financial datasets. The OFFSET function enables improved flexibility, as it allows formulas to automatically adjust to changes in data.
Content of the Problem
Key Takeaways
- VBA OFFSET is a useful function in Excel for navigating and manipulating spreadsheets. It is used to return a range that is a specific number of rows and columns from a cell or range of cells. Thus, improving financial analytics and data management capabilities.
- The OFFSET function has three main arguments – the reference cell, the number of rows to move from the reference cell, and the number of columns to move from the reference cell. Additionally, there are two optional arguments – the height and width of the returned range.
- In finance, VBA OFFSET is a powerful tool for creating dynamic ranges, financial models, or dashboards. It can also be used to dynamically summarize data, create rolling charts or prepare dynamic reports, all of which can improve the accuracy and efficiency of finance-related tasks.