Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-defined function clitch ...
Sorry, I'm back again.
I've written a user-defined function to replace the often confusing V/HLookup(...Match(...) .) combination used in spreadsheets to return an element in a bordered (top and left edges) table. Here's the code: Function GETENTRY(atRow As Variant, _ atCol As Variant, _ inTable As Range) As Variant Dim i As Integer, j As Integer On Error Resume Next With Application i = .Match(atRow, inTable.Columns(1), 0) j = .Match(atCol, inTable.Rows(1), 0) End With If Err < 0 Then MsgBox "One of " & atRow & " or " & atCol & " _ not found.", vbCritical GETENTRY = CVErr(xlErrNA) Exit Function End If GETENTRY = inTable(i, j) End Function Here's the problem: It works fine if invoked directly, but if I use Excel's Paste (Insert) function dialogue box, then as soon as I click on the top left corner of the range for the 3rd arg (inTable), it triggers the error message. (Actually, it doesn't exit the function as I expect either, instead it flashes the message and waits for new input. Hitting the cancel button on the dialogue box triggers the message again. But that's a whole nuther confusion and I don't want to get side-tracked.) I can avoid this outcome in 3 ways: a) enter one of the other two args as the last one specified in the dialogue box, b) remove the On Error Resume Next statement, or c) and this is really wierd, remove the MsgBox statement. Choice a) is clearly not reasonable - I can't expect others to know that trick - and choices b) & c) frustrate my *truly wonderful* error handling. ('course, the only reason I found out about this bug is that I was so excited to see Excel list MY FUNCTION that I just had to try it. Hubrous was my downfall.) I'm really confused about this, especially the REM'ing out the msgbox part. Does anyone have any idea about what's going on. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-defined function clitch ...
Forgot to mention two things: I'm working in xl97, and Dave, if you see this
and it seems familiar, the earlier question was from me trying to compare the speed of my function with direct calls to the native spreadsheet functions (turns out to be about half as fast). Your answers helped me rethink the function itself, thanks. Tony George Raft wrote in message ... Sorry, I'm back again. I've written a user-defined function to replace the often confusing V/HLookup(...Match(...) .) combination used in spreadsheets to return an element in a bordered (top and left edges) table. Here's the code: Function GETENTRY(atRow As Variant, _ atCol As Variant, _ inTable As Range) As Variant Dim i As Integer, j As Integer On Error Resume Next With Application i = .Match(atRow, inTable.Columns(1), 0) j = .Match(atCol, inTable.Rows(1), 0) End With If Err < 0 Then MsgBox "One of " & atRow & " or " & atCol & " _ not found.", vbCritical GETENTRY = CVErr(xlErrNA) Exit Function End If GETENTRY = inTable(i, j) End Function Here's the problem: It works fine if invoked directly, but if I use Excel's Paste (Insert) function dialogue box, then as soon as I click on the top left corner of the range for the 3rd arg (inTable), it triggers the error message. (Actually, it doesn't exit the function as I expect either, instead it flashes the message and waits for new input. Hitting the cancel button on the dialogue box triggers the message again. But that's a whole nuther confusion and I don't want to get side-tracked.) I can avoid this outcome in 3 ways: a) enter one of the other two args as the last one specified in the dialogue box, b) remove the On Error Resume Next statement, or c) and this is really wierd, remove the MsgBox statement. Choice a) is clearly not reasonable - I can't expect others to know that trick - and choices b) & c) frustrate my *truly wonderful* error handling. ('course, the only reason I found out about this bug is that I was so excited to see Excel list MY FUNCTION that I just had to try it. Hubrous was my downfall.) I'm really confused about this, especially the REM'ing out the msgbox part. Does anyone have any idea about what's going on. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-defined function clitch ...
You may have noticed the when you're editing a cell, many of the icons on the
toolbars are disabled. You can have your function test an icon. (I think that this was suggested by Jan Karel Pieterse: Option Explicit Function GETENTRY(atRow As Variant, _ atCol As Variant, _ inTable As Range) As Variant Dim i As Long, j As Long If Application.CommandBars(1).FindControl(ID:=23, Recursive:=True).Enabled _ = False Then Exit Function End If On Error Resume Next With Application i = .Match(atRow, inTable.Columns(1), 0) j = .Match(atCol, inTable.Rows(1), 0) End With If Err < 0 Then 'MsgBox "One of " & atRow & " or " & atCol & "not found.", vbCritical GETENTRY = CVErr(xlErrNA) Exit Function End If GETENTRY = inTable(i, j) End Function And I would be very wary about putting that message box into your function. If you have lots of formulas that refer to the same table and you change a heading row, you may be dismissing these message boxes for a longgggggg time. George Raft wrote: Sorry, I'm back again. I've written a user-defined function to replace the often confusing V/HLookup(...Match(...) .) combination used in spreadsheets to return an element in a bordered (top and left edges) table. Here's the code: Function GETENTRY(atRow As Variant, _ atCol As Variant, _ inTable As Range) As Variant Dim i As Integer, j As Integer On Error Resume Next With Application i = .Match(atRow, inTable.Columns(1), 0) j = .Match(atCol, inTable.Rows(1), 0) End With If Err < 0 Then MsgBox "One of " & atRow & " or " & atCol & " _ not found.", vbCritical GETENTRY = CVErr(xlErrNA) Exit Function End If GETENTRY = inTable(i, j) End Function Here's the problem: It works fine if invoked directly, but if I use Excel's Paste (Insert) function dialogue box, then as soon as I click on the top left corner of the range for the 3rd arg (inTable), it triggers the error message. (Actually, it doesn't exit the function as I expect either, instead it flashes the message and waits for new input. Hitting the cancel button on the dialogue box triggers the message again. But that's a whole nuther confusion and I don't want to get side-tracked.) I can avoid this outcome in 3 ways: a) enter one of the other two args as the last one specified in the dialogue box, b) remove the On Error Resume Next statement, or c) and this is really wierd, remove the MsgBox statement. Choice a) is clearly not reasonable - I can't expect others to know that trick - and choices b) & c) frustrate my *truly wonderful* error handling. ('course, the only reason I found out about this bug is that I was so excited to see Excel list MY FUNCTION that I just had to try it. Hubrous was my downfall.) I'm really confused about this, especially the REM'ing out the msgbox part. Does anyone have any idea about what's going on. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-defined function clitch ...
Good point about the messages - hadn't thought about that, and it is
intended for use in many tables. Of course, if I remove it, the problem goes away (why, I don't understand), but thaar ya go. I also don't understand what the proposed test is doing, or how we know the Paste Function dialogue box is the first in the CMDbars collection, but I'll fiddle with it a bit to see if I can't learn something. Thanks again. Tony Dave Peterson wrote in message ... You may have noticed the when you're editing a cell, many of the icons on the toolbars are disabled. You can have your function test an icon. (I think that this was suggested by Jan Karel Pieterse: Option Explicit Function GETENTRY(atRow As Variant, _ atCol As Variant, _ inTable As Range) As Variant Dim i As Long, j As Long If Application.CommandBars(1).FindControl(ID:=23, Recursive:=True).Enabled _ = False Then Exit Function End If On Error Resume Next With Application i = .Match(atRow, inTable.Columns(1), 0) j = .Match(atCol, inTable.Rows(1), 0) End With If Err < 0 Then 'MsgBox "One of " & atRow & " or " & atCol & "not found.", vbCritical GETENTRY = CVErr(xlErrNA) Exit Function End If GETENTRY = inTable(i, j) End Function And I would be very wary about putting that message box into your function. If you have lots of formulas that refer to the same table and you change a heading row, you may be dismissing these message boxes for a longgggggg time. George Raft wrote: Sorry, I'm back again. I've written a user-defined function to replace the often confusing V/HLookup(...Match(...) .) combination used in spreadsheets to return an element in a bordered (top and left edges) table. Here's the code: Function GETENTRY(atRow As Variant, _ atCol As Variant, _ inTable As Range) As Variant Dim i As Integer, j As Integer On Error Resume Next With Application i = .Match(atRow, inTable.Columns(1), 0) j = .Match(atCol, inTable.Rows(1), 0) End With If Err < 0 Then MsgBox "One of " & atRow & " or " & atCol & " _ not found.", vbCritical GETENTRY = CVErr(xlErrNA) Exit Function End If GETENTRY = inTable(i, j) End Function Here's the problem: It works fine if invoked directly, but if I use Excel's Paste (Insert) function dialogue box, then as soon as I click on the top left corner of the range for the 3rd arg (inTable), it triggers the error message. (Actually, it doesn't exit the function as I expect either, instead it flashes the message and waits for new input. Hitting the cancel button on the dialogue box triggers the message again. But that's a whole nuther confusion and I don't want to get side-tracked.) I can avoid this outcome in 3 ways: a) enter one of the other two args as the last one specified in the dialogue box, b) remove the On Error Resume Next statement, or c) and this is really wierd, remove the MsgBox statement. Choice a) is clearly not reasonable - I can't expect others to know that trick - and choices b) & c) frustrate my *truly wonderful* error handling. ('course, the only reason I found out about this bug is that I was so excited to see Excel list MY FUNCTION that I just had to try it. Hubrous was my downfall.) I'm really confused about this, especially the REM'ing out the msgbox part. Does anyone have any idea about what's going on. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-defined function clitch ...
The function wizard calls the specified function *during* data entry by
the user. It is called as soon as the user enters something in the last non-optional field (*something* as in *any* thing). Your function has to be able to handle that. That means that the use of Msgbox can be extremely disruptive. So, it is not weird that removing the Msgbox fixes the problem. Also, if I were you, I would not relegate the informative error message to a interruptive msgbox but actually return the information(instead of the oh-so-60s #N/A or #VALUE stuff) To see how the function wizard works, note that in its dialog box, the result of the function are shown *twice.* The first display is to the right of and just below the area where the arguments are specified. The second is towards the bottom left of the dialog box where it reads "Formula result=". In fact the function is *called* twice. This becomes very obvious with the following two functions. Try using them with the function wizard and observe when the wizard starts displaying the results as well as what the results are. Function getRand(a, b, c) getRand = Rnd() End Function Function getRandOpt(Optional a, Optional b, Optional c) getRandOpt = Rnd() End Function Also, you could better leverage the error handling and not leave it to the user to guess what didn't work. If I were in your shoes, I would code your function along the lines of: Function GETENTRY(atRow As Double, atCol As Double, _ inTable As Range) As Variant Dim i As Long, j As Long On Error Resume Next With Application.WorksheetFunction i = .Match(atRow, inTable.Columns(1), 0) j = .Match(atCol, inTable.Rows(1), 0) End With On Error GoTo 0 If i = 0 Then GETENTRY = "atRow value (" & atRow & ") not found" ElseIf j = 0 Then GETENTRY = "atCol value (" & atCol & ") not found" Else GETENTRY = inTable(i, j) End If End Function The above function works for direct data entry or through the function wizard. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Sorry, I'm back again. I've written a user-defined function to replace the often confusing V/HLookup(...Match(...) .) combination used in spreadsheets to return an element in a bordered (top and left edges) table. Here's the code: Function GETENTRY(atRow As Variant, _ atCol As Variant, _ inTable As Range) As Variant Dim i As Integer, j As Integer On Error Resume Next With Application i = .Match(atRow, inTable.Columns(1), 0) j = .Match(atCol, inTable.Rows(1), 0) End With If Err < 0 Then MsgBox "One of " & atRow & " or " & atCol & " _ not found.", vbCritical GETENTRY = CVErr(xlErrNA) Exit Function End If GETENTRY = inTable(i, j) End Function Here's the problem: It works fine if invoked directly, but if I use Excel's Paste (Insert) function dialogue box, then as soon as I click on the top left corner of the range for the 3rd arg (inTable), it triggers the error message. (Actually, it doesn't exit the function as I expect either, instead it flashes the message and waits for new input. Hitting the cancel button on the dialogue box triggers the message again. But that's a whole nuther confusion and I don't want to get side-tracked.) I can avoid this outcome in 3 ways: a) enter one of the other two args as the last one specified in the dialogue box, b) remove the On Error Resume Next statement, or c) and this is really wierd, remove the MsgBox statement. Choice a) is clearly not reasonable - I can't expect others to know that trick - and choices b) & c) frustrate my *truly wonderful* error handling. ('course, the only reason I found out about this bug is that I was so excited to see Excel list MY FUNCTION that I just had to try it. Hubrous was my downfall.) I'm really confused about this, especially the REM'ing out the msgbox part. Does anyone have any idea about what's going on. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-defined function clitch ...
Start typing anything into a cell--but don't hit enter.
Now click on File on the worksheet menubar. Look at the "Open..." menu item. Notice that it's greyed out. This menuitem gets checked in this line: If Application.CommandBars(1).FindControl(ID:=23, Recursive:=True).Enabled _ ... If it's greyed out (.enabled = false), then get out of your function. George Raft wrote: Good point about the messages - hadn't thought about that, and it is intended for use in many tables. Of course, if I remove it, the problem goes away (why, I don't understand), but thaar ya go. I also don't understand what the proposed test is doing, or how we know the Paste Function dialogue box is the first in the CMDbars collection, but I'll fiddle with it a bit to see if I can't learn something. Thanks again. Tony Dave Peterson wrote in message ... You may have noticed the when you're editing a cell, many of the icons on the toolbars are disabled. You can have your function test an icon. (I think that this was suggested by Jan Karel Pieterse: Option Explicit Function GETENTRY(atRow As Variant, _ atCol As Variant, _ inTable As Range) As Variant Dim i As Long, j As Long If Application.CommandBars(1).FindControl(ID:=23, Recursive:=True).Enabled _ = False Then Exit Function End If On Error Resume Next With Application i = .Match(atRow, inTable.Columns(1), 0) j = .Match(atCol, inTable.Rows(1), 0) End With If Err < 0 Then 'MsgBox "One of " & atRow & " or " & atCol & "not found.", vbCritical GETENTRY = CVErr(xlErrNA) Exit Function End If GETENTRY = inTable(i, j) End Function And I would be very wary about putting that message box into your function. If you have lots of formulas that refer to the same table and you change a heading row, you may be dismissing these message boxes for a longgggggg time. George Raft wrote: Sorry, I'm back again. I've written a user-defined function to replace the often confusing V/HLookup(...Match(...) .) combination used in spreadsheets to return an element in a bordered (top and left edges) table. Here's the code: Function GETENTRY(atRow As Variant, _ atCol As Variant, _ inTable As Range) As Variant Dim i As Integer, j As Integer On Error Resume Next With Application i = .Match(atRow, inTable.Columns(1), 0) j = .Match(atCol, inTable.Rows(1), 0) End With If Err < 0 Then MsgBox "One of " & atRow & " or " & atCol & " _ not found.", vbCritical GETENTRY = CVErr(xlErrNA) Exit Function End If GETENTRY = inTable(i, j) End Function Here's the problem: It works fine if invoked directly, but if I use Excel's Paste (Insert) function dialogue box, then as soon as I click on the top left corner of the range for the 3rd arg (inTable), it triggers the error message. (Actually, it doesn't exit the function as I expect either, instead it flashes the message and waits for new input. Hitting the cancel button on the dialogue box triggers the message again. But that's a whole nuther confusion and I don't want to get side-tracked.) I can avoid this outcome in 3 ways: a) enter one of the other two args as the last one specified in the dialogue box, b) remove the On Error Resume Next statement, or c) and this is really wierd, remove the MsgBox statement. Choice a) is clearly not reasonable - I can't expect others to know that trick - and choices b) & c) frustrate my *truly wonderful* error handling. ('course, the only reason I found out about this bug is that I was so excited to see Excel list MY FUNCTION that I just had to try it. Hubrous was my downfall.) I'm really confused about this, especially the REM'ing out the msgbox part. Does anyone have any idea about what's going on. -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-defined function clitch ...
Paste Function dialogue box is the first in the CMDbars collection
It isn't. The Worksheet Menu Bar is the first member of the commandbars collection. -- Regards, Tom Ogilvy "George Raft" wrote in message ... Good point about the messages - hadn't thought about that, and it is intended for use in many tables. Of course, if I remove it, the problem goes away (why, I don't understand), but thaar ya go. I also don't understand what the proposed test is doing, or how we know the Paste Function dialogue box is the first in the CMDbars collection, but I'll fiddle with it a bit to see if I can't learn something. Thanks again. Tony Dave Peterson wrote in message ... You may have noticed the when you're editing a cell, many of the icons on the toolbars are disabled. You can have your function test an icon. (I think that this was suggested by Jan Karel Pieterse: Option Explicit Function GETENTRY(atRow As Variant, _ atCol As Variant, _ inTable As Range) As Variant Dim i As Long, j As Long If Application.CommandBars(1).FindControl(ID:=23, Recursive:=True).Enabled _ = False Then Exit Function End If On Error Resume Next With Application i = .Match(atRow, inTable.Columns(1), 0) j = .Match(atCol, inTable.Rows(1), 0) End With If Err < 0 Then 'MsgBox "One of " & atRow & " or " & atCol & "not found.", vbCritical GETENTRY = CVErr(xlErrNA) Exit Function End If GETENTRY = inTable(i, j) End Function And I would be very wary about putting that message box into your function. If you have lots of formulas that refer to the same table and you change a heading row, you may be dismissing these message boxes for a longgggggg time. George Raft wrote: Sorry, I'm back again. I've written a user-defined function to replace the often confusing V/HLookup(...Match(...) .) combination used in spreadsheets to return an element in a bordered (top and left edges) table. Here's the code: Function GETENTRY(atRow As Variant, _ atCol As Variant, _ inTable As Range) As Variant Dim i As Integer, j As Integer On Error Resume Next With Application i = .Match(atRow, inTable.Columns(1), 0) j = .Match(atCol, inTable.Rows(1), 0) End With If Err < 0 Then MsgBox "One of " & atRow & " or " & atCol & " _ not found.", vbCritical GETENTRY = CVErr(xlErrNA) Exit Function End If GETENTRY = inTable(i, j) End Function Here's the problem: It works fine if invoked directly, but if I use Excel's Paste (Insert) function dialogue box, then as soon as I click on the top left corner of the range for the 3rd arg (inTable), it triggers the error message. (Actually, it doesn't exit the function as I expect either, instead it flashes the message and waits for new input. Hitting the cancel button on the dialogue box triggers the message again. But that's a whole nuther confusion and I don't want to get side-tracked.) I can avoid this outcome in 3 ways: a) enter one of the other two args as the last one specified in the dialogue box, b) remove the On Error Resume Next statement, or c) and this is really wierd, remove the MsgBox statement. Choice a) is clearly not reasonable - I can't expect others to know that trick - and choices b) & c) frustrate my *truly wonderful* error handling. ('course, the only reason I found out about this bug is that I was so excited to see Excel list MY FUNCTION that I just had to try it. Hubrous was my downfall.) I'm really confused about this, especially the REM'ing out the msgbox part. Does anyone have any idea about what's going on. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
user defined function | Excel Worksheet Functions | |||
user defined function help | Excel Worksheet Functions | |||
User Defined Function | Excel Discussion (Misc queries) | |||
User-defined data type; Error: Only User-defined types... | Excel Programming | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |