Dynamic Named Ranges

Hi All,

I see a lot of time people ask that they cannot find the last row of the data. One of the most complicated scenario would be, to assign the dynamic source range to a chart. Suppose I have a chart and the number of rows in my chart would keep on changing time to time, so how would I manage this? One option is to use VBA code and assign a new source data whenever it is refreshed. But do we really need code behind to resolve this problem? I would say no :)

Before going further, I would like to tell you about Offset function, which i will be using to explain the Dynamic named Ranges.

Syntax of Offset function is :

OFFSET(reference,rows,cols,height,width)

Offset, in itself is a powerful function to design a dynamic range.

Parameter 1, Reference : This would be the starting point of your range. Offset function requires you to provide it a starting point, on which you will finally decide what range to be consider.

Parameter 2, Rows : This number is used to shift the starting point. Suppose if the Starting Point is A1, and the number of Rows (second parameter) is 2, then the starting point will be shifted by 2 rows and will point to A3.

Parameter 3, Cols : This number is used to shift the starting point by columns. Suppose if the Starting Point is A1, and the number of columns (third parameter) is 5, then the starting point will be shifted by 5 Columns and will point to F1.

Parameter 4, Height : This would represent the number of cells in a column, aka, the height of range. This can be assumed as the height dimension of a matrix.

Parameter 5, Width : This would represent the width of the range.

Examples :

Offset($A$1,1,1,5,5) : The range is $B$2:$F$6.

Explanation : Starting point is $A$1. Second Parameter, shift row by 1 so It will point to $A$2. As per third Parameter, shift col by 1 so it will point for $B$2. Forth parameter says that the Height of the required range should be 5 cells. So it will be : $B$2:$B$6. The fifth and last parameter says that the width of the range should be 5 columns. So the final range would turn out to be $B$2:$F$6.

Dynamic ranges can be very handy and useful in creating charts when the source range has to be dynamic and is of changing nature. For example, suppose if we have the following chart :



You can see that we have a very simple chart, with the source data as $A$3:$B$11. However, the data can be of a changing nature. For example, see the following screenshot...the data is increased by 5 rows and the new source should be $A$3:$B$16.



Rather than assigning the source data to chart again and again, we can create a Dynamic Named Range which can be flexible if the rows of our data are increased. Dynamic Named Ranges are nothing but a Name Range, with Offset Formula in its target range.

The chart earlier had the series as $A$3:$B$11, if you see and edit the source data of chart, you will see two ranges. One is the Range for Series :- $B$4:$B$11 and X Series which is dates :- $A$4:$A$11.

To create this, I would first create a X Range, which will point to the Date Column of our chart. It will certainly involve the Offset function. To get the dynamic range for Dates, I will start my Offset from $A$4, which is the start point. Setting up next two parameters as 0, 0 so that the starting point should not be changed. Since we have to calculate the rows on run time, so I will use CountA formula so that I can get the exact height of the data. CountA function returns the number of non blank cells in the specified range. Suppose I try CountA($A$4:$A$410) function on the data (as shown in the second image), it would return 13. Because in the entire range (A4:A410) there are only 13 non blank cells. Rest are Blank. Width would be one to ensure that we are talking about a single column(or we can say series or x series values). See the following screenshot :



Similarly, I would create a SharePriceRange



After creating the dynamic named ranges, I will need to assign the name ranges to our chart. In our current chart, we have one series, and one x Series. That is why I have only used two dynamic named ranges. If we will have more series, then we will need to add as many named ranges equal to the number of series present in the chart.

For assigning a named range, I will need to edit the source data. Following is the screenshot of two buttons which we may need to click in order to edit the appropriate series.



The sheet which I am operating is Sheet2, so I will need to ensure that I have included the worksheet name along with the name range. See the two screenshots :








I have created a sample spreadsheet to demonstrate it properly. To download the spreadsheet, please Click here.

Thanks,
Vikas Bhandari

Move the Excel VBA Code from One Workbook to Another.

Hi All,

Recently, I wanted to move VBA Code from one workbook to another. I didn't have any idea. So my teammate did me a favor and got me the following code. So I thought that it is good to share it with the outside world. Moreover, it also moves the code with the Forms as well.

The function expects two parameters,

sourceBook: the workbook where we have to copy the code from
destBook : the workbook where the code has to be copied to

Sub MoveVBACode(sourceBook As Workbook, destBook As Workbook)

    Dim sourceVbComp As VBIDE.VBComponents
    Dim destVbComp As VBIDE.VBComponents


    Dim vbComp As VBComponent


    'To check the code is protected or not
    If sourceBook.VBProject.Protection = vbext_pp_locked Then
        MsgBox "Can't copy code because Source project code is password protected", vbInformation
        Exit Sub
    End If

    Set sourceVbComp = sourceBook.VBProject.VBComponents
    Set destVbComp = destBook.VBProject.VBComponents

    'To copy the modules from source to destination
    For Each vbComp In sourceVbComp
        If vbComp.Type = vbext_ct_Document Then
            CopyModule vbComp, destVbComp, ""
        ElseIf vbComp.Type = vbext_ct_StdModule Then
            CopyModule vbComp, destVbComp, ".bas"
        ElseIf vbComp.Type = vbext_ct_MSForm Then
            CopyModule vbComp, destVbComp, ".frm"
        ElseIf vbComp.Type = vbext_ct_ClassModule Then
            CopyModule vbComp, destVbComp, ".cls"
        End If
    Next

End Sub

Function CopyModule(sourceVbComp As VBIDE.VBComponent, _
                    destVbComp As VBIDE.VBComponents, _
                    extn As String)
    
Dim fName As String
Dim vbComp As VBIDE.VBComponent
Dim destCodeMod As VBIDE.CodeModule
Dim codeText As String
Dim destCodeModule As Variant
Dim TempVBComp As Variant

Const filePath As String = "C:\"
Const xportPath As String = "C:\Temp\"
    
    fName = xportPath & sourceVbComp.Name & extn
    
    sourceVbComp.Export fName
    
    If sourceVbComp.Type = vbext_ct_Document Then
    'To copy the code form ThisWorkbook and sheets
        
        'To get the code module in the destination file
        For Each vbComp In destVbComp
            If vbComp.Type = vbext_ct_Document And vbComp.Name = sourceVbComp.Name Then
                Set destCodeModule = vbComp.CodeModule
                Exit For
            End If
        Next
        
        'To update the code from source to destination
        If Not destCodeModule = "" Then
            Set TempVBComp = destVbComp.Import(fName)
            With destCodeModule
                If TempVBComp.CodeModule.CountOfLines > 0 Then
                     codeText = TempVBComp.CodeModule.Lines(1, TempVBComp.CodeModule.CountOfLines)
                    .InsertLines 1, codeText
                End If
            End With
              destVbComp.Remove TempVBComp
        End If
        
    Else ' For modules ,forms and classes
        destVbComp.Import fName
    End If
        
    'To kill the exported file
    Kill fName

End Function

Thanks,
Vikas

Move all worksheets from a workbook to another

Hi,

Ever moved all the worksheets from a workbook to another. I did it previously, so thought to share the code with you all.

Public Sub CopyWorksheets(sourceBook As Workbook, destBook As Workbook)
   
    Dim shtCounter As Integer
    Dim wkSheet As Worksheet
    
    'remove all the sheets from Destination workbook except one.
    'the last worksheet will be deleted once we have copied the worksheets
    
    For shtCounter = destBook.Worksheets.Count To 2 Step -1
        Set wkSheet = destBook.Worksheets(shtCounter)
        Application.DisplayAlerts = False
        wkSheet.Delete
        Application.DisplayAlerts = True
    Next shtCounter
    
    'rename the sheet1 so that we can assure there is no duplicity
    
    Set wkSheet = Worksheets(1)
    
    wkSheet.Name = "123111xSheet111789" 'giving some arbit name so that we can
                                        ' assure that the sheets we are trying
                                        'copy doesn't have the same name as the worksheet's name.
    
    'copy sheets to destWKB
    
    sourceBook.Worksheets.Copy after:=destBook.Worksheets(1)
    
    
    
    Application.DisplayAlerts = False
    wkSheet.Delete
    Application.DisplayAlerts = True
End Sub

HTH,
Vikas Bhandari

Excel - Break External Links

Hi All,

Please find below the code for Breaking the external links from a Workbook.

Public Sub BreakLinks()
    Dim aLinks As Variant
    Dim i As Integer
    Dim sht As Worksheet
    
    aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(aLinks) Then
        For i = 1 To UBound(aLinks)
            ActiveWorkbook.BreakLink Name:=aLinks(i), Type:=xlExcelLinks
        Next i
    End If
End Sub

Hope this helps.

Thanks,
Vikas