Hỏi code VBA tương đương hàm Code và hàm Sum trong excel

[viehoai]

Mình đang tự học excel xin các anh chị giúp đỡ code:
1. Trong excel muốn tính mã các chữ cái dùng hàm =Code(tham chiếu). Vậy trong VBA dùng hàm gì?
2. Muốn tính tổng trong VBA trong trường hợp sau dùng code gì: vd cho kết quả D1 tính sum Ai:Aj (trong đó i,j chưa biết)
Xin cảm ơn.

 

[Hai Lúa Miền Tây]

Mình đang tự học excel xin các anh chị giúp đỡ code:

2. Muốn tính tổng trong VBA trong trường hợp sau dùng code gì: vd cho kết quả D1 tính sum Ai:Aj (trong đó i,j chưa biết)
Xin cảm ơn.

Dùng WorksheetFunction thôi bạn

Sub TONG()
Dim rng As Range
    On Error Resume Next
    Set rng = Application.InputBox("Quet chon vung de cong!", _
                                              "Chon vung", Selection.Address, , , , , 8)
    MsgBox [COLOR=Blue][B]Application.WorksheetFunction.Sum(rng)[/B][/COLOR]
End Sub

 

[TrungChinhs]

Mình đang tự học excel xin các anh chị giúp đỡ code:
1. Trong excel muốn tính mã các chữ cái dùng hàm =Code(tham chiếu). Vậy trong VBA dùng hàm gì?
2. Muốn tính tổng trong VBA trong trường hợp sau dùng code gì: vd cho kết quả D1 tính sum Ai:Aj (trong đó i,j chưa biết)
Xin cảm ơn.

Thì cũng dùng Code bạn ạ
Ví dụ dữ liệu tại cột B, kết quả tại cột C thì code là

Sub Macro1()
    [b:b].SpecialCells(2).Offset(, 1) = "=CODE(RC2)"
    [c:c] = [c:c].Value
End Sub

 

[ChanhTQ@]

Câu (1) cũng có thể là vầy:

Sub Macro1()
 Dim Clls As Range     
 For Each Clls In Range("B3:B" & [B3].End(xlDown).Row)
   Clls.Offset(, 2).Value = Asc(Clls.Value)
 Next Clls
End Sub

Còn câu (2) ở trên, ta chắc ăn, có thể thêm dòng lệnh:

 If Not Rng Is Nothing then . . .

 

[nghiaphuc]

Mình đang tự học excel xin các anh chị giúp đỡ code:
1. Trong excel muốn tính mã các chữ cái dùng hàm =Code(tham chiếu). Vậy trong VBA dùng hàm gì?
Xin cảm ơn.

Trong VBA dùng hàm ASC hoặc ASCW bạn ạ.

 

[sealand]

Nói chung là theo ý kiến các bạn đã góp ý. Mình muốn nói thêm trường hợp liên quan:
1-Trong VBA có đối tượng Form và tuỳ trường hợp ta cũng có thể lấy KeyCode hoặc KeyAsscii.
2-Để dễ hiểu mình nêu công thức tổng quát và ta thay biến vào là xong

MsgBox Application.WorksheetFunction.Sum(Sheet1.Range(Cells(dong1,cot1), Cells(dong2, cot2)))

Trường hợp cụ thể bạn hỏi ta viết:

sheet1.[D1]= Application.WorksheetFunction.Sum(Sheet1.Range(Cells(i, 1), Cells(j, 1)))

Hoặc 1 dạng viết khác:

sheet1.[D1]=WorksheetFunction.Sum(Sheet1.Cells(i, 1).Resize(j - i))

(Lưu ý: cột theo thứ tự cột: Cọt A=1, Cột B=2. Trừ trường hợp Code của sheet còn code của Module hay Workbook bạn phải chỉ dẫn tường minh địa chỉ đó trên sheet nào. Noi chung nen day du )

 

[viehoai]

Các anh chị ơi,
Cho em hỏi tiếp . Em đang lập trình một vấn đề trong công việc gặp trở ngại khi copy dữ liệu từ file khác
1. khi copy dữ liệu từ file khác khác thì phải open workbook đó chương trình mới nhận. Vậy có cách nào để lấy thông tin dữ liệu từ workbook khác mà không cần phải mở workbook đó ra không
2. Trong trường hợp kiểm tra sự tồn tại của file hoặc đường dẫn sai thì code viết như thế nào.
Xin cảm ơn các anh chị

 

[viehoai]

Nói chung là theo ý kiến các bạn đã góp ý. Mình muốn nói thêm trường hợp liên quan:
1-Trong VBA có đối tượng Form và tuỳ trường hợp ta cũng có thể lấy KeyCode hoặc KeyAsscii.
2-Để dễ hiểu mình nêu công thức tổng quát và ta thay biến vào là xong

MsgBox Application.WorksheetFunction.Sum(Sheet1.Range(Cells(dong1,cot1), Cells(dong2, cot2)))

sheet1.[D1]= Application.WorksheetFunction.Sum(Sheet1.Range(Cells(i, 1), Cells(j, 1)))

Hoặc 1 dạng viết khác:

sheet1.[D1]=WorksheetFunction.Sum(Sheet1.Cells(i, 1).Resize(j - i))

(Lưu ý: cột theo thứ tự cột: Cọt A=1, Cột B=2. Trừ trường hợp Code của sheet còn code của Module hay Workbook bạn phải chỉ dẫn tường minh địa chỉ đó trên sheet nào. Noi chung nen day du )

Em muốn xuất ra công thức tính Sum luôn thì như thế nào tương tự như là trường hợp sum range mình đã biết là ="=sum(A1:A6)". Xin cảm ơn anh

 

[Hai Lúa Miền Tây]

Các anh chị ơi,
Cho em hỏi tiếp . Em đang lập trình một vấn đề trong công việc gặp trở ngại khi copy dữ liệu từ file khác
1. khi copy dữ liệu từ file khác khác thì phải open workbook đó chương trình mới nhận. Vậy có cách nào để lấy thông tin dữ liệu từ workbook khác mà không cần phải mở workbook đó ra không
2. Trong trường hợp kiểm tra sự tồn tại của file hoặc đường dẫn sai thì code viết như thế nào.
Xin cảm ơn các anh chị

Câu 1: Có 2 cách lấy, dùng ADO và dùng cách thường để lấy. (Bạn cho file cụ thể lên đi nhé)
Câu 2: Dùng code sau nhé

Chép UDF sau vào module:

Public Function KiemTra(strFullPath As String) As Boolean
    On Error GoTo loi
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then KiemTra = True
    
loi:
    On Error GoTo 0

End Function

Nếu kiểm tra folder thì chạy code sau:

Sub KiemTraFolder()
'Kiem tra su ton tai cua folder

    If KiemTra("C:\Users\Le Phat Dom\Desktop") Then
        MsgBox "Folder nay co ton tai!"
    Else
        MsgBox "Folder khong ton tai!"
    End If

End Sub

Nếu kiểm tra file thì chạy code sau:

Sub KiemTraFile()
'Kiem tra su ton tai cua 1 file

    If KiemTra("C:\Users\Le Phat Dom\Desktop\Test.xls") Then
        MsgBox "File nay co ton tai !"
    Else
        MsgBox "File nay khong ton tai !"
    End If

End Sub

 

[sealand]

Ý bạn là muốn đặt công thức vào ô (Gia sử là ô C2) có đúng không. Nếu vậy bạn viết:

Sheet1.range("C2").Formula="=Sum(A" & i & ":A" & j & ")"

 

[viehoai]

Ý bạn là muốn đặt công thức vào ô (Gia sử là ô C2) có đúng không. Nếu vậy bạn viết:

Sheet1.range("C2").Formula="=Sum(A" & i & ":A" & j & ")"

Cảm ơn anh nhiều
Anh giúp em code gán tổ hợp phím nào đó để chạy macro luôn nhé. Cảm ơn anh các anh chị nhiều.

 

[viehoai]

Câu 1: Có 2 cách lấy, dùng ADO và dùng cách thường để lấy. (Bạn cho file cụ thể lên đi nhé)
[/CODE]

Xin anh viết code giúp cho em theo cụ thể sau cũng được:
Giả sử em muốn copy vùng A2:C10 sheet1 từ file D:\VBA\filegoc.xls
sang sheet1 file hiện hành (vd e:\thu.xls)
(không mở file D:\VBA\filegoc.xls, nhưng vẫn copy dữ liệu được sang file thu.xls)
Xin cảm ơn

 

[sealand]

Cảm ơn anh nhiều
Anh giúp em code gán tổ hợp phím nào đó để chạy macro luôn nhé. Cảm ơn anh các anh chị nhiều.

Tools -->Macro --> Macro… --> Đưa vệt sáng vào Macro gần gán phím --> Option
Bạn chọn đặt phím tùy ý

 

[ndu96081631]

Xin anh viết code giúp cho em theo cụ thể sau cũng được:
Giả sử em muốn copy vùng A2:C10 sheet1 từ file D:\VBA\filegoc.xls
sang sheet1 file hiện hành (vd e:\thu.xls)
(không mở file D:\VBA\filegoc.xls, nhưng vẫn copy dữ liệu được sang file thu.xls)
Xin cảm ơn

Hôm qua đọc câu hỏi của bạn và tôi cũng đã trả lời... Đáng tiếc bài của bạn vi phạm nội quy
Giờ tôi xin trả lời lại:
Cách copy/paste thông thường:
- Mở file gốc
- Copy và paste dữ liệu
- Đóng file gốc lại
Với cách này, bạn hoàn toàn có thể xây dựng code dựa trên chức năng record macro
Cách dùng công thức:
- Gán công thức vào cell A2 của file hiện hành theo dạng:
='D:\VBA\[filegoc.xls]Sheet1'!A2
- Kéo fill công thức đến C10
- Copy vùng công thức, PasteSpecial\Value
Cách này cũng không cần mở file và cũng có thể làm bằng tay, vừa mở chức năng record macro để ghi lại quá trình thao tác
Cách dùng ADO
Cách này tuy không cần mở file nhưng code phức tạp hơn rất nhiều (e rằng bạn sẽ không dể dàng hiểu nổi)
------------------------
Theo nhận định của tôi: Với yêu cầu như bạn thì cách dùng công thức là dể hiểu nhất

 

[viehoai]

Hôm qua đọc câu hỏi của bạn và tôi cũng đã trả lời... Đáng tiếc bài của bạn vi phạm nội quy
Giờ tôi xin trả lời lại:
Cách copy/paste thông thường:
- Mở file gốc
- Copy và paste dữ liệu
- Đóng file gốc lại
Với cách này, bạn hoàn toàn có thể xây dựng code dựa trên chức năng record macro
Cách dùng công thức:
- Gán công thức vào cell A2 của file hiện hành theo dạng:
='D:\VBA\[filegoc.xls]Sheet1'!A2
- Kéo fill công thức đến C10
- Copy vùng công thức, PasteSpecial\Value
Cách này cũng không cần mở file và cũng có thể làm bằng tay, vừa mở chức năng record macro để ghi lại quá trình thao tác
Cách dùng ADO
Cách này tuy không cần mở file nhưng code phức tạp hơn rất nhiều (e rằng bạn sẽ không dể dàng hiểu nổi)
------------------------
Theo nhận định của tôi: Với yêu cầu như bạn thì cách dùng công thức là dể hiểu nhất

Cảm ơn thầy.
Trường hợp 1 em đã làm nhưng do khi chạy code thì màn hình bị giật (chắc là do mở rồi đóng file gốc?). Hơn nữa việc copy dữ liệu liên tục, nhiều lần
Nhưng em đang tự hoạc VBA hơn nữa đang thử áp dụng vào công việc nên Thầy giúp em CODE, em sẽ cố hiễu. Xin cảm ơn Thầy

 

[Hai Lúa Miền Tây]

Cảm ơn thầy.
Trường hợp 1 em đã làm nhưng do khi chạy code thì màn hình bị giật (chắc là do mở rồi đóng file gốc?). Hơn nữa việc copy dữ liệu liên tục, nhiều lần
Nhưng em đang tự hoạc VBA hơn nữa đang thử áp dụng vào công việc nên Thầy giúp em CODE, em sẽ cố hiễu. Xin cảm ơn Thầy

Không cho màn hình bị giật bạn thêm dòng sau vào đầu code:

 Sub Copy()
Application.ScreenUpdating = False

[B][COLOR=Green]'....Code cua ban[/COLOR][/B]

 Application.ScreenUpdating = True

End Sub

 

[ndu96081631]

Không cho màn hình bị giật bạn thêm dòng sau vào đầu code:

 Sub Copy()
Application.ScreenUpdating = False

[B][COLOR=Green]'....Code cua ban[/COLOR][/B]

 Application.ScreenUpdating = True

End Sub

Khi ta thiết lập ScreenUpdating = False thì máy tính không phải làm công việc "vẽ lại" màn hình mổi khi có sự thay đổi... Khi kết thúc quá trình tính toán, nó chỉ việc "Show" kết quả cuối cùng nên ngoài việc làm cho màn hình khỏi "giật giật" nó còn góp phần tăng tốc độ rất đáng kể đấy (có trường hợp nó tăng tốc đến vài chục lần)

 

[viehoai]

Cám ơn các anh chị đã giúp đỡ. Nhưng em vẫn chờ sự giúp đỡ code Cách dùng ADO

 

[Hai Lúa Miền Tây]

Cám ơn các anh chị đã giúp đỡ. Nhưng em vẫn chờ sự giúp đỡ code Cách dùng ADO

Thôi thì bạn dùng cái này đơn giản và dể hiểu vậy:

Chép code sau vào module:

Sub GetRange(FilePath As String, FileName As String, SheetName As String, _
             SourceRange As String, DestRange As Range)
    Dim Start

    Application.Goto DestRange
    Set DestRange = DestRange.Resize(Range(SourceRange).Rows.Count, _
                                     Range(SourceRange).Columns.Count)
    'Thiet lap ham tham chieu den file dong
    With DestRange
        .FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName _
                        & "'!" & SourceRange
        'Doi trong giay lat
        Start = Timer
        Do While Timer < Start + 2
            DoEvents
        Loop
        'Neu ban muon lay gia tri khong thoi thi them 4 dong sau vao
        .copy
        .PasteSpecial xlPasteValues
        .Cells(1).Select
        Application.CutCopyMode = False
    End With
    
End Sub

Và chạy code này:

Sub LayDuLieu()
    Application.ScreenUpdating = False
    On Error Resume Next

     GetRange "D:\VBA", "filegoc.xls", "Sheet1", "A2:C10", _
             Sheets("KetQua").Range("A1")
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

 

[ndu96081631]

Cám ơn các anh chị đã giúp đỡ. Nhưng em vẫn chờ sự giúp đỡ code Cách dùng ADO

Gữi bạn code đơn giản nhất:

Private Sub GetData(SourceFile, SourceRange As String)
    Dim rsCon As Object, rsData As Object, szConnect As String, szSQL As String
    If Val(Application.Version) < 12 Then
      szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & SourceFile & ";Extended Properties=""Excel 8.0;HDR=No"";"
    Else
      szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & SourceFile & ";Extended Properties=""Excel 12.0;HDR=No"";"
    End If
    szSQL = "SELECT * FROM " & SourceRange$ & ";"
    On Error GoTo Thoat
    Set rsCon = CreateObject("ADODB.Connection")
    Set rsData = CreateObject("ADODB.Recordset")
    rsCon.Open szConnect
    rsData.Open szSQL, rsCon, 0, 1, 1
    Range("A1").CopyFromRecordset rsData
    rsData.Close: Set rsData = Nothing
    rsCon.Close: Set rsCon = Nothing
Thoat:
End Sub

Sub GetData_Test()
  Range("A:C").ClearContents
  GetData ThisWorkbook.Path & "\filegoc.xls", "A2:C10"
End Sub

Xem 2 file đính kèm
Vì đơn giản nên vẫn còn phải cải tiến thêm nếu nhu cầu của bạn cao hơn!