How Split Functon works

Split Function:

Description:

Returns a zero-based, one-dimensional array containing a specified number of substrings.

Syntax:

Split(expression[, delimiter[, limit[, compare]]])

Arguments:

Part	Description
expression	Required. String expression containing substrings and delimiters. If expression is a zero-length string(""), Split returns an empty array, that is, an array with no elements and no data.
delimiter	Optional. String character used to identify substring limits. If omitted, the space character (" ") is assumed to be the delimiter. If delimiter is a zero-length string, a single-element array containing the entire expression string is returned.
limit	Optional. Number of substrings to be returned; –1 indicates that all substrings are returned.
compare	Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values.

 

Example :

Sub SplitFn()

Dim Strname() As String

Strname = Split("HELLO WORLD")

MsgBox (Strname(0) & Chr(10) & Strname(1))

End Sub

 

 

 

 

Read More:

How to break one cell values into multiple rows in Macro

 

 

 

 

How to open any file using marcos

To open any file which is placed at any location in system please use below code:


Below example both file is placed at the same location . so I am directly reading directory name but if you want to get from other location you can modify the code.

Sub fileopen()
Dim fname As String, wbTarget As Workbook
''''''/**********provide the filename which need to open ***********/''''''''''
fname = InputBox(Prompt:="Name of the file:", _
    Title:="Please Enter file name", Default:="myfile.xlsx")

''''''/**********filelocation can be get by User ***********/''''''''''

Filename = ActiveWorkbook.Path & "\" & fname

''''''/*********below is the function to open the file ***********/''''''''''
Set wbTarget = Workbooks.Open(Filename)

End Sub

 Input#

 Output#

 

 

 

 

How Input # Statement works in VBA  

 

 

How DateAdd functions works in VBA

DateAdd Function:

It returns a Variant (Date) containing a date to which a specified time interval has been added.

Syntax :

DateAdd(interval, number, date)

The DateAdd function syntax has below arguments:

Part	Description
interval	Required. String expression that is the interval of time you want to add. Please refer below table .
number	Required. Numeric expression that is the number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past).
date	Required. Variant (Date) or literal representing date to which the interval is added.

 

Below table represent how Interval can be used:

The interval argument has these settings:

Setting	Description
yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weekday
ww	Week
h	Hour
n	Minute
s	Second

Remarks:

You can use the DateAdd function to add or subtract a specified time interval from a date. For example, you can use DateAdd to calculate a date 30 days from today or a time 45 minutes from now.

To add days to date, you can use Day of Year ("y"), Day ("d"), or Weekday ("w").

Example:

This example takes a date and, using the DateAdd function, displays a corresponding date a specified number of months in the future.

 

Sub Adddt() Dim FirstDate As Date    ' Declare variables. Dim IntervalType As String Dim Number As Integer Dim Msg IntervalType = "m"    ' "m" specifies months as interval. FirstDate = InputBox("Enter a date") Number = InputBox("Enter number of months to add") Msg = "New date: " & DateAdd(IntervalType, Number, FirstDate)   End Sub

 

 

Date Statement

Date Statement

It Sets the current system date.

Syntax

Date = date

Note: For systems running Microsoft Windows 95, the required date specification must be a date from January 1, 1980 through December 31, 2099. For systems running Microsoft Windows NT, date must be a date from January 1, 1980 through December 31, 2079. For the Macintosh, date must be a date from January 1, 1904 through February 5, 2040.

Example

This example uses the Date statement to set the computer system date. In the development environment, the date literal is displayed in short date format using the locale settings of your code.

Dim MyDate MyDate = #February 12, 1985#    ' Assign a date. Date = MyDate    ' Change system date.

  You may like to visit :

How DATE Function works in VBA

DATE AND TIME Function / References in Excel

How DATE Function works in VBA

DATE Function

Date function returns a Variant (Date) containing the current system date.

Syntax

Date

Remarks

To set the system date, use the Date statement.

Date, and if the calendar is Gregorian, Date$ behavior is unchanged by the Calendar property setting. If the calendar is Hijri, Date$ returns a 10-character string of the form mm-dd-yyyy, where mm (01-12), dd (01-30) and yyyy (1400-1523) are the Hijri month, day and year. The equivalent Gregorian range is Jan 1, 1980 through Dec 31, 2099.

Example

Sub Date_fn()
Dim TodayDate
TodayDate = Date    ' TodayDate contains the current system date.
MsgBox ("Today's Date: " & TodayDate)
End Sub

Output:

 

Delete Multiple worksheets using VBA

Below is one more way to delete multiple worksheets except active worksheet using VBA.

 Assign below code to submit button:

Sub Del_Worksheets()

Dim Wrksheet As Worksheet

For Each Wrksheet In ThisWorkbook.Worksheets

If Wrksheet.Name <> ThisWorkbook.ActiveSheet.Name Then

Application.DisplayAlerts = False

Wrksheet.Delete

Application.DisplayAlerts = True

End If

Next Wrksheet

End Sub

Earlier approach :

How to delete multiple worksheets in one click using Macro :





 

VBA code to Hide & Unhide multiple Worsheets

Hiding worksheet manually is a tedious task when its more than one right ........No Worries here we have VBA code which HIDE and UNHIDE the multiple worksheets

HIDE :


Use the below highlighted code to hide the multiple worksheets:

Sub Hide_worksheet()

Dim Wrksheet As Worksheet

For Each Wrksheet In ThisWorkbook.Worksheets

If Wrksheet.Name <> ThisWorkbook.ActiveSheet.Name Then

Wrksheet.Visible = xlSheetHidden

End If

Next Wrksheet

End Sub

Output:

 UNHIDE:

If you have multiple worksheet hided and you want to open them than below is the VBA code to unhide the multiple worksheets .....

Sub unhide_worksheet()

Dim Wrksheet As Worksheet

For Each Wrksheet In ActiveWorkbook.Worksheets

Wrksheet.Visible = xlSheetVisible

Next Wrksheet

End Sub

 You may like to visit below :

How ForEach works in VBA
For.. Next Loop in VBA with example



 

How SQLRetrieve works in VBA

SQLRetrieve

SQLRetrieve retrieves all or part of the results from a previously executed query.

Before using SQLRetrieve, you must establish a connection with SQLOpen, execute a query with SQLExecQuery, and have the results pending.

This function is contained in the Xlodbc.xla add-in. Before use of this function, one must establish a reference to the add-in by using the References command.

Syntax

SQLRetrieve(ConnectionNum, DestinationRef, MaxColumns, MaxRows, ColNamesLogical, RowNumsLogical, NamedRngLogical, FetchFirstLogical)

Part	Description
ConnectionNum	Required. The unique connection ID returned by SQLOpen and for which you have pending query results that were generated by SQLExecQuery. If ConnectionNum is not valid, SQLExecQuery returns Error 2015.
DestinationRef	Optional. A Range object that specifies where the results should be placed. This function overwrites any values in the cells, without confirmation. If DestinationRef refers to a single cell, SQLRetrieve returns all the pending results in that cell and in the cells to the right of and below it. If DestinationRef is omitted, the bindings established by previous calls to SQLBind are used to return results. If no bindings exist for the current connection, SQLRetrieve returns Error 2023. If a particular result column hasn’t been bound and DestinationRef is omitted, the results are discarded.
MaxColumns	Optional. The maximum number of columns returned to the worksheet, starting at DestinationRef. If MaxColumns specifies more columns than are available in the result, SQLRetrieve places data in the columns for which data is available and clears the additional columns. If MaxColumns specifies fewer columns than are available in the result, SQLRetrieve discards the rightmost result columns until the results fit the specified size. The order in which the data source returns the columns determines column position. If MaxColumns is omitted, all the results are returned
MaxRows	Optional. The maximum number of rows to be returned to the worksheet, starting at DestinationRef. If MaxRows specifies more rows than are available in the results, SQLRetrieve places data in the rows for which data is available and clears the additional rows. If MaxRows specifies fewer rows than are available in the results, SQLRetrieve places data in the selected rows but doesn’t discard the additional rows. You can retrieve extra rows by using SQLRetrieve again and setting FetchFirstLogical to False. If MaxRows is omitted, all the rows in the results are returned.
ColNamesLogical	Optional. True to have the column names be returned as the first row of results. False or omitted to have the column names not be returned.
RowNumsLogical	Optional. Used only when DestinationRef is included in the function call. True to have the first column in the result set contain row numbers. False or omitted to have the row numbers not be returned. You can also retrieve row numbers by binding column 0 (zero) with SQLBind.
NamedRngLogical	Optional. True to have each column of the results be declared as a named range on the worksheet. The name of each range is the resulting column name. The named range includes only the rows that are returned with SQLRetrieve. The default value is False.
FetchFirstLogical	Optional. Allows you to request results from the beginning of the result set. If FetchFirstLogical is False, SQLRetrieve can be called repeatedly to return the next set of rows until all the result rows have been returned. When there are no more rows in the result set, SQLRequest returns 0 (zero). If you want to retrieve results from the beginning of the result set, set FetchFirstLogical to True. To retrieve additional rows from the result set, set FetchFirstLogical to False in subsequent calls. The default value is False.

SQLRetrieve Output Value

Ø  SQLRetrieve returns the number of rows in the result set.

Ø  If SQLRetrieve is unable to retrieve the results on the specified data source or if there are no results pending, it returns Error 2042. If no data is found, SQLRetrieve returns 0 (zero).

 

Remarks

Before calling SQLRetrieve, you must do the following:

1. Establish a connection with a data source by using SQLOpen.

2. Use the connection ID returned in SQLOpen to send a query with SQLExecQuery.

Example

This example runs a query on the Northwind database. The result of the query, displayed on Sheet1, is a list of all products that are currently on order.

databaseName = "NorthWind" queryString = _     "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" chan = SQLOpen("DSN=" & databaseName) SQLExecQuery chan, queryString Set output = Worksheets("Sheet1").Range("A1") SQLRetrieve chan, output, , , True SQLClose chan

 How ADO(ActiveX Data Objects)DB connection and Recordset work

How to connect to Teradata and extract DB using VBA

How to connect to Cisco Information Server