Dynamic Range

Thảo luận trong 'Ứng dụng Excel' bắt đầu bởi Tran Chau, 26 Tháng mười hai 2004.

4,658 lượt xem

  1. Tran Chau

    Tran Chau Thành viên hoạt động

    Bài viết:
    149
    Đã được thích:
    0
    Nơi ở:
    TP Ho Chi Minh
    Mình có đọc ở chuyên mục nào đó các bạn đang trao đổi về Dynamic Range(DN), tìm hoài không ra, thôi thì mạo muội có ý kiến ở đây vậy.
    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/excelvba/utils/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.
     
    #1
  2. Tran Chau

    Tran Chau Thành viên hoạt động

    Bài viết:
    149
    Đã được thích:
    0
    Nơi ở:
    TP Ho Chi Minh
    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
     
    #2
  3. Tran Chau

    Tran Chau Thành viên hoạt động

    Bài viết:
    149
    Đã được thích:
    0
    Nơi ở:
    TP Ho Chi Minh
    À 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:
     
    #3
  4. Tran Chau

    Tran Chau Thành viên hoạt động

    Bài viết:
    149
    Đã được thích:
    0
    Nơi ở:
    TP Ho Chi Minh
    =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.
     
    #4
  5. Tran Chau

    Tran Chau Thành viên hoạt động

    Bài viết:
    149
    Đã được thích:
    0
    Nơi ở:
    TP Ho Chi Minh
    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&
    ' Loop through all of the CheckBox Objects on Sheet1
    For Each cbx In Worksheets("Sheet1").CheckBoxes
    ' If the Checkbox was selected then ...

    If cbx.Value = xlOn Then
    ' Increment a counter
    a& = a& + 1
    ' Expand the aState$ dynamic array to add
    ' another element
    ReDim Preserve aState$(1 To a&)
    ' Initialize the new element of the array
    ' with the caption of the selected CheckBox
    aState$(a&) = cbx.Caption
    End If
    Next cbx
    ' If there were any CheckBoxes selected, then ...
    If a& > 0 Then
    ' Loop through the elements in the aState$
    ' dynamic array
    For b& = 1 To a&
    ' Display the contents of each array
    ' element in a message box
    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.
     
    #5
  6. Tran Chau

    Tran Chau Thành viên hoạt động

    Bài viết:
    149
    Đã được thích:
    0
    Nơi ở:
    TP Ho Chi Minh
    Lỡ kiếm tài liệu thì kiếm luon 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
     
    #6
  7. Tran Chau

    Tran Chau Thành viên hoạt động

    Bài viết:
    149
    Đã được thích:
    0
    Nơi ở:
    TP Ho Chi Minh
    Đô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
     
    #7
  8. Tran Chau

    Tran Chau Thành viên hoạt động

    Bài viết:
    149
    Đã được thích:
    0
    Nơi ở:
    TP Ho Chi Minh
    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
    'ActiveWorkbook.Names("mySortRange").Delete 'thu cai nay khg xoa duoc, ma phai la index 1
    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()
    'List all names in a workbook
    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

    5/-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
     
    #8
  9. Tran Chau

    Tran Chau Thành viên hoạt động

    Bài viết:
    149
    Đã được thích:
    0
    Nơi ở:
    TP Ho Chi Minh
    A quen noi tat ca cac code la suu tam tren mang, chu minh chua nghi ra cai gi dau
     
    #9
  10. levanduyet

    levanduyet Welcome

    Bài viết:
    535
    Đã được thích:
    11
    Giới tính:
    Nam
    Nơi ở:
    HCM
    To: Bác TranChau
    WKT vui mừng chào đón Bác tham gia vào trận. Thế là Web kế toán lại có thêm cao thủ. Hẹn gặp anh khi nào em về! Hihihi
    Lê Văn Duyệt
     
    #10
  11. Tran Chau

    Tran Chau Thành viên hoạt động

    Bài viết:
    149
    Đã được thích:
    0
    Nơi ở:
    TP Ho Chi Minh
    Trước đây, mình có down về 1 ch trình kế toán, hình như là QT-OK do Trần Sơn Đông đưa lên mạng, chương trình này hơi giống sách của TS Bùi Văn Dương- Võ Văn Nhị viết. Mình có mạn phép đưa thêm phần VAT, in phiếu thu chi, nhập xuất, cũng lấy từ Web KT, và định dạng lại phần báo cáo thuế, tương đối đủ để lo việc BCT và SSKT của một Cty nhỏ.Bạn nào có chỗ upload để bàn dân tham khảo vui lòng mail cho mình: t-chau@hcm.fpt.vn. Và cũng xin cám ơn trang Web đã có những tài liệu rất thực tiễn.
     
    #11
  12. Tran Chau

    Tran Chau Thành viên hoạt động

    Bài viết:
    149
    Đã được thích:
    0
    Nơi ở:
    TP Ho Chi Minh
    Duyệt thân,
    Mấy hôm nay tự nhiên hơi hứng, chứ vài bửa nửa lại phải lao vào làm ăn rồi, sợ không ránh đâu!
    Về Excel, mình có 1 file nầy nhưng không thể nào mở ra được, cứ mở ra là nó không hiện ra được, Duyệt mà open được nó mới là cao thủ , sẽ gởi Duyệt xem.
    Duyệt có bài mẫu nào để các file Excel có mối quan hệ như trong các database không( Access, Foxpro..), nếu làm được thì tốc độ truy xuất Excel database sẽ tăng. Mình biết SQL chắc chắn làm được, nhưng còn ADO, DAO thì sao? Xuất hiện vài chiêu thử xem !!. Hẹn gặp offline nhé
     
    #12
  13. vongphuc

    vongphuc Excel Fans Club

    Bài viết:
    300
    Đã được thích:
    4
    Nơi ở:
    Ha Noi
    Chào bác Tran Chau, xin chân thành cảm ơn bác đã góp sức cho diễn đàn và mong bác có thêm thật nhiều bài viết nữa

    Nếu bác có file Excel nào không mở ra được thì gửi cho tôi về địa chỉ mail vongphuc@sg.netnam.vn, tôi sẽ thử fix lỗi xem có được không nhé

    Về cơ sở dữ liệu trong Excel nó khác với Access, SQL như thế nào thì bác Tran Chau và bác levanduyet có thể phân tích và chỉ rõ được không? Mong tin từ các bác :flower:
     
    #13
  14. tranvanhung

    tranvanhung Gãi ngứa

    Bài viết:
    2,976
    Đã được thích:
    9
    Nơi ở:
    Biên Hoà - Đồng Nai
    To Tran Chau !

    Bác gởi cho tôi theo 1 trong 2 địa chỉ mail ở phía dưới tôi sẽ up lên cho mọi người tham khảo.
     
    #14
  15. Tran Chau

    Tran Chau Thành viên hoạt động

    Bài viết:
    149
    Đã được thích:
    0
    Nơi ở:
    TP Ho Chi Minh
    1/-Mình đã gửi file cho Bac tranvanhung roi đó.
    2/-Về file Excel không mở được, đó không phải do lỗi, mà tác giả muốn bảo mật file của mình bằng một phương pháp nào đó,khi ta mở nó bằng Excel.exe thì nó sẽ tự động đóng lại,đặc biệt macro nầy đã chặn được các thông báo cảnh báo virus macro của Excel và phím Shift( điều trên là mình đoán thôi)
    “do 1 tac gia chuyen lam phan mem du toan bang Excel, tac gia da thuong mai tu nam 1999, phien ban dau tien duoc viet bang macro4, sau do nang cap dan den nay. File nay la cai gi ma khong mo duoc “
    Sẽ gửi đến bác vongphuc tham khao
    3/- Các bạn dùng thử công thức tự động điền số thứ tự mà có cách khoảng trống mình sưu tầm:
    IF(C9<>"";COUNTA($C9:C$9);"")
    neu cell c9 khac trong thi dem so luong cells co data tu c9 den c9, neu trong thi khong co gi het
    khong hieu o cho sau khi copy "9 absolute" bien thanh "9 relative"
     
    #15
  16. Tran Chau

    Tran Chau Thành viên hoạt động

    Bài viết:
    149
    Đã được thích:
    0
    Nơi ở:
    TP Ho Chi Minh
    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() 'Dung ky thuat EarlyBinding de goi 1 ung dung khac, tieng Viet co nguoi goi la rang buoc som va tre
    ' thay xxx bang 1 trong cac chu sau, o day toi dung thu Word:
    ' Access, Excel, Outlook, PowerPoint or Word

    Dim oApp As Word.Application ' early binding
    Dim oDoc As Word.Document
    ' Excel.Workbook, Outlook.MailItem, PowerPoint.Presentation, Word.Document
    On Error Resume Next ' ignore errors
    Set oApp = GetObject(, "Word.Application")
    ' reference an existing application instance
    If oApp Is Nothing Then ' neu chuong trinh chua mo
    Set oApp = New Word.Application ' create a new application instance
    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 ' day la 1 chieu de ban nao muon bao mat, hay thay bang False va thu
    ' at this point the application is visible
    ' do something depending on the application...
    Set oDoc = .Documents.Open("E:\Doc4.doc")
    ' open a document
    ' ...
    oDoc.Close True ' close and save the document. day la cung la1 chieu de ban nao muon bao ve du lieu, neu thay False !
    .Quit ' close the application
    End With
    Set oDoc = Nothing ' free memory
    Set oApp = Nothing ' free memory
    End Sub
     
    #16
  17. Tran Chau

    Tran Chau Thành viên hoạt động

    Bài viết:
    149
    Đã được thích:
    0
    Nơi ở:
    TP Ho Chi Minh
    Mình đã gửi 2 file mẫu lên và file về late binding, nhưng hởi ôi! tài liệu thì nói vậy, thử với Word thì OK, nhưng với Access thì treo, thật học mà không mất tiền thì như vậy đó, kể cả với tài liệu của Âu , Mỹ
     
    #17
  18. tranvanhung

    tranvanhung Gãi ngứa

    Bài viết:
    2,976
    Đã được thích:
    9
    Nơi ở:
    Biên Hoà - Đồng Nai
    Đây là các file mà bác Tran Chau gởi cho tôi và tôi up lên cho các bác. Nếu có thắc mắc thì các bác cứ mess cho bác Tran Chau nha.
     

    Các file đính kèm:

    #18
  19. Tran Chau

    Tran Chau Thành viên hoạt động

    Bài viết:
    149
    Đã được thích:
    0
    Nơi ở:
    TP Ho Chi Minh
    Sau vài ngày ktoán xử dụng file BVDương:
    1/-Sheet Sỏ Ktoán Máy khi nhập liệu không nên insert hay move row, cell vì sẽ ảnh hưởng đến toàn bộ công thức ở các sheets khác
    Nếu lở dời thì kiểm tra lại công thức của row đầu tiên rồi copy/ fill down lại
    2/-Sau khi thử vài lần mà đã ổn định thì nên tạo 1 file vào data, theo mình nghĩ đó là file sktmáy và bdmục tkhoan( có số tôn đầu kỳ) Sau đó copy đè lên file góc, coi như đó là file chương trình. Mỗi tháng chỉ lưu file data nầy
    3/-Có thể dùng record macro để chạy, chép các công thức vào các file sổ cái, nkchung.. để có vẻ lập trình 1 chút.
    Mình cũng chưa thử, vì đã nộp xong báo cáo thuế rồi !!! lười quá, bạn nào thử rồi góp ý với
    Nhân tiện xin nhắc:
    -Sheet DocSo chưa chuyển mã sang VNI
    -Chưa link cái file Nhập kho, Xuất kho
    Bạn nào hứng thì làm giúp và up lên WKT hộ
     
    #19
  20. Tran Chau

    Tran Chau Thành viên hoạt động

    Bài viết:
    149
    Đã được thích:
    0
    Nơi ở:
    TP Ho Chi Minh
    Nhờ bác up lên cho các bạn xem thử, chỉ có code chưa có data và Named cells
    Chương trình này sẽ lấy dữ liệu từ các cells được Named và đặt nó vào row thứ LastPlc và cột thích hợp
     
    #20

Chia sẻ trang này