Hàm tự tạo chạy được trên VBE nhưng không chạy trên sheet (1 người xem)

[anhtuan1069]

- Dưới đây mình có hàm GetSH (hàm của thầy NDU) để lấy danh sách cách tên sheet dạng mảng, tuy nhiên ta chỉ sử dụng được trên VBE còn viết hàm trên sheet thì lại báo lỗi Value, xin các anh chị giải thích giúp.

Function GetSh()
  Dim Temp()
  ThisWorkbook.Names.Add String(240, "z"), "=SUBSTITUTE(GET.WORKBOOK(1),""[""&GET.WORKBOOK(16)&""]"","""")"
  Temp = Evaluate("Transpose(" & String(240, "z") & ")")
  Temp = WorksheetFunction.Transpose(Temp)
  ThisWorkbook.Names(String(240, "z")).Delete
  GetSh = Temp
End Function

Sub test()
    MsgBox Join(GetSh, ",")
End Sub

 

[befaint]

Ở chỗ công thức trên bảng tính, nhấn F2, shift+Home, F9 xem...

 

[anhtuan1069]

Vẫn lỗi value bạn à

 

[giaiphap]

Thế đổi thành Hàm khác có chức năng tương tự được không?

 

[NguyenNgocThuHien]

ThisWorkbook.Names.Add String(240, "z"), "=SUBSTITUTE(GET.WORKBOOK(1),""[""&GET.WORKBOOK(16)&""]"","""")"

Cái này khi chạy dưới tư cách là một hàm thì sẽ bị lỗi, với cả không nên tận dụng code trên, chạy một hàm mà lại tạo ra một cái "Name" mới. Mình nghĩ là nên viết hàm mới cho nhanh.

 

[NguyenNgocThuHien]

Ví dụ như thế này.

 

[anhtuan1069]

Theo mình hiểu chắc có lẻ khi sử dụng hàm trong excel thì ta không thực hiện được một lệnh tương tác khác bên ngoài, nên khi đưa hàm đó vào sub trong excel thì nó chạy được

Ví dụ như thế này.

mình không download được file của bạn, nhưng mình đoán chắc là duyệt qua các sheet sau đó lấy tên, nhưng code trên cái hay của nó là không cần dùng vòng lặp

 

[VetMini]

Hàm/sub gọi từ sheet thì khong được phép thay đổi bất cứ gì trên sheet. Đặt và xoá names coi như thay đổi thuộc tính sheet, vì vậy hàm bị sheet cản không cho chạy.
Hàm gọi từ nơi khác thì tuỳ tiện thao tác.

 

[anhtuan1069]

Hàm/sub gọi từ sheet thì khong được phép thay đổi bất cứ gì trên sheet. Đặt và xoá names coi như thay đổi thuộc tính sheet, vì vậy hàm bị sheet cản không cho chạy.
Hàm gọi từ nơi khác thì tuỳ tiện thao tác.

Cám ơn anh

 

[NguyenNgocThuHien]

Theo mình hiểu chắc có lẻ khi sử dụng hàm trong excel thì ta không thực hiện được một lệnh tương tác khác bên ngoài, nên khi đưa hàm đó vào sub trong excel thì nó chạy được

mình không download được file của bạn, nhưng mình đoán chắc là duyệt qua các sheet sau đó lấy tên, nhưng code trên cái hay của nó là không cần dùng vòng lặp

Mọi người vẫn download được mà. Nếu dùng code thì phải bỏ cái

ThisWorkbook.Names.Add String(240, "z"), "=SUBSTITUTE(GET.WORKBOOK(1),""[""&GET.WORKBOOK(16)&""]"","""")"

Hoặc là chặn, để khi nó chạy từ excel thì sẽ không được thực hiện, việc thực hiện thêm name sẽ thực hiện bằng tay, hoặc macro.

 

[NguyenNgocThuHien]

Function GetSh(Optional rr As Range)
    Dim wb As Workbook
    Dim objS As Object
    Dim kq()
    Dim lIndex As Long
    
    If rr Is Nothing Then
        Set wb = ActiveWorkbook
    Else
        Set wb = rr.Worksheet.Parent
    End If
    lIndex = 1
    ReDim kq(1 To wb.Sheets.Count, 1 To 1)
    For Each objS In wb.Sheets
        kq(lIndex, 1) = objS.Name
        lIndex = lIndex + 1
        
    Next
    GetSh = kq
    
    
End Function

 

[NguyenNgocThuHien]

Còn về cái chuyện dùng vòng lặp hay không thì nó không quan trọng tới mức đó đâu. Giả sử có 100 sheet, 100 lượt lặp không là gì cả. Chứ bản chất của cách trên nó cũng phức tạp, phải tạo name, mà cuối cùng nó cũng phải duyệt từng sheet để lấy thông tin.