Dynamic Range

[Pansy_flower]

Giới thiệu các bài viết nói về đề tài nầy:

1/ http://www.cpearson.com/excel/named.htm

2/ http://www.beyondtechnology.com/geeks007.shtml.

3/-at http://myweb.tiscali.co.uk/xlvba/exc...tils/index.htm

Còn mình đang trao đổi với bạn bài viết có tại:

http://www.decisionmodels.com/optspeedf.htm.

Theo mình thì DN là vùng sẽ tự động mở rộng/thu hẹp khi mà ta thêm/bớt data.Thường khi chưa dùng DN thì ta sẽ chọn vùng thật lớn cho chắc ăn, nhưng rõ là như vậy không hay lắm.

Đến đây thì lại bận rồi, thôi các bạn vui lòng đọc nguyên văn bài viết vậy.

Ngoài ra, các bạn có thể down file mẫu tại:

Dynamic Range Wizard: A dynamic named range can be one of Excel's most incredibly powerful tools. It can also be very tricky to set up. This add-in makes the process a simple point-and-click exercise.

Mình có sẳn file mẫu DynaRange.zip của Ozgrid, có thể up nếu ai cần, vui lòng mail cho mình t-chau@hcm.fpt.vn

(Trích bài của anh Trân Châu)
​

 

[Pansy_flower]

À lại kiếm ra file nầy nửa, gửi các bạn xem.

Dynamic Named Ranges
Rodney Powell
Microsoft MVP - Excel need a developer for your excel project ?
go here

Named ranges are among the most powerful features of Excel, especially when used as the source range for list controls, PivotTables, or charts. A problem arises, however, when the contents of a list change often. It would be a problem to have to redefine your named ranges everytime a table has records added or removed. The solution is to create a range that will automatically adjust based on the number of items in the list.
First, create a list in column A of a worksheet. From the worksheet's Insert menu choose Names then the Define.... Enter a name for your new range, such as MySheet!rngDynamic. Then, in the Refers to: box, enter the following:

=OFFSET(MySheet!$A$1,0,0,COUNTA(MySheet!$A:$A),1)

How It Works:

The first argument for the OFFSET function is the cell on which you want to anchor it. Everything else will be set relative the this cell address. Typically, you will want it to be either the header for the first field in your source data table or its first record.

The second argument indicates how many rows to move from the anchor address to begin the range. In this case, we used 0 which would include my header row. If we substituted 1 then the header row would be skipped and the range would begin on row 2 of the table.

The third argument indicates how many columns to move from the anchor address to begin the range. In this example we used 0, meaning to begin the range on the same column as the anchor address provided in the first argument.

The fourth argument tells how many rows the range should extend. They key here is to substitute the COUNTA function for your primary field, instead of hardcoding a value. This way if you add or remove items from that field, the range will grow or shrink accordingly. You also may need to subtract 1 from the COUNTA result to account for the elimination of a header row. It is important that you do not have any superfluous data beneath the table range you intend to evaluate.

The fifth, and final, argument is how many columns wide you want for the dynamic range to be. In our example, this range includes only a single column, therefore the argument provided was simply 1.

This is a very flexible technique for defining your named ranges. The best thing to do is to experiment with some variations of the sample formula provided and you will soon find that Dynamic Named Ranges will become an indispensable tool you will want to use throughout your Excel work.


Note:

Dynamic named ranges can only be referenced in an open workbook, because they are dependent upon a workbook's ability to calculate.

Luôn tiện các bạn đọc luôn bài sau:

Dynamic Arrays
Rodney Powell
Microsoft MVP - Excel need a developer for your excel project ?
go here

Note: Some readers may find it useful to review the previous article describing arrays.

Sometimes you won't know the specific size of an array and will need the ability to change the number of elements contained at run time. A dynamic array is a flexible type of array that you can resize at run time. Besides that dynamic arrays are a convenient feature in VBA, the importance of using dynamic arrays is memory management. With dynamic arrays you can be sure that your operation conserves demands on memory to no more than is actually required to meet the immediate need.

Since a dynamic array doesn't have a preset number of elements, you will declare it with an empty set of parentheses.

Dim aMyArray()

Notice that the declaration of the array aMyArray does not specify the number of elements it contains. The statement above declares a dynamic array, however it does not allocate any memory to the array. It will not be until you use the ReDim statement to change the array for the appropriate number of elements that memory is allocated for the array. The ReDim statement can appear only in a procedure. It can change the number of elements, as well as the lower and upper bounds.

Dim aMyArray()
ReDim aMyArray(7)

You can redimension an array programmatically as many times as necessary. However, when an array is redimensioned, all it's elements are lost. VBA resets the values to Empty for variant arrays, to zero for numeric arrays, to a zero-length string for text arrays, and to Nothing for arrays of objects.

If you want the array maintain the assigned values for all elements already within your array, you can include the optional Preserve keyword with the ReDim statement. If you redimension an array to a size smaller than it was previously, values outside of the new upper bound (and lower bound) of the array will be lost.

ReDim Preserve aMyArray(1 to 7)

With dynamic arrays you can manage memory efficiently. You can use a large array for a short time and then free memory to the system when you're no longer using the array. To accomplish this, you use the Erase statement to reinitialize the elements of a dynamic array to wipe out the data that it holds and recover all of the memory assigned to the array.

Here is an example of how to reclaim the memory allocated to the array:

Erase aMyArray
Let's wrap this up with a code example that may help to further demonstrate how dynamic arrays work. Using the Forms toolbar, place three CheckBox controls on a worksheet (Sheet1). Make the captions for those checkboxes: "Florida", "Texas", and "Virginia".

Next insert a module and copy in the following VBA code.

Option Explicit
Option Base 1
Sub DynamicArrayDemo()
   Dim cbx As CheckBox, aState$(), a&, b&
[COLOR=Green] '  Loop through all of the CheckBox Objects on Sheet1[/COLOR]
   For Each cbx In Worksheets("Sheet1").CheckBoxes
[COLOR=Green]    '  If the Checkbox was selected then ...[/COLOR]

      If cbx.Value = xlOn Then
[COLOR=Green]       '  Increment a counter[/COLOR]
         a& = a& + 1
[COLOR=Green]       '  Expand the aState$ dynamic array to add
      '  another element[/COLOR]
         ReDim Preserve aState$(1 To a&)
[COLOR=Green]       '  Initialize the new element of the array
      '  with the caption of the selected CheckBox[/COLOR]
         aState$(a&) = cbx.Caption
      End If
   Next cbx
[COLOR=Green] '  If there were any CheckBoxes selected, then ...[/COLOR]
   If a& > 0 Then
[COLOR=Green]    '  Loop through the elements in the aState$
  '  dynamic array[/COLOR]
      For b& = 1 To a&
[COLOR=Green]       '  Display the contents of each array
      '  element in a message box[/COLOR]
        MsgBox aState$(b&)
      Next b&
   End If
End Sub

You can run this code and experiment with checking different controls on your worksheet to improve your grasp of how dynamic arrays are applied.

(Trích bài của anh Trân Châu)


​

 

[Pansy_flower]

Lỡ kiếm tài liệu thì kiếm luôn 1 làn cho các bạn xem, ngày mai là đi lo viêc kiếm tiền uống bia , nuôi con rồi!

Create a Dynamic Range

You can use a dynamic formula to define a range. As new items are added, the range will automatically expand.

Choose Insert>Name>Define
Type a name for the range, e.g. NameList

In the Refers To box, enter an Offset formula that defines the range size, based on the number of items in the column, e.g.:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

In this example, the list is on Sheet1, starting in cell A1

The arguments used in this Offset function are:
Reference cell: Sheet1!$A$1
Rows to offset: 0
Columns to offset: 0
Number of Rows: COUNTA(Sheet1!$A:$A)
Number of Columns: 1
Note: for a dynamic number of columns, replace the 1 with:
COUNTA(Sheet1!$1:$1)
Click OK

Bài trên chép tại: http://www.contextures.com/xlNames01.html#Dynamic


Đôi khi bạn sẽ cần Left Lookups

Another limitation of both the VLOOKUP and HLOOKUP functions is that you can only lookup a value to the right of the key value. For example, in the range shown to the left, you can retrieve the value "c" by using VLOOKUP to search for a 3. However, the reverse is not true. It is not possible to use VLOOKUP to search for "c" and return the value 3. This sort of operation is called a left lookup, and is possible only by using the MATCH and OFFSET functions.

=OFFSET(G32,MATCH(I32,$G$32:$G$38,0)-1,-1,1,1)

The MATCH function tells us where in the list $G$32:$G$38 the value of I32 is, and then the OFFSET function goes to the left ( -1) to retrieve the value.

Tìm file mẫu tại: http://www.cpearson.com/excel

(Trích bài của anh Trân Châu)
​

 

[Pansy_flower]

1/-Xóa tất cả các Name trong Workbook

Sub DeleteAllName()

  Set nms = ActiveWorkbook.Names ' day la Object Collection
For r = 1 To nms.Count ' dem so Name co trong workbook
nms(1).Delete ' khi xoa Names(1), se tu dong don cai khac len la 1
Next
[COLOR=Green] 'ActiveWorkbook.Names("mySortRange").Delete 'thu cai nay khg xoa duoc, ma phai la index 1[/COLOR]
End Sub

2/-Xuất hiện tất cả các Name trong Workbook vào sheet đang hoạt động

Attribute VB_Name = "Module1"
Sub mNameLister()
[COLOR=Green] 'List all names in a workbook[/COLOR]
Dim x
For x = 1 To Names.Count
        With ActiveCell
    .Offset(x, 0) = Names(x).Name   'Name
    .Offset(x, 1) = " " & Names(x)  'Address
        End With
Next
End Sub

3/-Bảo vệ công thức trong Workbook. Chép vào VBE\This Workbook

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
 Dim rng As Range
 For Each rng In Target.Cells
      If rng.HasFormula Then
           ActiveSheet.Protect ("123456")
           Exit Sub
       Else
           ActiveSheet.Unprotect ("123456")
       End If
 Next rng
End Sub

4/- Chuyển công thức thành giá trị trong vùng chọn

Sub SelecCTToGiaTri()
'
' Replace selection,formulas with values
'

        Selection = Selection.Value
     End Sub

5/-Diễn giải công thức

Function GetFormula(Rng As Range) As String
    Application.Volatile True
    GetFormula = "<----  " & Application.Text(Rng.FormulaLocal, "")
End Function

6/-Bảo vệ chỉ cột A và D. Chép vào VBE\This Workbook

Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

'If InStr(1, Target.Address, "D") <> 0 Then'
 If InStr(1, Target.Address, "D") Or InStr(1, Target.Address, "A") <> 0 Then
     ActiveSheet.Protect ("123")
 Else
     ActiveSheet.Unprotect ("123")
 End If
End Sub

6/-Hiện thông báo tên các Worksheet,Workbooks đang mở

Sub WBColltec()
Dim wkBook As Workbook
Dim wkSheet As Worksheet
For Each wkBook In Workbooks
   For Each wkSheet In Application.Worksheets
      MsgBox wkBook.Name & " -- " & wkSheet.Name
   Next wkSheet
Next wkBook
End Sub

Cai nay thi trong file Help Excel co chi, nhung bac Bill dau nghe, _
dung khong chay duoc, minh da suu tam va dung thay chay duoc. _
nho vao Tool Ref word xx library chon

Sub OLEAutomationEarlyBinding() [COLOR="Lime"]'Dung ky thuat EarlyBinding de goi 1 ung dung [/COLOR]_ 
khac, tieng Viet co nguoi goi la rang buoc som va tre
[COLOR=Green] ' thay xxx bang 1 trong cac chu sau, o day toi dung thu Word:
' Access, Excel, Outlook, PowerPoint or Word[/COLOR]

Dim oApp As Word.Application ' early binding
Dim oDoc As Word.Document
[COLOR=Green] ' Excel.Workbook, Outlook.MailItem, PowerPoint.Presentation, Word.Document[/COLOR]
    On Error Resume Next ' ignore errors
    Set oApp = GetObject(, "Word.Application")
[COLOR=Green]     ' reference an existing application instance[/COLOR]
    If oApp Is Nothing Then [COLOR=Green]' neu chuong trinh chua mo[/COLOR]
        Set oApp = New Word.Application [COLOR=Green]' create a new application instance[/COLOR]
    End If
    On Error GoTo 0 ' resume normal error handling
    If oApp Is Nothing Then ' not able to create the application
        MsgBox "The application is not available!", vbExclamation
    End If
    With oApp
        .Visible = True [COLOR=Green]' day la 1 chieu de ban nao muon bao mat, _
 hay thay bang False va thu[/COLOR]
[COLOR=Green]         ' at this point the application is visible
        ' do something depending on the application...[/COLOR]
        Set oDoc = .Documents.Open("E:\Doc4.doc")
[COLOR=Green]         ' open a document
'        ...[/COLOR]
        oDoc.Close True [COLOR=Green]' close and save the document. day la cung la1 _
    chieu de ban nao muon bao ve du lieu, neu thay False ![/COLOR]
        .Quit [COLOR=Green]' close the application[/COLOR]
    End With
    Set oDoc = Nothing ' free memory
    Set oApp = Nothing ' free memory
End Sub[/COLOR]

(Trích bài anh Trân Châu)​