MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Function VBA Name changes in worksheet!!


Posted by Noo on January 06, 2002 12:19 AM

Hi

I made a custom function named as MYFunction. When I type this function in woksheet, its name turns to be "myfunction" without any capital letters.

How to keep the capital letter exactly as MYFunction ( not myfunction) while using this function in worksheet?

Thanks for any help.


Posted by Jack in UK on January 06, 2002 3:25 AM

Hi Noo--

Best you post up the full code and ill have a look
HTH
Jack

Posted by Troilus on January 06, 2002 8:41 AM


Why do you need the capital letters?
If the function works, what is the problem?

Posted by Jack in Uk on January 06, 2002 11:19 AM

Hi
Why do you need the capital letters?
If the function works, what is the problem?

Sorry to question your reply but the guy asked a question and the board is to try to answer and help eah other offeringf our opinions of what we know work and can work, many questions on this board are pointless to most posters who are ok with Excel but thats not the point it important to the questuioner, not us to question their reason or motive, i for one agree and so have requested they post the code, i write formula in just the same way and have foind the very same thing, and YES i fell the question is valid and deserves any surfers in this boards to read and if poss offer any assistance, just as something works, thats not always the full story.

I hope you understand i not digging, but opening the wondrful board to ANYONE to ask and Excel question regardless of level or knowledge, i for one have ask many odd questions and had full responce from helpful guys, WITHOUT critisam, we are not to critisise posters, else what is the point of this board..

Rdgs
Jack, (No offence ment..)

Posted by Dank on January 06, 2002 3:32 PM

Hello,

Is the function named MYFunction in the VBA module? The reason I ask is that I just tried this:-

Function MYFunction()
MYFunction = 5
End Function

When I type =myfunction() into a worksheet it changes it to MYFunction(). Which version of Excel are you using and have you any options set in the module? As Jack suggested it's probably worth posting your code (including all declarations at the top of the module) so someone else can try it on their machine.

Kind regards,
Daniel.

Posted by rm on January 06, 2002 4:06 PM

thank you Jack

Thanks Jack...though this post has nothing to do with this thread, I would like to thank you and agree with your post...the reason that this is my favorite site is that it matters not what level of expertise you possess with excel...experts and novices alike are able to share ideas and gain knowledge and "newbies" can feel comfortable asking any question without being made to feel that they are dumb or lower than anyone else--(hey, even the experts began knowing nothing)--and if someone doesn't "get it" why someone is questioning something, then they should not even respond because they obviously can't help...after all, if someone asks a question, they want help, not criticism.

...and many, many thanks to all who have graciously offered help and advice to all of my previous posts...I would be totally lost without it.

rm

Posted by Troilus on January 06, 2002 4:08 PM


My questions were quite valid.
I can think of no reason why the poster would have such a requirenent and was curious to know why - since there may well be a valid reason of which I am unaware.

Posted by Troilus on January 06, 2002 4:57 PM

Can the class now be dismissed? .......

...and many, many thanks to all who have graciously offered help and advice to all of my previous posts...I would be totally lost without it. rm


Thanks very much for telling me what I should and shouldn't say and do !

John Lennon once said (or it might have been Bob Dylan) that whenever he released a new song he could hardly wait to read the reviews so that he could find out what he meant by the words he had written.

Posted by Cressida on January 06, 2002 9:17 PM

It was John Lennon.

Posted by Noo on January 07, 2002 12:47 AM

The reason I ask ...

I have named MYFunction mixed with capital letter to help user be notified wheter spelling is right or wrong when press enter. It is the same as typing =sum(a1) then Excel changes it to =SUM(A1) which also mean that it is accepted.

Some of my custom function has very long name such as PositionOfArraySpot(). Capital letter also helps me read this function easier than just "positionofarrayspot()".

Posted by Ivan F Moala on January 07, 2002 1:31 AM

Re: The reason I ask ...

That is exactly WHY you should do it this way
and is good practice.
I have come across then problem on occasions
and then it would go away....don't recall what
I did....but I think it involved referencing
diff versions ??? Will have a look through my workbooks.


Ivan I have named MYFunction mixed with capital letter to help user be notified wheter spelling is right or wrong when press enter. It is the same as typing =sum(a1) then Excel changes it to =SUM(A1) which also mean that it is accepted. Some of my custom function has very long name such as PositionOfArraySpot(). Capital letter also helps me read this function easier than just "positionofarrayspot()".

Posted by Troilus on January 07, 2002 8:01 AM

OK. - can appreciate the logic of that. Thanks.

Some of my custom function has very long name such as PositionOfArraySpot(). Capital letter also helps me read this function easier than just "positionofarrayspot()".

Posted by Jack in UK on January 07, 2002 11:20 AM

Re: Can the class now be dismissed? .......

Ok this is the answer as i see it, and i havetested it and works for me, but glad of better of different methods.... Ok we have had enough silly comments and i stand by ask away and people will help

Excel VBA editor reas non case related so upper or lower matters little in Versions E97 and above, i have today tested all 97/2000/2002XP and thisis common, but E95 was different and fussy, dont know why, E94 or Version 4 i once had and was not VBA as such was module system yuck!

OK my fix is this... your posted highlight this the opperater remembering a home growm function, well today i was asked to comvert a string of 3500 data into XYZ1 and so on, this i did i had my bas file emailed over and bingo done, but the function read this JACK_AddA(IntergerOffset Row, IntergerOffsetColumn) but the guy could not remember how to spell my name JAck its not, at work i use my real name, so this is the fix, in the cell required i typed = only then clicked the function wizard and scroll down to my name not hard to miss and click that selection and then input the arguments.

Je was well happy, and so now he has all my many coll formula, and can access then without remenbering any of them they are all :
MYName_Add or TakeOff or Conv2Z or RemvA whatever all start with Jack_

Thats teh best and full proof answer

Also te function returns the true name of the function AS IS in the code so will be case related....

Now thats the way i now work and others i work with do also and thay are lower level than any of us posters.

Thats my call and take on it.

Take care
HTH
Jack in the UK