Hi,
I am currently experiencing an issue whereby, i have received an excel file from a colleague which is running a specific function. However, when i try to run that function on my computer i get the #NAME error. The function basically just creates an array of values.
Any advice would be much appreciated.
Thanks
Did you allow macros to be enabled when you opened the file?
Pete
Hi,
Yes i did.
Maybe a colleague is using an Excel function that is not available in your version of Excel.
Is this an Excel function or a custom function written in VBA?
Artik
Hi i dont think thats the case because its a custom function developed with VBA
Well, since guessing and assuming is not used in programming or writing formula I think this post could go on for ages.
Why don't you just paste the function and or context, formula or vba code so that you might get logical answers?
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
Thanks for your reply. I know for a fact that it's not an issue with the code because my colleague had his laptop next to mine, ran the exact same code and called the function in the exact same way as i did on my computer and the function worked on his computer but got error #NAME on mine.
Cheers
Maybe check Tools - References for missing Reference.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
i have recreated that issue on a file that i first uploaded to google drive, then downloaded again.
heres how i fix it:
1. double click the topmost cell in a column that has the #NAME? error.
2. double click the formula to highlight it all
3. press the right arrow to get to the end of the formula
4. now click the ENTER key
this should fix the error in that cell
5. click that cell again to select it
6. now drag down to other cells that share the same formula
Last edited by janmorris; 09-05-2021 at 04:04 AM.
Thanks for your suggestions but I checked both of them and they made no difference.
Cheers
We can keep on guessing but it would be a lot easier when you post an example file with some data to work with and the function in it.
your comment in post #5 proves nothing.
why not follow @Kebellah's advice in post #6, you may get closer to a resolution than continuing random guesswork.
Torachan,
Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.
Ok attached is the file. Please be aware that i am an absolute amateur at macros (and excel in general) - thx
Does your version of Excel support spilled formulas?
Artik
Will check and get back to you, but, without vba and little Excel knowledge you'll not get far and have to depend on others, I suggest you Goggle for tutorials starting from basics, it really helps and trial and error recording macros and editing is how moet of us started. I never had or followed any courses
like you i also avoid VBA, but that is because im on macOS and theres some VBA errors that cannot fixed when transporting an excel file from windows to mac.
anyhow, heres what i get (i guess its working fine)
And now you come with it, MAC and Windows
You could have started with THAT!
Eating someone’s time with incomplete information
Good luck
The OP didn't say he was using a Mac, janmorris uses a Mac.Originally Posted by Keebellah
That asid, it works OK on Windows 64 bit with a Windows 365 subscription.
I can make it produce a #SPILL! error by putting something in a cell below the formula, but not a #NAME! error.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
The only way I can get a #NAME? error is if the name of the function is misspelled or the function doesn't exist (same difference really).
Ok thanks for your input. Looks like the issue is that I am not running office 365 and office 2019 doesnt support spilled formulas?
Cheers
Keebellah, please dont be rude
Not sure. I don't think that would give you a #NAME? error.
https://support.microsoft.com/en-us/...1-87a7eb36e531
Have you tried inputting the formula as an Array Formula using Ctrl-Shift-Enter and dragging the formula down? Can't see that working though.
Is it exactly the same workbook that has the formula that doesn't work? Or have you copied and/or retyped the formula?
All I CAN say is it works for me as uploaded in Windows 64 bit with Excel 365 subscription.
You'd only get a #NAME error there if macros are disabled.
Remember what the dormouse said
Feed your head
Hi, have tried cntrl+shift+enter - didnt work. Yes it is the same workbook and i have tried copying and re-typing the formula. Think i could maybe get the free trial for 365 and see if that changes things.
Appreciate your help
Do other macros and/or VBA User Defined Functions work in this workbook? See rorya's post #23.
TMS, it is possible that MS does not tell us everything. Look here.
Just in case, an excerpt from the site:
the creeker, probably in your version you can use this function as an array formula (Shift + Ctrl + Enter). Select the multi-cell range (from top to bottom), press [F2], enter the formula and arguments into the first cell and press Shift + Ctrl + Enter.Most dynamic array formulas (but not all!) will keep displaying their results in legacy Excel until you make any changes to them. Editing a formula immediately breaks it and displays one or more #NAME? error values.
The problem is that you don't know how many cells to select. Therefore, I have prepared a procedure for you that will do it for you.
Usage:
1. Select the cell from which to start inserting the array formula. Make sure that the range below this cell is empty as existing values will be overwritten.
2. Run the LaunchMyFormula macro and enter two arguments separated by a semicolon ( ; ).
Reading previous posts, it comes to my mind that you may have settings in Excel such that macros are disabled. You must then change to enable macros.
Artik
My Excel 2010 do not have the built-in SORT worksheet function, so I used "System.Collections.ArrayList" to sort the "steps" array and needed to transpose the array at the end, without making any other changes.
Hence, it seems working on Excel 2010 (sample file is attached)
Please Login or Register to view this content.
Last edited by Haluk; 09-06-2021 at 10:27 AM.
Note that .Net Framework version 3.5 is required to use ArrayList. You may have other versions, but without version 3.5 the ArrayList may not work properly.
Artik
I've .Net Framework 4.8 installed and "System.Collections.ArrayList" works fine.
BTW, any other sorting routine can also be used if needed...
Have you checked that you really don't have version 3.5 installed? As I mentioned, you can have several versions installed.
Artik
Yes, I really don't have .Net 3.5 installed.
BTW, the registery editor shows that 3.5 is also installed....
And this is another alternative in sorting the array where a very classical routine is used.
(Sample workbook is also attached).
Please Login or Register to view this content.
For your information: Period Function 2 and 3 work fine here, Excel 2016 32-but on W10 64-bit
Keebellah, thanks for the information.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks