☰

用户定义函数从前一张表中选取值而不进行更新

tag-icon • tag-icon microsoft-excel
用户定义函数从前一张表中选取值而不进行更新

我的 excel 表中有一个prevSheet()函数。每当我将模板表复制并粘贴到新表中时，该函数都会起作用。后来我Addworksheets()通过 VBA 创建了一个函数，将模板表复制到新表中，重命名并锁定它。从那时起，该prevSheet()函数就不再更新。每次我都必须选择单元格并按 Enter 才能更新它，尽管计算选项设置为Automatic。

有人可以帮忙吗？

这是我的 addworksheet() 函数：

Sub AddWorksheet() Application.Volatile

Dim i As Integer 
Dim ws1 As Worksheet

If ThisWorkbook.ProtectStructure = False Then


Dim dateString As String, TheDate As Date Dim valid As Boolean: valid
= True

Do   dateString = InputBox("Enter the first date of the month for which you want to prepare the sheet", "Enter The Date", Day(Now()) & "/" & Month(Now()) & "/" & Year(Now()))
     If dateString = "" Then

    MsgBox "You cancelled the action of creating new sheets.  If you want to create new sheets for the month, it is manndatory to enter the date to detect the month and number of days of the month"
    Exit Sub
       Else
    If IsDate(dateString) Then
        TheDate = DateValue(dateString)
        valid = True
    Else
        MsgBox "Invalid date"
        valid = False
    End If
         End If    
Loop Until valid = True

Dim mon As Integer 
Dim yea As Integer 
Dim days As Integer


mon = Month(TheDate) 
yea = Year(TheDate)

days = Day(Application.WorksheetFunction.EoMonth(TheDate, 0))

Dim date1 As Date date1 = DateValue("1-" + mon + 1 & "-" & yea)



For i = 1 To 31

'# if to check if sheets already exists If Not sheetExists(32 - i) Then Set ws1 = Worksheets.Add(After:=Worksheets("DEBTORS"))


If 32 - i <= days Then Worksheets("TEMPLATE").Cells.Copy ws1.Cells(1, 1) End If

ws1.NAME = 32 - i

If i = 31 Then Worksheets("1").Range("$f$44").Value = date1 End If

ws1.Protect 32 - i, True, True End If ' # end of if that checks if sheet already exisits


Next


Else MsgBox "Please Remove Protection on your workbook structure to add sheets" & Chr(10) & "Review Tab => Protect Workbook" End If


End Sub




Function sheetExists(sheetToFind As String) As Boolean
    sheetExists = False
    For Each Sheet In Worksheets
        If sheetToFind = Sheet.NAME Then
            sheetExists = True
            Exit Function
        End If
    Next Sheet End Function

这是我的 prevsheet() 函数：

Function PrevSheet(RCELL As Range)


    'Application.Volatile

    Dim i As Integer
    Dim s As String
    i = RCELL.Cells(1).Parent.Index
    s = RCELL.Cells(1).Parent.NAME

    If s = "1" Then
    PrevSheet = 0

    Else

    PrevSheet = ThisWorkbook.Sheets(i - 1).Range(RCELL.Address)

    End If


End Function

答案1

我解决了这个问题。通过添加Appliction.volatile到我的用户定义函数，该函数现在强制它在每次更新某些内容或某些单元格时进行更新。

我的 prevSheet() 函数代码中的注释行解决了该问题。

相关内容