arrays

Arrays

One dimensional arrays

Arrays are declared the same way a variable has been declared except that the declaration of an array variable uses parenthesis. In the following example, the size of the array is mentioned in the brackets.

Dim arr1()    ‘without size

Dim arr1(5)  ‘with size of 5

Dim arr3       ‘with parameter

Arr3=array(“appple”,”orange”,”mango”)

·        Although, the array size is indicated as 5, it can hold 6 values as array index starts from ZERO.

·        Array Index cannot be negative.

·        VBScript Arrays can store any type of variable in an array. Hence, an array can store an integer, string, or characters in a single array variable.

Example

Sub array_index()

Dim arr(3)

arr(0) = "sathis"

arr(1) = "kumar"

arr(2) = "siva"

arr(3) = "karthi"

MsgBox ("first array index:" & arr(0))

MsgBox ("second array index:" & arr(1))

MsgBox ("third array index:" & arr(2))

MsgBox ("fourth arry index:" & arr(3))

End Sub

Multi dimensional array

Arrays are not just limited to a single dimension, however, they can have a maximum of 60 dimensions. Two-dimensional arrays are the most commonly used ones.

example

In the following example, a multi-dimensional array is declared with 2rows and 3 columns.

Sub multi_array()

Dim arr(1,2)

Arr(0,0)=”sathis”

Arr(0,1)=”kumar”

Arr(0,2)=”siva”

Arr(1,0)=”12”

Arr(1,1)=”15”

Arr(1,2)=”32”

Msgbox (“array index 0,0 is:”&arr(0,0))

Msgbox(“array index1,2 is:”&arr(1,2))

End sub

Dynamic array

ReDim statement is used to declare dynamic-array variables and allocate or reallocate storage space.

Example

In the following example, an array has been redefined and then the values preserved when the existing size of the array is changed.

Sub dynamicarray1()

Dim a As Variant

i = 0

ReDim a(5)

a(0) = "apple"

a(1) = "orange"

a(2) = "mango"

ReDim Preserve a(10)

For i = 3 To 10

a(i) = i

Next

For i = 0 To UBound(a)

MsgBox a(i)

Next

End Sub

file directory functions

Chdir  functions

The Microsoft Excel CHDIR statement allows you to change the current directory or folder.

Syntax

Chdir path

Example

Chdir”c:/docments”

In this example, the current directory would now be changed to C:\instructions.

chdrive

The Microsoft Excel CHDRIVE statement allows you to change the current drive.

Syntax

Chdrive drive

Example

Chdrive”c”

Chdir”c:/docments

In this example, the current drive is set to the S drive. Then the current directory is set to S:\Games.”

Curdir function

The Microsoft Excel CURDIR function returns the current path.

Syntax

Curdir(drive)

Example

Dim lresult as string

Lresult = curdir()

Dir function

The Microsoft Excel DIR function returns the first filename that matches the pathname and attributes specified. To retrieve additional filenames that match pathname and attributes, call DIR again with no arguments.

Syntax

Dir(path)

Example

Dim lresult as string

Lresult =dir(“c:/janus.doc”)

In this example, the variable called LResult would now contain the filename of the janus.doc file.

File date and time function

The Microsoft Excel FILEDATETIME function returns the date and time of when a file was created or last modified.

Syntax

Filedatetime(file path)

Example

Dim lresult as string

Lresult =filedatetime(“c:/janus.doc”)

In this example, the variable called LResult would now contain the create or last modified date for the c:/janus.doc  file.

File length function

The Microsoft Excel FILELEN function returns the size of a file in bytes.

Syntax

Filelen(file _path)

Example

Dim lresult as string

Lresult =filelen(“c:/janus.doc”)

In this example, the variable called LResult would now contain the size of the c:/janus.doc file in bytes.

Getattr function

The Microsoft Excel GETATTR function returns an integer that represents the attributes of a file, folder, or directory.

Syntax

Getattr(path)

Example

Dim lresult as string

Lresult =getattr(“c:/janus.doc”)

In this example, the variable called LResult would now contain the integer representation of the attributes of the janus.doc  file.

Setattr function

The Microsoft Excel SETATTR statement allows you to set the attributes of a file.

Syntax

          Setattr path,attributes

Example

Setattr “c:\dropbox\janus.doc” vbreadonly+vb hidden

In this example, the file called janus.doc (found in the c:\dropbox) would now be set to a ReadOnly, Hidden file.

Mkdir function

The Microsoft Excel MKDIR statement allows you to create a new folder or directory.

Syntax

Mkdir path

Example

Mkdir “c:\test\excel”

The c:\Test directory must already exist. The MKDIR statement will only attempt to create the Excel directory under the c:\Testdirectory. It will not create the c:\Test directory itself.

vba date functions

vba date functions

Date function

The Microsoft Excel DATE function returns the current system date.

Syntax

Date()

Example

The DATE function can only be used in VBA code in Microsoft Excel.

Let's look at some Excel DATE function examples and explore how to use the DATE function in Excel VBA code:

Dim currentdate as string

Currentdate=date

Date add function

The Microsoft Excel DATEADD function returns a date after which a certain time/date interval has been added.

Syntax

Dateadd (interval,number,date)

Example

 Dim ldate as date

Ldate=dateadd(“m”,5,”1/11/2018”)

Result=1/4/2019

Date diff function

The Microsoft Excel DATEDIFF function returns the difference between two date values, based on the interval specified.

Syntax

Datediff(“d”,date1,date2)

Example

Dim ldatediff as date

Ldate = datediff(“m”,22/11/2011,1/1/2012)

Result=2

Datepart function

The Microsoft Excel DATEPART function returns a specified part of a given date

Syntax

Datepart(“d”,date)

Example

Dim lvalue as integer

Lvalue = datepart(“d”,22/2/2018)

In this example, the variable called LValue would now contain the value of 22

Date serial function

The  Microsoft Excel DATESERIAL function returns a date given a year, month, and day value.

Syntax

Dateserial(year,month,day)

Example

Dim ldate as date

Ldate=dateserial(2004,5,31)

In this example, the variable called LDate would now contain the value of "5/31/2004".

Date value function

The Microsoft Excel DATEVALUE function returns the serial number of a date.

Syntax

Datevalue(date)

Example

Dim ldate as date

Ldate=datevalue(“may15,2012”)

In this example, the variable called LDate would now contain the value of 5/15/2012.

Day function

The Microsoft Excel DAY function returns the day of the month (a number from 1 to 31) given a date value.

Syntax

Day(date value)

Example

Dim lday as integer

Lday =day(“12/31/2012”)

In this example, the variable called LDay would now contain the value of 31.

Format date function

The Microsoft Excel FORMAT function takes a date expression and returns it as a formatted string.

Syntax

Format (date,format)

Example

Dim lvalue as string

Lvalue  =format(date,”yyyy/mm/dd”)

In this example, the variable called LValue would now contain the date formatted as yyyy/mm/dd.

Hour function

The Microsoft Excel HOUR function returns the hours (a number from 0 to 23) from a time value.

Syntax

Hour(serial-number)

Example

Dim lhour  as integer

Lhour =hour(“10:42:50 pm”)

In this example, the variable called LHour would now contain the value of 22.

Minute function

The Microsoft Excel MINUTE function returns the minutes (a number from 0 to 59) from a time value.

Syntax

Minute(serial-number)

Example

Dim lminute as integer

Lminute = minute(“10/13/58 pm”)

In this example, the variable called LMinute would now contain the value of 13.

Month function

The Microsoft Excel MONTH function returns the month (a number from 1 to 12) given a date value.

Syntax

Month (date value)

Example

Dim lmonth as  integer

Lmonth =month(“12/31/2011”)

In this example, the variable called LMonth would now contain the value of 12.

Month name function

The Microsoft Excel MONTHNAME function returns a string representing the month given a number from 1 to 12.

Syntax

Monthname(number)

Example

Dim  lvalue as string

Lvalue=monthname(3)

In this example, the variable called LValue would now contain the value of 'Mar'.

Now function

The Microsoft Excel NOW function returns the current system date and time.

Syntax

Now()

Example

Dim lvalue as integer

Lvalue = now

In this example, the variable called LValue would now contain the current system date and time.

Time serial function

The Microsoft Excel TIMESERIAL function returns a time given an hour, minute, and second value.

Syntax

Timeserial (hour,minute,second)

Example

The TIMESERIAL function can only be used in VBA code in Microsoft Excel.

Dim ltime as date

Ltime =timeserial(23,5,31)

In this example, the variable called LTime would now contain the value of "11:05:31 PM".

Time value function

The Microsoft Excel TIMEVALUE function returns the serial number of a time.

Syntax

Timevalue(time)

Example

The TIMEVALUE function can also be used in VBA code in Microsoft Excel.

Dim lvalue as date

Ltime = timevalue(“18:30:12”)

In this example, the variable called LTime would now contain the value of 6:30:12 PM.

Weekday function

The Microsoft Excel WEEKDAY function returns a number representing the day of the week, given a date value.

Syntax

Weekday (“date”)

Example

The WEEKDAY function can also be used in VBA code in Microsoft Excel.

Dim lweekday as integer

Lweekday=weekday(“12/31/2001”)

In this example, the variable called LWeekday would now contain the value of 2.

Weekdayname function

The Microsoft Excel WEEKDAYNAME function returns a string representing the day of the week given a number from 1 to 7.

Syntax

Weekdayname(number)

Example

Dim lvalue as string

Lvalue  =weekdayname(3)

In this example, the variable called LValue would now contain the value of 'Wed'.

Year function

The Microsoft Excel YEAR function returns a four-digit year (a number from 1900 to 9999) given a date value.

Syntax

Year(date_value)

Example

The YEAR function can also be used in VBA code in Microsoft Excel.

Dim lyear as integer

Lyear = year(“12/3/2011”)

In this example, the variable called LYear would now contain the value of 2001.

time sheet template creation

Time sheet template creation

Enter label

The first step to actually building the time sheet is to enter the appropriate labels. That includes the following headings:

•      All applicable employee information, such as name, social security number, employee identification number, department, and manager. Include only data that's truly required.
• ·    A time period. This could be the first day of the work week, the start and end date of a bimonthly time period, or even the first day of a fiscal month.
•      Generating dates is necessary. Including the names of the workdays might seem unnecessary, but your users will probably appreciate your attention to helpful details.
•      Time in and out, breaks, sick, vacation, overtime, and so on.
• ·    Subtotals and grand totals, as required.
• ·    Employee and approving manager signature lines, if required.

Enter the appropriate labels to identify your time sheet data.

3: Automate the dates

You can require users to enter the dates manually, but that leads to mistakes, even with the best trained users. If you know the exact time period, the simplest solution is to automate the required dates as follows:

1.   Have users enter the first date of the time period in cell B2.

2.   In the first cell in the Date column, A7, refer to the input date using the formula

=IF(B2<>"",B2,"")

Let the sheet generate dates based on the first date of each time period.

If the time period cell (B2) is blank, this formula returns a zero-length string. If there's a value, the formula returns it. The formula will return a date serial value until we format it (which we'll do later).

1.   In cell A8, enter the formula

 =IF(A7<>"",A7+1,"")

This formula adds 1 to the value (date) in cell A7.

1.  Copy the formula in cell A8 as needed. For instance, if you're tracking time by the week, copy the formula in cell A8 to cells A9:A13 for a total of seven rows (A7:A13). For a biweekly timesheet, you'd copy the formula to cell A20, and so on.

The next step is to enter a formula in column B that returns the name of the weekday for the dates in column A. To do so, enter the simple formula =A8 in cell B7 and copy it to cells B8:B13. (Later, we'll format B7:B13 to display the day of the week by name rather than the actual dates shown in column A.) If there's no date in cell B7, the sheet will appear empty.

4: Format Date and Day of Week columns

Right now, the General format displays serial values in the Date and Day of Week columns. First, let's format the dates in column A, as follows:

1.   Select A7:A13.

2.   Right-click the selection and choose Format Cells from the context menu.

3.   On the Numbers tab, select Date from the Category list, choose the appropriate format, such as d/m/yy, from the Type list, and click OK.

Next, format the dates in column B, as follows:

1.   Select B7:B13.

2.   Right-click the selection and choose Format Cells.

3.   On the Number tab, choose Custom from the Category list.

4.   Enter dddd in the Type field, as shown in Figure , and click OK.

The dddd format displays a date value as its day of the week.The sheet generates dates for each time period if you provide a beginning date (cell B7).

5: Enter a formula that calculates the first eight hours of each day

=IF(((D7-C7)+(F7-E7))*24>8,8,((D7-C7)+(F7-E7))*24)

If the number of hours worked in one day is greater than eight, the formula returns 8. If the number or work hours is equal to or less than 8, the formula returns that amount. For now, the formula returns 0 because there are no time values to evaluate.This formula returns the first eight hours of each work day.

6: Enter a formula that calculates overtime for each day

=IF(((D7-C7)+(F7-E7))*24>8,((D7-C7)+(F7-E7))*24-8,0)

When the number of work hours is greater than 8, this formula returns the overage.

7: Format In and Out columns

When calculating time, work with valid time values to avoid troublesome errors.

Now, you might think that a different set of formulas could handle general numbers. What would happen if you delete the *24 components in both formulas? Those formulas work up to a point, but neither handles all timelines. For instance, neither formula can handle an In value that's greater than its companion Out value (such as row 10 in Figure H).

To format the In and Out values appropriately, do the following:

1.   Select C7:F13.

2.   Right-click the selection and choose Format Cells.

3.   On the Number tab, choose Time from the Category list.

4.   Select the 00:00 format and click OK.

8: Provide In and Out default values

Enter default time values to avoid user input errors.

The default values shown here are entered using a 24-hour clock. 13:00 is 1:00 PM (check the Formula bar) and 17:00 is 5:00 PM. You can enter 1:00 and 5:00, but you must train your users to also enter the PM. Excel will assume that 1:00 is 1:00 AM and 5:00 is 5:00 AM if you don't specify that it's PM. Either way, entering time isn't intuitive for most users, so providing the default values is helpful.

In this example sheet, Saturday and Sunday aren't regular workdays, so it stores 0 values. Of course, you can customize the days normally worked to suit each employee.

9: Enter defaults for sick and vacation time

1.   Select I7:J13.

2.   Right-click the selection and choose Format Cells.

3.   On the Number tab, choose Number from the Category list and click OK.

By default, the Number format assumes two decimal places, but you might want to set that to 0. The two decimal places will allow employees to specify partial hours.

Enter default values for sick and vacation time.

10: Enter a formula that calculates daily totals

Figure K

=SUM(G7:J7)