Minor puzzle: some UDF calls respect mixed case, others insist on lower case
in [Programming]
|
Prev: Error message on opening Excel after deleting add-in file
Next: The VBA IDE will discard changes without warning
From: Prof Wonmug on 30 Apr 2010 11:58 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.
From: JLGWhiz on 30 Apr 2010 13:28 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" <wonmug(a)e.mcc> wrote in message news:dqult595tk6790hego2nbd3h7c5ikkqpoh(a)4ax.com... >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.
From: Prof Wonmug on 30 Apr 2010 14:53 On Fri, 30 Apr 2010 13:28:45 -0400, "JLGWhiz" <JLGWhiz(a)cfl.rr.com> 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" <wonmug(a)e.mcc> wrote in message >news:dqult595tk6790hego2nbd3h7c5ikkqpoh(a)4ax.com... >>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. >
From: Helmut Meukel on 30 Apr 2010 15:13 "Prof Wonmug" <wonmug(a)e.mcc> schrieb im Newsbeitrag news:an9mt5pcc9vd5f012jd72tkl6kqed8ekf6(a)4ax.com... > On Fri, 30 Apr 2010 13:28:45 -0400, "JLGWhiz" <JLGWhiz(a)cfl.rr.com> > 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" <wonmug(a)e.mcc> wrote in message >>news:dqult595tk6790hego2nbd3h7c5ikkqpoh(a)4ax.com... >>>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.
From: JLGWhiz on 30 Apr 2010 16:09
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" <wonmug(a)e.mcc> wrote in message news:an9mt5pcc9vd5f012jd72tkl6kqed8ekf6(a)4ax.com... > On Fri, 30 Apr 2010 13:28:45 -0400, "JLGWhiz" <JLGWhiz(a)cfl.rr.com> > 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" <wonmug(a)e.mcc> wrote in message >>news:dqult595tk6790hego2nbd3h7c5ikkqpoh(a)4ax.com... >>>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. >> |