UDF "NAV()" предназначен для поиска правильного отчета на сетевом диске на основе первого аргумента (всегда дата), а затем перебирает все рабочие листы, чтобы найти фрагмент данных с той же строкой, что и второй аргумент, и тем же столбцом, что и третий аргумент (второй и третий могут быть текстом или числами).
Надежно работает в ближайшем окне. Всегда возвращает #ЗНАЧ! при использовании на листе, например. =СЧА(D7,D8,D9) или =СЧА(19.02.2016, "Стоимость чистых активов", "221-I").
В целом похоже, что такое поведение можно получить, если попытаться изменить другие ячейки в UDF, но мои функции этого не делают. Кроме того, я считаю, что все ссылки на диапазоны указывают, какая книга и рабочий лист, поэтому я не думаю, что это проблема. Я не уверен, где искать дальше.
Функция также пытается отправить мне отчет по электронной почте через Outlook, когда не может найти то, что ищет пользователь. Не знаю, актуально ли это.
Опять же, что вызывает недоумение, так это то, что этот код, кажется, отлично работает в непосредственном окне, но дает только #ЗНАЧ! при использовании на рабочем листе.
Где еще я могу посмотреть в своем коде ниже, чтобы определить, что заставит NAV() правильно работать в непосредственном окне, но всегда дает #VALUE! при использовании на листе?
Option Explicit
Function NAV(ByVal NAVDate As Date, ByVal matchRow As Variant, ByVal matchColumn As Variant) As Variant
'Application.ScreenUpdating = False
Application.Volatile True
NAV = FindItemOnWorksheet(NAVDate, matchRow, matchColumn)
'Application.ScreenUpdating = True
End Function
Function FindItemOnWorksheet(ByVal NAVDate As Date, ByVal ItemSpecies As Variant, ByVal ItemGenus As Variant) As Variant
' Finds Item by opening NAV workbook with correct date, activating correct worksheet, and searching for correct row and column
Dim startingRange As Range
Dim ws As Worksheet
Dim wb As Workbook
Dim theDate As Date
Dim theItemSpecies As String
Dim theItemGenus As String
theDate = NAVDate
theItemSpecies = ItemSpecies
theItemGenus = ItemGenus
Set wb = GetWB(NAVDate)
'Loop through ws
Dim WS_Count As Integer
Dim i As Integer
WS_Count = wb.Worksheets.Count
For i = 1 To WS_Count
Set ws = wb.Worksheets(i)
Set startingRange = ws.Range("A1:Z100")
Dim theRow As Range
Dim theColumn As Range
Set theRow = startingRange.Cells.Find(theItemSpecies, SearchDirection:=xlPrevious, lookat:=xlWhole)
If Not (theRow Is Nothing) Then
Set theColumn = startingRange.Cells.Find(theItemGenus, SearchDirection:=xlPrevious, lookat:=xlWhole)
If Not (theColumn Is Nothing) Then
FindItemOnWorksheet = ws.Cells(theRow.Row, theColumn.Column).Value
wb.Close
Exit Function
End If
End If
Next i
'Loop if no hit on either row or column Find()
'following executes only if no match found
MsgBox "No Match Found. Make sure you are entering arguments--" & vbNewLine & _
" The Date of NAV, " & vbNewLine & _
" the entry found in the right row of NAV workbooks (e.g. 'Net Asset Value'), " & vbNewLine & _
" the right column (e.g. 'Fund')." & vbNewLine & _
" This function will only find exact matches." & vbNewLine & vbNewLine & _
"Now emailing developer to ask for a fix."
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "User attempted" & _
"=FindItemOnWorksheet( " & theDate & ", " & theItemSpecies & ", " & theItemGenus & " )" & vbNewLine & _
"theDate type " & TypeName(theDate) & vbNewLine & _
"theItemSpecies type " & TypeName(theItemSpecies) & vbNewLine & _
"theItemGenus type " & TypeName(theItemGenus)
On Error Resume Next
With OutMail
.To = <Address Removed>
.CC = ""
.BCC = ""
.Subject = "FindItemOnWorksheet Error"
.Body = strbody
'.Attachments.Add ("C:\file.xlsx")
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
FindItemOnWorksheet = "Error"
'wb.Close
Exit Function
End Function
Function GetWB(ByVal NAVDate As Date) As Workbook
'Open requested workbook, return to parent procedure
Dim wbPath As String
Dim wbYear As String
Dim wbMonth As String
Dim wbWeek As String
Dim wbFile As String
Dim wbString As String
Dim wb As Workbook
Dim BackADay As Boolean
Dim OriginalNAVDateRequested As Date
OriginalNAVDateRequested = NAVDate
BackADay = True
'Loop through possible file tree structures and dates to find the closest NAV in the past to the date requested.
Do While BackADay = True
'Don't go back to a previous week if cannot find current NAV
If OriginalNAVDateRequested - NAVDate > 4 Then
BackADay = False
End If
wbPath = <Network Path Removed>
wbYear = CStr(Year(NAVDate)) & "\"
wbMonth = MonthName(Month(NAVDate)) & " " & wbYear
wbWeek = DateFormat(NAVDate) & "\"
wbFile = Dir(wbPath & wbYear & wbMonth & wbWeek & "*Valuation Package*.xlsx")
'Pricings with distributions have differing tree structure
If wbFile = "" Then
wbWeek = wbWeek & "POST Distribution " & wbWeek
wbFile = Dir(wbPath & wbYear & wbMonth & wbWeek & "*Valuation Package*.xlsx")
If wbFile = "" Then
NAVDate = NAVDate - 1
Else: BackADay = False
End If
Else: BackADay = False
End If
Loop
wbString = wbPath & wbYear & wbMonth & wbWeek & wbFile
Set wb = Workbooks.Open(wbString, UpdateLinks:=False, ReadOnly:=True)
Set GetWB = wb
End Function
Function DateFormat(ByVal X As Date) As String
'Appends leading zeroes if needed to achieve form "00" for any two digit integer, and converts to string
Dim MM As String
Dim DD As String
Dim YYYY As String
If Month(X) < 10 Then
MM = "0" & CStr(Month(X))
Else
MM = CStr(Month(X))
End If
If Day(X) < 10 Then
DD = "0" & CStr(Day(X))
Else
DD = CStr(Day(X))
End If
YYYY = CStr(Year(X))
DateFormat = MM & "." & DD & "." & YYYY
End Function
Variant
. - person PatricK   schedule 26.02.2016Variant
, а затем попытался просто удалить все определения типов. Оба дают одинаковое поведение. - person Micah Neely   schedule 26.02.2016Variant
тип входных данных дляNAV()
иFindItemOnWorksheet()
? Вам нужно будет изменить код для преобразования входных данных. Попробуйте пройти через это. Я думаю, что если вы сделаете это в ближайшем окне, вы получите ту же ошибку:?NAV(Range("D7"),Range("D8"),Range("D9"))
- person PatricK   schedule 26.02.2016=NAV(D7,D8,D9)
на рабочем листе по-прежнему дает «#ЗНАЧ!», ноPrint NAV(Range("D7"),Range("D8"),Range("D9"))
в непосредственном окне дает правильный ответ, точно так же, как когда я определял три аргумента непосредственно в непосредственном окне. - person Micah Neely   schedule 26.02.2016Application.Volatile True
? Open up wb и подобные вещи могут пересчитываться, пока первый расчет не завершен (попытка открыть открытый wb), а затем терпит неудачу... вообще не рекомендуется выполнять действия в UDF. - person Dirk Reichel   schedule 26.02.2016debug.print "start"
в качестве первого действия иdebug.print "end"
в качестве последнего (также перед функцией выхода)... если есть 2 запуска друг за другом без конца, то вы зацикливаете функцию... (не происходит в прямом окне, потому что всегда выполняется только один раз) - person Dirk Reichel   schedule 26.02.2016DateFormat
можно заменить встроеннойFormat(NAVDate,"MM.DD.YYYY")
. - person PatricK   schedule 26.02.2016