Forum Home
Press F1
Thread ID: 84572	2007-11-10 20:15:00	XL Problem.	B.M. (505)	Press F1

Post ID	Timestamp	Content	User
610132	2007-11-10 20:15:00	I’ve got a neat little spreadsheet designed by an XL gun, but all of a sudden it’s started giving trouble. I figure there is something hidden somewhere, but have no idea where to look. In cell G5 we have the numbers 1,2,3 In cell H5 we have =calc_amount(G5) In cell G6 we have =calc_string(G5,H5,TRUNC(J5,0)) In cell H6 we have =calc_amount(G6) And so this progression goes on for a 1000+ rows. Now, this spreadsheet has worked perfectly for years but now intermittently it locks up and returns #REF in all cells and sometimes XL has to close. I’ve crashed around but can’t find any other relevant cells. I can’t even find a reference to the =calc function Neither does the Error tracking facility in XL shed any light. Anyone any ideas? :confused:	B.M. (505)
610133	2007-11-11 05:15:00	This spreadsheet was in Excel?? Or some other spreadhseet program first? Because there isn't such a command in Excel.	pctek (84)
610134	2007-11-11 05:29:00	The spreadsheet has always been in XL and still is. The only thing I've done is upgrade from XL as part of Office 2000 to XL as part of Office XP Pro. It still works fine until you recalculate the whole sheet. Then it's all #REF except for cell G5 that just has 1,2,3 typed in it. Beats me how he's done it as I can't find an =calc???????? function either. There has to be something hidden somewhere but where????? :D Paging Graham & Andrew. :D :D	B.M. (505)
610135	2007-11-11 07:08:00	Not sure if it's anything to do with it, but Excel recalculates formulae when opening from an older version of Excel. Try opening it back in XL 2K and see if it works.	jwil1 (65)
610136	2007-11-11 16:44:00	Not sure if it's anything to do with it, but Excel recalculates formulae when opening from an older version of Excel. Try opening it back in XL 2K and see if it works. Good idea, I'll hook on my old HDD that I copied everything from initially and see what happens. After lying awake all night pondering how he’s done this and leaving more hair around the house than the cat leaves fur, I’ve had a thought!!!! Can you add functions that you’ve dreamt up yourself to the XL function library? This is the only way I can see that the =calc_string & =calc_amount could be used given that they don’t appear to be a generic function of XL. :confused:	B.M. (505)
610137	2007-11-11 23:35:00	Good idea, I'll hook on my old HDD that I copied everything from initially and see what happens. Can you add functions that you’ve dreamt up yourself to the XL function library? : Yes you can: office.microsoft.com	beeswax34 (63)
610138	2007-11-11 23:45:00	The calc commands look suspiciously like old Lotus 1-2-3 commands sarel	sarel (2490)
610139	2007-11-12 01:37:00	Yes you can: . microsoft . com/en-us/excel/HA010548461033 . aspx" target="_blank">office . microsoft . com Ahhhhhhh, Beeswax, I'll bet that's what he's done . I'll follow that path and see how long it is before I get completely lost . :D No sarel, always been XL and still is . I'll bet I've mucked up the VBA/Macro somewhere . :blush:	B.M. (505)
610140	2007-11-12 07:45:00	Hi BM They sound like a custom functions (user defined functions aka 'UDF') written using VBA. Open the spreadsheet, press the Alt+F11 keys to open the VBA window (if that doesn't work then right click the tab name and select 'View Code'). Once you are in the VBA window, expand the folder on the left that is called 'Modules' - in there you may see 1 or more modules (e.g. Module1, Module 2 etc). Double click each one to view the code within each module (the screen on the right will change) until you find the functions that are called calc_amount, calc_string and/or calc_amount. These are the custom functions that you have been using. As to why they are returning a #Ref error - I suspect they will do that if the cell the function refers to contains an error and/or there is a problem with the UDF. Have you enabled macros when you open the spreadsheet? If you aren't seeing the macro warning when opening the spreadsheet has the security been set to high? If so, change it back to 'Medium' under Tools > Options > Security > Macro Security. If they still don't work can you post the code for one of the modules that isn't working? And the value you are trying to pass into that function? Andrew	andrew93 (249)
610141	2007-11-12 17:59:00	Thanks Andrew, Well I couldn’t find anything using the VBA editor . Nothing, but I did find quite by accident another sheet of the workbook called Macro’s . :D Now, on here we have the following: calc_amount =ARGUMENT("string") =CALL("c:/xlstake/xlstake","calc_amount","JC", string) =RETURN(A3) calc_string =ARGUMENT("string") =ARGUMENT("bet") =ARGUMENT("dividend") =CALL("c:/xlstake/xlstake","calc_string","FFJJ", string, bet, dividend) =RETURN(A10) calc_length =ARGUMENT("string") =CALL("c:/xlstake/xlstake","calc_length","JC", string) =RETURN(A15) get_value =ARGUMENT("string") =ARGUMENT("index") =CALL("c:/xlstake/xlstake","get_value","JCJ", string, index) =RETURN(A21) pop_front =ARGUMENT("string") =ARGUMENT("number") =CALL("c:/xlstake/xlstake","pop_front","FFJ", string, number) =RETURN(A27) pop_back =ARGUMENT("string") =ARGUMENT("number") =CALL("c:/xlstake/xlstake","pop_back","FFJ", string, number) =RETURN(A33) push_back =ARGUMENT("string") =ARGUMENT("value") =CALL("c:/xlstake/xlstake","push_back","FFJ", string, value) =RETURN(A39) Which believe it or not is all gobbledegook to me . :blush: Quite a challenge this one isn't it? Cheers Bob	B.M. (505)
1 2