Dynamic Range

[Tran Chau]

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.

 

[Tran Chau]

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

 

[Tran Chau]

À 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:

 

[Tran Chau]

=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.

 

[Tran Chau]

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.

 

[Tran Chau]

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

 

[Tran Chau]

Đô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

 

[Tran Chau]

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

 

[Tran Chau]

A quen noi tat ca cac code la suu tam tren mang, chu minh chua nghi ra cai gi dau

 

[levanduyet]

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

 

[Tran Chau]

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.

 

[Tran Chau]

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é

 

[vongphuc]

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:

 

[tranvanhung]

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.

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.

 

[Tran Chau]

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"

 

[Tran Chau]

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

 

[Tran Chau]

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ỹ

 

[tranvanhung]

Đâ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.

 

[Tran Chau]

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ộ

 

[Tran Chau]

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