Posts Tagged ‘Function’

LTRIM() Function VBA: To Remove Spaces

Posted: May 10, 2013 by Theodoulus in VBA
Tags: , ,

In VBA, LTRIM() function is used to remove the leading spaces from the given string.

The syntax for LTRIM() function is

 text is the String that you wish to remove the leading spaces from.


Ltrim(“                  UsefulGyaan”)                      ‘ returns  “UsefulGyaan”
Ltrim(“                 UsefulGyaan          “)             ‘ returns  “UsefulGyaan          “
Ltrim(“UsefulGyaan          “)                             ‘ retruns “UsefulGyaan          “

Note: – LTRIM() function doesn’t remove the spaces between or after the string.

This function returns the numbers found at the beginning of a string which is passed as an argument. It stops reading the string at the first character that it can’t recognize as part of a number.
But it ignores spaces.

Syntax: VAL(string)


Expression Result Comments
Val(“1234Excel5”) 1234 Stops reading at first alphabet
VAL(“1234.5Excel”) 1234.5 Dot (“.”) recognised as a decimal
VAL(“1234.5.56Excel”) 1234.5 VAL returns a value that is a valid number, so second dot not considered
VAL(“1234 23 34”) 12342334 Spaces ignored
VAL(“12323 .5 6”) 12323.56 Spaces ignored
VAL(“1,234.5”) 1 “,” not recognised as a thousands delimiter
VAL(“123E3Excel”) 123000 “E” recognized as “power of ten” delimiter
VAL(“123e3Excel”) 123000 “e” recognized as “power of ten” delimiter
VAL(“123D3Excel”) 123000 “D” recognized as “power of ten” delimiter
VAL(“123d3Excel”) 123000 “d” recognized as “power of ten” delimiter

Unique Values From Data Using VBA Dictionary Object

Posted: April 19, 2013 by MaxFraudy in Excel, VBA
Tags: , ,

Very often when we are working with huge amount of data, many a times we need to get unique values from a list. e.g. from region-wise sales data, a unique list of product names. Also, when using form controls like a dropdown (combobox) or a list box, we want to get unique values from a range that will be used as input for that control. While there are many ways to get this done, perhaps the most efficient one is using dictionary objects.

The program written below does exactly as required (Thanks to Transformer for his code). It is a parameterised function that takes the following arguments:

   varSource = source data from which unique values are to be extracted
   blnPaste = TRUE if you want the result to be pasted to a range, FALSE by default
   rngDest = range where the results are to be pasted
   blnCase = TRUE if the list is to case-sensitive, FALSE by default. The results will be in proper case for FALSE
   blnCounts = TRUE if you want to see the number of occurrences of each item as well, FALSE by default

Function varUniqueList(varSource As Variant, _
                   Optional blnPaste As Boolean = False, _
                   Optional rngDest As Range, _
                   Optional blnCase As Boolean = True, _
                   Optional blnCounts As Boolean = False)

Dim lngLoop         As Long
Dim strKey          As String
Dim objDictionary   As Object

If IsArray(varSource) Then  'proceeding only if the array is non-empty
    Set objDictionary = CreateObject("Scripting.Dictionary")

    With objDictionary
        For lngLoop = LBound(varSource) To UBound(varSource)

            If blnCase Then
                strKey = varSource(lngLoop)
                strKey = StrConv(varSource(lngLoop), vbProperCase)
            End If
        .Item(strKey) = .Item(strKey) + 1 'counting occurences
        Next lngLoop

        If blnCounts Then
            varUniqueList = Array(.keys, .items)
            varUniqueList = Array(.keys)
        End If

        'pasting results in destination range
        If blnPaste And Not rngDest Is Nothing Then 
            rngDest.Resize(.Count, 1-blnCounts).Value = Application.Transpose(varUniqueList)
        End If
    End With

    Set objDictionary = Nothing
    Exit Function
End If

End Function

This function can be called from any other procedure like below. Here rngSource is the range which contains the data and rngDest is the range where results are to be pasted.

Sub Start() 
Dim varData      As Variant 
Dim varResult    As Variant 
Dim rngRes       As Range 

    With ThisWorkbook.Worksheets("Sheet1") 
        vardata = Application.Transpose(.Range("rngSource")) 
        Set rngRes = .Range("rngDest") 
        Call varUniqueList(varData, True, rngRes) 'for pasting data
        varResult = varUniqueList(varData)        'result array
    End With 
End Sub

FREQUENCY Function in Excel

Posted: April 16, 2013 by Transformer in Excel
Tags: , , ,

Frequency function calculates the number of values in a dataset that fall within a particular interval. Generally it is used to group the values of a dataset in intervals.
It requires two arguments, the first one is a dataset and the other is a list of intervals.

                         Frequency (data_array, bins_array)

Data_array: it is an array or a range of values for which you want the frequency (Dataset)
Bins_array: it is an array or a range of intervals based on which you want to group the data_array (Intervals)
This function returns an array of numbers (count of frequencies). The size of the returned array is one more than the size of bins_array, the extra element being the count of elements in the data_array that are greater than the max value of the bins_array. It must be entered as an array formula.

We have a list of students’ marks and we want to group them in intervals as given below. Here, the list of marks is the data_array and bins_array is the upper limits of the intervals.

Scores	 Interval   bins_array   Results
30	 <=20        20           0(No element <= 20)   
97	 21-40	     40           4(4 elements between 21 to 40)  
62	 41-60	     60           5(5 elements between 41 to 60)  
45	 61-80	     80           4(4 elements between 61 to 80)  
94	                          7(extra element that tells how many 
32			            elements lie outside the intervals)

The order of values given in bins_array is immaterial, as illustrated in the example below.
In the same dataset above, if we change the order of elements in bins_array then the function returns same values against each element of bins_array as it would have done if the bins_array was sorted.

Unsorted (bins_array)               Results
  40                                   4
  20                                   0
  80                                   4
  60                                   5