Posts Tagged ‘Range’

Converting a Range to Recordset is a very painfull process. Currently the process which we follow is establishing a connection and then firing a Query to get the values in to a Recordset.
But now we can make a new and better approach using the Range.Value() property.
This is a better way to move data from one workbook to another without making use of the Clipboard.

Sub Test()

        Dim xlXML             As Object
        Dim adoRecordset      As Object
        Dim rng               As Range

        Set rng = ActiveSheet.Range("A1:D20")
        Set adoRecordset = CreateObject("ADODB.Recordset")
        Set xlXML = CreateObject("MSXML2.DOMDocument") 
        xlXML.LoadXML rng.Value(xlRangeValueMSPersistXML)
        adoRecordset.Open xlXML

End Sub

This adorecordset will hold the range in the form of recordset and one make use of this in the way it is required.
Happy Coding….   😉

At times we need to change cell values in a range based on a calculation. The most common way of doing it is using loop.

e.g.  We have numeric data in range A1:B10 of Sheet1 and we need to multiply all cells of this range by 2.

Sub Test()

    Dim rngData     As Range
    Dim rngCell     As Range

    Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:B10")    
    For Each rngCell In rngData
        rngCell = rngCell * 2

End Sub

In the above example, Evaluate can be used instead of looping through cells, it works faster than a loop. (more…)

VBA Trick HatSometimes we need to slice an array i.e. fetch a row/column from a multidimensional array. There is no inbuilt function in VBA to do the same and the most common way to do so is using a loop. However it can be done using a worksheet function named Index.

Syntax :

  Application.Index(Array, Row_Number, Column_Number)
  Application.WorksheetFunction.Index(Array, Row_Number, Column_Number)

To extract a column from the source array, ‘0’ should be passed as row_number argument. Similarly, to extract a row from source array, ‘0’ should be passed as column_number argument. (more…)

Custom Data Validation :: Using Formulas

Posted: June 4, 2013 by MaxFraudy in Excel
Tags: , ,

Excel provides Data Validation feature that lets you restrict the type of data that the user enters into the cell. There are many types of built-in validations that are available, like below:

Data Validation Options

Data Validation Options

The last option, i.e. Custom, can be used to put in non-standard validations using Excel formulas. Let’s see how it works using an example. Suppose you have a data table in which the first column contains the Product Code. (more…)

Did You Know :: 3D Reference in Excel

Posted: June 2, 2013 by Transformer in Excel
Tags: , ,

UntitledA reference that refers to the same range on multiple sheets is called 3D reference. Sometimes it is very useful.

e.g.  We have a workbook that has sheets for each day(Sunday,Monday..) and a sheet named Total where we need a cumulative total.

3D Reference

We need to refer to the same range in each sheet. So rather than referring to each sheet range separately and then using a sum formula, there is an easy way to do it.

                                       =SUM(FirstSheet:LastSheet! Cell Address)


Intersection of Overlapping Ranges:Space Operator

Posted: May 13, 2013 by Transformer in Excel, VBA
Tags: ,

We all know about “: operator. If one writes A1:A10 then it refers to a continuous range from A1 to A10. There is one more operator Space operatorIf this operator is used to separate two or more overlapping ranges then it refers to intersection of those ranges.

e.g.  A3:M3 D1:D6 would return D3 since it lies at the intersection of the two ranges.

Intersection of two Ranges


Highlight Active Cell’s Row and Column

Posted: May 6, 2013 by Transformer in Excel, VBA
Tags: , ,

Sometimes when we have a large amount of data in a spreadsheet then it is very difficult to identify which column and row we are looking at once we start scrolling. What if we could simply click a cell and it’s row and column are somehow highlighted so that we don’t need to strain our eye?

Highlight Active Cells Row & Column

Highlight Active Cells Row & Column


When a function is created in Excel VBA then it can be called from a sheet as well as from another procedure in the code.If one wants to check from where the function has been called then one can use the Application.Caller.

Its behavior depends on the caller. If it is called from:

  1. Range/Cell then it returns Range
  2. Shape or some control then it returns String(Name of the shape/Control)
  3. Some procedure or function then it returns Error.

The following function can be used to check how it works.

Function GetCaller()

    Dim strCallerTyp        As String

    strCallerTyp = TypeName(Application.Caller)
    Select Case strCallerTyp
        Case "Range"
            MsgBox "Called From a Range and the address is: " & Application.Caller.Address
        Case "String"
            MsgBox "Called from a control/Shape and the name is: " & Application.Caller
        Case "Error"
            MsgBox "Error:Not called from the sheet."
    End Select

End Function