Minor puzzle: some UDF calls respect mixed case, others insist on lower case
I have a bunch of UDFs that I have used for years hundreds of times in
several workbooks. The names of the UDFs in the add-in modules are coded in mixed case (RayleighMean, RndTallyLog, & ExpSF). Up until a few days ago, whenever I would code a UDF call in a cell (=ExpSF(...)), Excel would change the name to lower case (=expsf(...)). I recall trying repeatedly to get Excel to leave it in mixed case. A few days ago, I wrote a new UDF called FmtTime. I just noticed that all of the calls to this one function are in mixed case just like I entered them. In fact, if I try to change it to all lower case (fmttime), Excel changes it back to FmtTime. What the heck is going on? Excel is like a lot of girlfriends I've had...can't live with them or without them. |
Minor puzzle: some UDF calls respect mixed case, others insist on lower case
Check your code for the UDF. If you used lower case within the code, it
might be VBA doing the conversion, rather than Excel. I know that I have been surprised a few times by certain object variables turning up in a differenct case than originally declared. "Prof Wonmug" wrote in message ... I have a bunch of UDFs that I have used for years hundreds of times in several workbooks. The names of the UDFs in the add-in modules are coded in mixed case (RayleighMean, RndTallyLog, & ExpSF). Up until a few days ago, whenever I would code a UDF call in a cell (=ExpSF(...)), Excel would change the name to lower case (=expsf(...)). I recall trying repeatedly to get Excel to leave it in mixed case. A few days ago, I wrote a new UDF called FmtTime. I just noticed that all of the calls to this one function are in mixed case just like I entered them. In fact, if I try to change it to all lower case (fmttime), Excel changes it back to FmtTime. What the heck is going on? Excel is like a lot of girlfriends I've had...can't live with them or without them. |
Minor puzzle: some UDF calls respect mixed case, others insist on lower case
On Fri, 30 Apr 2010 13:28:45 -0400, "JLGWhiz"
wrote: Check your code for the UDF. If you used lower case within the code, it might be VBA doing the conversion, rather than Excel. I know that I have been surprised a few times by certain object variables turning up in a differenct case than originally declared. As I said in my post, the UDF names are in mixed case (see below). "Prof Wonmug" wrote in message .. . I have a bunch of UDFs that I have used for years hundreds of times in several workbooks. The names of the UDFs in the add-in modules are coded in mixed case (RayleighMean, RndTallyLog, & ExpSF). Up until a few days ago, whenever I would code a UDF call in a cell (=ExpSF(...)), Excel would change the name to lower case (=expsf(...)). I recall trying repeatedly to get Excel to leave it in mixed case. A few days ago, I wrote a new UDF called FmtTime. I just noticed that all of the calls to this one function are in mixed case just like I entered them. In fact, if I try to change it to all lower case (fmttime), Excel changes it back to FmtTime. What the heck is going on? Excel is like a lot of girlfriends I've had...can't live with them or without them. |
Minor puzzle: some UDF calls respect mixed case, others insist on lower case
"Prof Wonmug" schrieb im Newsbeitrag
... On Fri, 30 Apr 2010 13:28:45 -0400, "JLGWhiz" wrote: Check your code for the UDF. If you used lower case within the code, it might be VBA doing the conversion, rather than Excel. I know that I have been surprised a few times by certain object variables turning up in a differenct case than originally declared. As I said in my post, the UDF names are in mixed case (see below). "Prof Wonmug" wrote in message . .. I have a bunch of UDFs that I have used for years hundreds of times in several workbooks. The names of the UDFs in the add-in modules are coded in mixed case (RayleighMean, RndTallyLog, & ExpSF). In VB (and VBA) this may happen if you use the same name twice. It happend to me when I had a User Defined Type element with the same name as a local variable. Or a Function in one module and a variable or UDT element in annother module with the same name. It's allowed if there is no ambiguity, but VB will use only one case (both same mixed case or both lowercase or both uppercase). HTH. Helmut. |
Minor puzzle: some UDF calls respect mixed case, others insist on lower case
Helmut is describing the conditions that I referred to. It is not Excel
playing tricks. It is related to your code and how the VBA compiler stores your names, variables, etc. in memory. If code is written inconsistently in respect to case, VBA will use its own logic to determine what is stored in memory and that is what will be displayed. Doesn't make any difference whether case displayed is lower, upper or mixed, what you see is what VBA has in memory for that item. So if you want it to be consistent, then it will be necessary to review your code to see where the anomaly occurred. "Prof Wonmug" wrote in message ... On Fri, 30 Apr 2010 13:28:45 -0400, "JLGWhiz" wrote: Check your code for the UDF. If you used lower case within the code, it might be VBA doing the conversion, rather than Excel. I know that I have been surprised a few times by certain object variables turning up in a differenct case than originally declared. As I said in my post, the UDF names are in mixed case (see below). "Prof Wonmug" wrote in message . .. I have a bunch of UDFs that I have used for years hundreds of times in several workbooks. The names of the UDFs in the add-in modules are coded in mixed case (RayleighMean, RndTallyLog, & ExpSF). Up until a few days ago, whenever I would code a UDF call in a cell (=ExpSF(...)), Excel would change the name to lower case (=expsf(...)). I recall trying repeatedly to get Excel to leave it in mixed case. A few days ago, I wrote a new UDF called FmtTime. I just noticed that all of the calls to this one function are in mixed case just like I entered them. In fact, if I try to change it to all lower case (fmttime), Excel changes it back to FmtTime. What the heck is going on? Excel is like a lot of girlfriends I've had...can't live with them or without them. |
Minor puzzle: some UDF calls respect mixed case, others insist onlower case
From what I can tell, excel remembers the first time you use the UDF in a
worksheet cell. It won't matter how the function is declared in your VBA code. After you've used it once (in any workbook!), excel will remember it forever. Unless... You do something to make excel forget it. This worked for me. I created a UDF: Function Test88() as boolean Test88 = True End Function Then I used: =TEST88() in a cell in that workbook. And excel remembered the case after that. So I changed the name of my function in the code: Function Test88xxx() as boolean Then back to excel to create a new workbook name. I selected any old cell I wanted. Insert|name|define TeSt88 (note the funny case). And let it point at that cell. I typed this in a cell: =test88 and excel converted it to: =TeSt88 Then I deleted the name (Insert|Name dialog) I fixed the UDF function name in the VBE. And recalculated. (I sometimes select all the cells, edit|replace equal signs with equal signs to force all the functions to recalc.) And the case of my function was changed to: =TeSt88() and excel remembered it when I used it again (no matter how I typed it in). Prof Wonmug wrote: I have a bunch of UDFs that I have used for years hundreds of times in several workbooks. The names of the UDFs in the add-in modules are coded in mixed case (RayleighMean, RndTallyLog, & ExpSF). Up until a few days ago, whenever I would code a UDF call in a cell (=ExpSF(...)), Excel would change the name to lower case (=expsf(...)). I recall trying repeatedly to get Excel to leave it in mixed case. A few days ago, I wrote a new UDF called FmtTime. I just noticed that all of the calls to this one function are in mixed case just like I entered them. In fact, if I try to change it to all lower case (fmttime), Excel changes it back to FmtTime. What the heck is going on? Excel is like a lot of girlfriends I've had...can't live with them or without them. -- Dave Peterson |
All times are GMT +1. The time now is 01:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com