|
ATPVBAEN Problems
I sometimes use some functions that require the analysis toolpack, the basic
EXCEL one, not the VBA one. A typical function is eomonth(date, # of months), which finds the ending date of the month that is so maybe months later. I notice that, when I save a file with this function, though there is no message asking me to update links, when I re-open this file, there is a link when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was this way before. I occasionally get messages suggesting the add-ins are not installed even though clearly they are, since the equation is still there and changes when I change the date it is computing from. In any event, I also sent such a file to someone else to use and he ended up losing all cells that had this eomonth function in them - they all show up as =#N/A, though the right answer is still there, probably because he did not update links, or maybe because he did. In any event, he loses the equations. I even notice that when I open this file, sometimes, I see the formula change so that, in the formula bar, it shows something like atpvbaen.xls//eomonth(x,y), which seems the cause of the problem. My first question is whether this link to ATPVBAEN.xla should show up as a link, under edit links - can someone else try it on their computer - just type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell me if a link to atpvbaen.xla shows up for them (assuming you have the analysis toolpak installed)? Also, how can I fix things so that, when I send the file to someone else, he won't have the problem with his computer not being able to find this link? Can he just change the source, assuming he can find his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL 2007, which may not deal with add-ins in the same way. Help, please! Thanks Dean |
ATPVBAEN Problems
Dean,
No, I see no EditLink using this function. By the way, if this is the only function you use, you can roll your own quite easily, then drop the dependence on this add-in: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function NickHK "Dean" wrote in message ... I sometimes use some functions that require the analysis toolpack, the basic EXCEL one, not the VBA one. A typical function is eomonth(date, # of months), which finds the ending date of the month that is so maybe months later. I notice that, when I save a file with this function, though there is no message asking me to update links, when I re-open this file, there is a link when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was this way before. I occasionally get messages suggesting the add-ins are not installed even though clearly they are, since the equation is still there and changes when I change the date it is computing from. In any event, I also sent such a file to someone else to use and he ended up losing all cells that had this eomonth function in them - they all show up as =#N/A, though the right answer is still there, probably because he did not update links, or maybe because he did. In any event, he loses the equations. I even notice that when I open this file, sometimes, I see the formula change so that, in the formula bar, it shows something like atpvbaen.xls//eomonth(x,y), which seems the cause of the problem. My first question is whether this link to ATPVBAEN.xla should show up as a link, under edit links - can someone else try it on their computer - just type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell me if a link to atpvbaen.xla shows up for them (assuming you have the analysis toolpak installed)? Also, how can I fix things so that, when I send the file to someone else, he won't have the problem with his computer not being able to find this link? Can he just change the source, assuming he can find his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL 2007, which may not deal with add-ins in the same way. Help, please! Thanks Dean |
ATPVBAEN Problems
Does your home grown function work for every date, leap year or not?
Just to be sure, are you saying that, if you go to a blank spreadsheet and type in an eomonth function into some cell, then go to edit links, there is no link? Do you have the analysis toolpak FOR VBA installed also, or just the regular analysis toolpak? If just the regular one, then I might have some clues. I did some googling and found a post by our own Tom Ogilvy on some other forum which told me something I didn't know, that atpvbaen is really the add in for the VBA analysis toolpak, not the regular toolpack. So. I unchecked the VBA version in my EXCEL and then noticed that the eomonth function died, however if I retyped it in, it worked, and there was no edit link. However, as soon as I added the VBA add-in back in and typed a new eomomth function in another cell, I got the edit link back again - just for that one new eomonth function, not the others. So it seems that, at least on my computer, if I use the eomonth function, it looks to find it first in the analysis toolpak VBA, not just the regular analysis toolpak. I'm not sure I really need the analysis toolpak VBA, but it seems like it might be of some use! Does anyone have any idea how to tell EXCEL to look at the regular analysis toolpak first, or does the VBA one supersede the regular one, making this edit link unavoidable? The link seems to be problematic in that, if you send a file to someone else, it wants to update that atpvbaen link, which they may not even have. Thanks much, Nick! Dean "NickHK" wrote in message ... Dean, No, I see no EditLink using this function. By the way, if this is the only function you use, you can roll your own quite easily, then drop the dependence on this add-in: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function NickHK "Dean" wrote in message ... I sometimes use some functions that require the analysis toolpack, the basic EXCEL one, not the VBA one. A typical function is eomonth(date, # of months), which finds the ending date of the month that is so maybe months later. I notice that, when I save a file with this function, though there is no message asking me to update links, when I re-open this file, there is a link when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was this way before. I occasionally get messages suggesting the add-ins are not installed even though clearly they are, since the equation is still there and changes when I change the date it is computing from. In any event, I also sent such a file to someone else to use and he ended up losing all cells that had this eomonth function in them - they all show up as =#N/A, though the right answer is still there, probably because he did not update links, or maybe because he did. In any event, he loses the equations. I even notice that when I open this file, sometimes, I see the formula change so that, in the formula bar, it shows something like atpvbaen.xls//eomonth(x,y), which seems the cause of the problem. My first question is whether this link to ATPVBAEN.xla should show up as a link, under edit links - can someone else try it on their computer - just type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell me if a link to atpvbaen.xla shows up for them (assuming you have the analysis toolpak installed)? Also, how can I fix things so that, when I send the file to someone else, he won't have the problem with his computer not being able to find this link? Can he just change the source, assuming he can find his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL 2007, which may not deal with add-ins in the same way. Help, please! Thanks Dean |
ATPVBAEN Problems
Dean,
As it is using VBA's date calculation engine, rather than my own, it will return the correct date (apart from the built-in Lotus compatibility leap year/non leap year). Basically, all it is goes to the first of the month after the one we want, then comes back 1 day. Hence the last day of the month we want. As for all the add-in stuff, I tend to avoid them, unless they provide something that is absolutely essential. From your description, I cannot reproduce these problem by installing/uninstalling either addin, in XL2002. NickHK "Dean" wrote in message ... Does your home grown function work for every date, leap year or not? Just to be sure, are you saying that, if you go to a blank spreadsheet and type in an eomonth function into some cell, then go to edit links, there is no link? Do you have the analysis toolpak FOR VBA installed also, or just the regular analysis toolpak? If just the regular one, then I might have some clues. I did some googling and found a post by our own Tom Ogilvy on some other forum which told me something I didn't know, that atpvbaen is really the add in for the VBA analysis toolpak, not the regular toolpack. So. I unchecked the VBA version in my EXCEL and then noticed that the eomonth function died, however if I retyped it in, it worked, and there was no edit link. However, as soon as I added the VBA add-in back in and typed a new eomomth function in another cell, I got the edit link back again - just for that one new eomonth function, not the others. So it seems that, at least on my computer, if I use the eomonth function, it looks to find it first in the analysis toolpak VBA, not just the regular analysis toolpak. I'm not sure I really need the analysis toolpak VBA, but it seems like it might be of some use! Does anyone have any idea how to tell EXCEL to look at the regular analysis toolpak first, or does the VBA one supersede the regular one, making this edit link unavoidable? The link seems to be problematic in that, if you send a file to someone else, it wants to update that atpvbaen link, which they may not even have. Thanks much, Nick! Dean "NickHK" wrote in message ... Dean, No, I see no EditLink using this function. By the way, if this is the only function you use, you can roll your own quite easily, then drop the dependence on this add-in: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function NickHK "Dean" wrote in message ... I sometimes use some functions that require the analysis toolpack, the basic EXCEL one, not the VBA one. A typical function is eomonth(date, # of months), which finds the ending date of the month that is so maybe months later. I notice that, when I save a file with this function, though there is no message asking me to update links, when I re-open this file, there is a link when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was this way before. I occasionally get messages suggesting the add-ins are not installed even though clearly they are, since the equation is still there and changes when I change the date it is computing from. In any event, I also sent such a file to someone else to use and he ended up losing all cells that had this eomonth function in them - they all show up as =#N/A, though the right answer is still there, probably because he did not update links, or maybe because he did. In any event, he loses the equations. I even notice that when I open this file, sometimes, I see the formula change so that, in the formula bar, it shows something like atpvbaen.xls//eomonth(x,y), which seems the cause of the problem. My first question is whether this link to ATPVBAEN.xla should show up as a link, under edit links - can someone else try it on their computer - just type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell me if a link to atpvbaen.xla shows up for them (assuming you have the analysis toolpak installed)? Also, how can I fix things so that, when I send the file to someone else, he won't have the problem with his computer not being able to find this link? Can he just change the source, assuming he can find his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL 2007, which may not deal with add-ins in the same way. Help, please! Thanks Dean |
ATPVBAEN Problems
Wow, my memory is really bad. I had this problem only about 6 months ago
and, for some reasons, posted on yet another forum, answered by Dave Peterson: http://www.officehelp.in/1263734/atpvbaen-link In that post, I even recalled having the problem even before that! Nevertheless, Dave's suggestions did not help the problem, although he did suggest that unless you use these add-in functions in your VBA code, which I don't plan to, you don't really need this atpvbaen at all. So, it seems to me that Dave would advise me that the best thing for me to do is to simply uncheck the analysis toolpack for VBA. I may have to redo hundreds of eomonth functions but, once done, it probably won't get confused again by eomonth. Then, when I send the file somewhere, they probably won't see the link either, as long as they either don't have it checked or, even if they do have it checked, as long as they don't add any NEW eomonth functions. Anyone care to agree, or disagree? What I'd really like to know if why atpvbaen insists on stealing away the eomonth function that apparently, is already in funcres.xls. If it is really just for VBA, how does it even find it? Dean "Dean" wrote in message ... Does your home grown function work for every date, leap year or not? Just to be sure, are you saying that, if you go to a blank spreadsheet and type in an eomonth function into some cell, then go to edit links, there is no link? Do you have the analysis toolpak FOR VBA installed also, or just the regular analysis toolpak? If just the regular one, then I might have some clues. I did some googling and found a post by our own Tom Ogilvy on some other forum which told me something I didn't know, that atpvbaen is really the add in for the VBA analysis toolpak, not the regular toolpack. So. I unchecked the VBA version in my EXCEL and then noticed that the eomonth function died, however if I retyped it in, it worked, and there was no edit link. However, as soon as I added the VBA add-in back in and typed a new eomomth function in another cell, I got the edit link back again - just for that one new eomonth function, not the others. So it seems that, at least on my computer, if I use the eomonth function, it looks to find it first in the analysis toolpak VBA, not just the regular analysis toolpak. I'm not sure I really need the analysis toolpak VBA, but it seems like it might be of some use! Does anyone have any idea how to tell EXCEL to look at the regular analysis toolpak first, or does the VBA one supersede the regular one, making this edit link unavoidable? The link seems to be problematic in that, if you send a file to someone else, it wants to update that atpvbaen link, which they may not even have. Thanks much, Nick! Dean "NickHK" wrote in message ... Dean, No, I see no EditLink using this function. By the way, if this is the only function you use, you can roll your own quite easily, then drop the dependence on this add-in: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function NickHK "Dean" wrote in message ... I sometimes use some functions that require the analysis toolpack, the basic EXCEL one, not the VBA one. A typical function is eomonth(date, # of months), which finds the ending date of the month that is so maybe months later. I notice that, when I save a file with this function, though there is no message asking me to update links, when I re-open this file, there is a link when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was this way before. I occasionally get messages suggesting the add-ins are not installed even though clearly they are, since the equation is still there and changes when I change the date it is computing from. In any event, I also sent such a file to someone else to use and he ended up losing all cells that had this eomonth function in them - they all show up as =#N/A, though the right answer is still there, probably because he did not update links, or maybe because he did. In any event, he loses the equations. I even notice that when I open this file, sometimes, I see the formula change so that, in the formula bar, it shows something like atpvbaen.xls//eomonth(x,y), which seems the cause of the problem. My first question is whether this link to ATPVBAEN.xla should show up as a link, under edit links - can someone else try it on their computer - just type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell me if a link to atpvbaen.xla shows up for them (assuming you have the analysis toolpak installed)? Also, how can I fix things so that, when I send the file to someone else, he won't have the problem with his computer not being able to find this link? Can he just change the source, assuming he can find his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL 2007, which may not deal with add-ins in the same way. Help, please! Thanks Dean |
ATPVBAEN Problems
Thanks Nick. I use too many of these add-ins to be willing to do away with
them, though I think I will uncheck the second Analysis Toolpak option, the one for VBA, as ATPVBAEN seems to be the source of the problem, at least when using the eomonth function. I also have EXCEL 2002 and, after now having unchecked, then rechecked the box a few times to make sure the cause and effect were still there, I too can no longer reproduce my symptom either, though it was definitely there earlier - it would add the link for any new eomonth functions I typed in, with the check box checked! I'd attribute it to a going south installation of EXCEL on my machine but this also happened to a colleague to whom I sent a raw file that did not have the edit link initially either (he said he did have EXCEL 2007, which may deal with add-ins a little bit differently), so it isn't just my version of EXCEL or my computer. Very strange. I hope that someone can explain all this. Thanks again, Nick. Dean "NickHK" wrote in message ... Dean, As it is using VBA's date calculation engine, rather than my own, it will return the correct date (apart from the built-in Lotus compatibility leap year/non leap year). Basically, all it is goes to the first of the month after the one we want, then comes back 1 day. Hence the last day of the month we want. As for all the add-in stuff, I tend to avoid them, unless they provide something that is absolutely essential. From your description, I cannot reproduce these problem by installing/uninstalling either addin, in XL2002. NickHK "Dean" wrote in message ... Does your home grown function work for every date, leap year or not? Just to be sure, are you saying that, if you go to a blank spreadsheet and type in an eomonth function into some cell, then go to edit links, there is no link? Do you have the analysis toolpak FOR VBA installed also, or just the regular analysis toolpak? If just the regular one, then I might have some clues. I did some googling and found a post by our own Tom Ogilvy on some other forum which told me something I didn't know, that atpvbaen is really the add in for the VBA analysis toolpak, not the regular toolpack. So. I unchecked the VBA version in my EXCEL and then noticed that the eomonth function died, however if I retyped it in, it worked, and there was no edit link. However, as soon as I added the VBA add-in back in and typed a new eomomth function in another cell, I got the edit link back again - just for that one new eomonth function, not the others. So it seems that, at least on my computer, if I use the eomonth function, it looks to find it first in the analysis toolpak VBA, not just the regular analysis toolpak. I'm not sure I really need the analysis toolpak VBA, but it seems like it might be of some use! Does anyone have any idea how to tell EXCEL to look at the regular analysis toolpak first, or does the VBA one supersede the regular one, making this edit link unavoidable? The link seems to be problematic in that, if you send a file to someone else, it wants to update that atpvbaen link, which they may not even have. Thanks much, Nick! Dean "NickHK" wrote in message ... Dean, No, I see no EditLink using this function. By the way, if this is the only function you use, you can roll your own quite easily, then drop the dependence on this add-in: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function NickHK "Dean" wrote in message ... I sometimes use some functions that require the analysis toolpack, the basic EXCEL one, not the VBA one. A typical function is eomonth(date, # of months), which finds the ending date of the month that is so maybe months later. I notice that, when I save a file with this function, though there is no message asking me to update links, when I re-open this file, there is a link when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was this way before. I occasionally get messages suggesting the add-ins are not installed even though clearly they are, since the equation is still there and changes when I change the date it is computing from. In any event, I also sent such a file to someone else to use and he ended up losing all cells that had this eomonth function in them - they all show up as =#N/A, though the right answer is still there, probably because he did not update links, or maybe because he did. In any event, he loses the equations. I even notice that when I open this file, sometimes, I see the formula change so that, in the formula bar, it shows something like atpvbaen.xls//eomonth(x,y), which seems the cause of the problem. My first question is whether this link to ATPVBAEN.xla should show up as a link, under edit links - can someone else try it on their computer - just type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell me if a link to atpvbaen.xla shows up for them (assuming you have the analysis toolpak installed)? Also, how can I fix things so that, when I send the file to someone else, he won't have the problem with his computer not being able to find this link? Can he just change the source, assuming he can find his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL 2007, which may not deal with add-ins in the same way. Help, please! Thanks Dean |
ATPVBAEN Problems
I'm not sure what you are saying about Lotus. I'm using EXCEL - does is not
work for leap years in EXCEL? I forget where to put such a function - is it on the "This Workbook" page? "NickHK" wrote in message ... Dean, As it is using VBA's date calculation engine, rather than my own, it will return the correct date (apart from the built-in Lotus compatibility leap year/non leap year). Basically, all it is goes to the first of the month after the one we want, then comes back 1 day. Hence the last day of the month we want. As for all the add-in stuff, I tend to avoid them, unless they provide something that is absolutely essential. From your description, I cannot reproduce these problem by installing/uninstalling either addin, in XL2002. NickHK "Dean" wrote in message ... Does your home grown function work for every date, leap year or not? Just to be sure, are you saying that, if you go to a blank spreadsheet and type in an eomonth function into some cell, then go to edit links, there is no link? Do you have the analysis toolpak FOR VBA installed also, or just the regular analysis toolpak? If just the regular one, then I might have some clues. I did some googling and found a post by our own Tom Ogilvy on some other forum which told me something I didn't know, that atpvbaen is really the add in for the VBA analysis toolpak, not the regular toolpack. So. I unchecked the VBA version in my EXCEL and then noticed that the eomonth function died, however if I retyped it in, it worked, and there was no edit link. However, as soon as I added the VBA add-in back in and typed a new eomomth function in another cell, I got the edit link back again - just for that one new eomonth function, not the others. So it seems that, at least on my computer, if I use the eomonth function, it looks to find it first in the analysis toolpak VBA, not just the regular analysis toolpak. I'm not sure I really need the analysis toolpak VBA, but it seems like it might be of some use! Does anyone have any idea how to tell EXCEL to look at the regular analysis toolpak first, or does the VBA one supersede the regular one, making this edit link unavoidable? The link seems to be problematic in that, if you send a file to someone else, it wants to update that atpvbaen link, which they may not even have. Thanks much, Nick! Dean "NickHK" wrote in message ... Dean, No, I see no EditLink using this function. By the way, if this is the only function you use, you can roll your own quite easily, then drop the dependence on this add-in: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function NickHK "Dean" wrote in message ... I sometimes use some functions that require the analysis toolpack, the basic EXCEL one, not the VBA one. A typical function is eomonth(date, # of months), which finds the ending date of the month that is so maybe months later. I notice that, when I save a file with this function, though there is no message asking me to update links, when I re-open this file, there is a link when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was this way before. I occasionally get messages suggesting the add-ins are not installed even though clearly they are, since the equation is still there and changes when I change the date it is computing from. In any event, I also sent such a file to someone else to use and he ended up losing all cells that had this eomonth function in them - they all show up as =#N/A, though the right answer is still there, probably because he did not update links, or maybe because he did. In any event, he loses the equations. I even notice that when I open this file, sometimes, I see the formula change so that, in the formula bar, it shows something like atpvbaen.xls//eomonth(x,y), which seems the cause of the problem. My first question is whether this link to ATPVBAEN.xla should show up as a link, under edit links - can someone else try it on their computer - just type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell me if a link to atpvbaen.xla shows up for them (assuming you have the analysis toolpak installed)? Also, how can I fix things so that, when I send the file to someone else, he won't have the problem with his computer not being able to find this link? Can he just change the source, assuming he can find his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL 2007, which may not deal with add-ins in the same way. Help, please! Thanks Dean |
ATPVBAEN Problems
Dean,
Make it a Public function in a standard module, then you can call it as a worksheet function. As for the Lotus aspect, MS continued a bug that Lotus introduced in calculating one Leap year (1900 ?). Whilst this is probably not a problem for you, you did ask <g. NickHK "Dean" wrote in message ... I'm not sure what you are saying about Lotus. I'm using EXCEL - does is not work for leap years in EXCEL? I forget where to put such a function - is it on the "This Workbook" page? "NickHK" wrote in message ... Dean, As it is using VBA's date calculation engine, rather than my own, it will return the correct date (apart from the built-in Lotus compatibility leap year/non leap year). Basically, all it is goes to the first of the month after the one we want, then comes back 1 day. Hence the last day of the month we want. As for all the add-in stuff, I tend to avoid them, unless they provide something that is absolutely essential. From your description, I cannot reproduce these problem by installing/uninstalling either addin, in XL2002. NickHK "Dean" wrote in message ... Does your home grown function work for every date, leap year or not? Just to be sure, are you saying that, if you go to a blank spreadsheet and type in an eomonth function into some cell, then go to edit links, there is no link? Do you have the analysis toolpak FOR VBA installed also, or just the regular analysis toolpak? If just the regular one, then I might have some clues. I did some googling and found a post by our own Tom Ogilvy on some other forum which told me something I didn't know, that atpvbaen is really the add in for the VBA analysis toolpak, not the regular toolpack. So. I unchecked the VBA version in my EXCEL and then noticed that the eomonth function died, however if I retyped it in, it worked, and there was no edit link. However, as soon as I added the VBA add-in back in and typed a new eomomth function in another cell, I got the edit link back again - just for that one new eomonth function, not the others. So it seems that, at least on my computer, if I use the eomonth function, it looks to find it first in the analysis toolpak VBA, not just the regular analysis toolpak. I'm not sure I really need the analysis toolpak VBA, but it seems like it might be of some use! Does anyone have any idea how to tell EXCEL to look at the regular analysis toolpak first, or does the VBA one supersede the regular one, making this edit link unavoidable? The link seems to be problematic in that, if you send a file to someone else, it wants to update that atpvbaen link, which they may not even have. Thanks much, Nick! Dean "NickHK" wrote in message ... Dean, No, I see no EditLink using this function. By the way, if this is the only function you use, you can roll your own quite easily, then drop the dependence on this add-in: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function NickHK "Dean" wrote in message ... I sometimes use some functions that require the analysis toolpack, the basic EXCEL one, not the VBA one. A typical function is eomonth(date, # of months), which finds the ending date of the month that is so maybe months later. I notice that, when I save a file with this function, though there is no message asking me to update links, when I re-open this file, there is a link when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was this way before. I occasionally get messages suggesting the add-ins are not installed even though clearly they are, since the equation is still there and changes when I change the date it is computing from. In any event, I also sent such a file to someone else to use and he ended up losing all cells that had this eomonth function in them - they all show up as =#N/A, though the right answer is still there, probably because he did not update links, or maybe because he did. In any event, he loses the equations. I even notice that when I open this file, sometimes, I see the formula change so that, in the formula bar, it shows something like atpvbaen.xls//eomonth(x,y), which seems the cause of the problem. My first question is whether this link to ATPVBAEN.xla should show up as a link, under edit links - can someone else try it on their computer - just type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell me if a link to atpvbaen.xla shows up for them (assuming you have the analysis toolpak installed)? Also, how can I fix things so that, when I send the file to someone else, he won't have the problem with his computer not being able to find this link? Can he just change the source, assuming he can find his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL 2007, which may not deal with add-ins in the same way. Help, please! Thanks Dean |
ATPVBAEN Problems
The same person who was having problems with EXCEL's eomonth functions
disappearing from the file I sent him (not immediately, but eventually), is having problems with your function too (returns #NAME?), when I send him a simple file that works for me. He has EXCEL 2007, but should that matter? Both of us do NOT have Analysis ToolPak - VBA installed now. Your function works for me, but apparently not for him when he receives the file and opens it on his computer. Does this make any sense? Dean "NickHK" wrote in message ... Dean, Make it a Public function in a standard module, then you can call it as a worksheet function. As for the Lotus aspect, MS continued a bug that Lotus introduced in calculating one Leap year (1900 ?). Whilst this is probably not a problem for you, you did ask <g. NickHK "Dean" wrote in message ... I'm not sure what you are saying about Lotus. I'm using EXCEL - does is not work for leap years in EXCEL? I forget where to put such a function - is it on the "This Workbook" page? "NickHK" wrote in message ... Dean, As it is using VBA's date calculation engine, rather than my own, it will return the correct date (apart from the built-in Lotus compatibility leap year/non leap year). Basically, all it is goes to the first of the month after the one we want, then comes back 1 day. Hence the last day of the month we want. As for all the add-in stuff, I tend to avoid them, unless they provide something that is absolutely essential. From your description, I cannot reproduce these problem by installing/uninstalling either addin, in XL2002. NickHK "Dean" wrote in message ... Does your home grown function work for every date, leap year or not? Just to be sure, are you saying that, if you go to a blank spreadsheet and type in an eomonth function into some cell, then go to edit links, there is no link? Do you have the analysis toolpak FOR VBA installed also, or just the regular analysis toolpak? If just the regular one, then I might have some clues. I did some googling and found a post by our own Tom Ogilvy on some other forum which told me something I didn't know, that atpvbaen is really the add in for the VBA analysis toolpak, not the regular toolpack. So. I unchecked the VBA version in my EXCEL and then noticed that the eomonth function died, however if I retyped it in, it worked, and there was no edit link. However, as soon as I added the VBA add-in back in and typed a new eomomth function in another cell, I got the edit link back again - just for that one new eomonth function, not the others. So it seems that, at least on my computer, if I use the eomonth function, it looks to find it first in the analysis toolpak VBA, not just the regular analysis toolpak. I'm not sure I really need the analysis toolpak VBA, but it seems like it might be of some use! Does anyone have any idea how to tell EXCEL to look at the regular analysis toolpak first, or does the VBA one supersede the regular one, making this edit link unavoidable? The link seems to be problematic in that, if you send a file to someone else, it wants to update that atpvbaen link, which they may not even have. Thanks much, Nick! Dean "NickHK" wrote in message ... Dean, No, I see no EditLink using this function. By the way, if this is the only function you use, you can roll your own quite easily, then drop the dependence on this add-in: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function NickHK "Dean" wrote in message ... I sometimes use some functions that require the analysis toolpack, the basic EXCEL one, not the VBA one. A typical function is eomonth(date, # of months), which finds the ending date of the month that is so maybe months later. I notice that, when I save a file with this function, though there is no message asking me to update links, when I re-open this file, there is a link when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was this way before. I occasionally get messages suggesting the add-ins are not installed even though clearly they are, since the equation is still there and changes when I change the date it is computing from. In any event, I also sent such a file to someone else to use and he ended up losing all cells that had this eomonth function in them - they all show up as =#N/A, though the right answer is still there, probably because he did not update links, or maybe because he did. In any event, he loses the equations. I even notice that when I open this file, sometimes, I see the formula change so that, in the formula bar, it shows something like atpvbaen.xls//eomonth(x,y), which seems the cause of the problem. My first question is whether this link to ATPVBAEN.xla should show up as a link, under edit links - can someone else try it on their computer - just type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell me if a link to atpvbaen.xla shows up for them (assuming you have the analysis toolpak installed)? Also, how can I fix things so that, when I send the file to someone else, he won't have the problem with his computer not being able to find this link? Can he just change the source, assuming he can find his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL 2007, which may not deal with add-ins in the same way. Help, please! Thanks Dean |
ATPVBAEN Problems
Actually, he is now telling me he has his macro security setting at high.
This would explain the problem with the homegrown function, right? But not the problem with EXCEL's function, I think. Do you agree? "Dean" wrote in message ... The same person who was having problems with EXCEL's eomonth functions disappearing from the file I sent him (not immediately, but eventually), is having problems with your function too (returns #NAME?), when I send him a simple file that works for me. He has EXCEL 2007, but should that matter? Both of us do NOT have Analysis ToolPak - VBA installed now. Your function works for me, but apparently not for him when he receives the file and opens it on his computer. Does this make any sense? Dean "NickHK" wrote in message ... Dean, Make it a Public function in a standard module, then you can call it as a worksheet function. As for the Lotus aspect, MS continued a bug that Lotus introduced in calculating one Leap year (1900 ?). Whilst this is probably not a problem for you, you did ask <g. NickHK "Dean" wrote in message ... I'm not sure what you are saying about Lotus. I'm using EXCEL - does is not work for leap years in EXCEL? I forget where to put such a function - is it on the "This Workbook" page? "NickHK" wrote in message ... Dean, As it is using VBA's date calculation engine, rather than my own, it will return the correct date (apart from the built-in Lotus compatibility leap year/non leap year). Basically, all it is goes to the first of the month after the one we want, then comes back 1 day. Hence the last day of the month we want. As for all the add-in stuff, I tend to avoid them, unless they provide something that is absolutely essential. From your description, I cannot reproduce these problem by installing/uninstalling either addin, in XL2002. NickHK "Dean" wrote in message ... Does your home grown function work for every date, leap year or not? Just to be sure, are you saying that, if you go to a blank spreadsheet and type in an eomonth function into some cell, then go to edit links, there is no link? Do you have the analysis toolpak FOR VBA installed also, or just the regular analysis toolpak? If just the regular one, then I might have some clues. I did some googling and found a post by our own Tom Ogilvy on some other forum which told me something I didn't know, that atpvbaen is really the add in for the VBA analysis toolpak, not the regular toolpack. So. I unchecked the VBA version in my EXCEL and then noticed that the eomonth function died, however if I retyped it in, it worked, and there was no edit link. However, as soon as I added the VBA add-in back in and typed a new eomomth function in another cell, I got the edit link back again - just for that one new eomonth function, not the others. So it seems that, at least on my computer, if I use the eomonth function, it looks to find it first in the analysis toolpak VBA, not just the regular analysis toolpak. I'm not sure I really need the analysis toolpak VBA, but it seems like it might be of some use! Does anyone have any idea how to tell EXCEL to look at the regular analysis toolpak first, or does the VBA one supersede the regular one, making this edit link unavoidable? The link seems to be problematic in that, if you send a file to someone else, it wants to update that atpvbaen link, which they may not even have. Thanks much, Nick! Dean "NickHK" wrote in message ... Dean, No, I see no EditLink using this function. By the way, if this is the only function you use, you can roll your own quite easily, then drop the dependence on this add-in: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function NickHK "Dean" wrote in message ... I sometimes use some functions that require the analysis toolpack, the basic EXCEL one, not the VBA one. A typical function is eomonth(date, # of months), which finds the ending date of the month that is so maybe months later. I notice that, when I save a file with this function, though there is no message asking me to update links, when I re-open this file, there is a link when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was this way before. I occasionally get messages suggesting the add-ins are not installed even though clearly they are, since the equation is still there and changes when I change the date it is computing from. In any event, I also sent such a file to someone else to use and he ended up losing all cells that had this eomonth function in them - they all show up as =#N/A, though the right answer is still there, probably because he did not update links, or maybe because he did. In any event, he loses the equations. I even notice that when I open this file, sometimes, I see the formula change so that, in the formula bar, it shows something like atpvbaen.xls//eomonth(x,y), which seems the cause of the problem. My first question is whether this link to ATPVBAEN.xla should show up as a link, under edit links - can someone else try it on their computer - just type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell me if a link to atpvbaen.xla shows up for them (assuming you have the analysis toolpak installed)? Also, how can I fix things so that, when I send the file to someone else, he won't have the problem with his computer not being able to find this link? Can he just change the source, assuming he can find his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL 2007, which may not deal with add-ins in the same way. Help, please! Thanks Dean |
ATPVBAEN Problems
Dean,
With Security set to high (and unsigned/not trusted code), the code will not run. Reduced security to medium and click allow macros. NickHK "Dean" wrote in message ... Actually, he is now telling me he has his macro security setting at high. This would explain the problem with the homegrown function, right? But not the problem with EXCEL's function, I think. Do you agree? "Dean" wrote in message ... The same person who was having problems with EXCEL's eomonth functions disappearing from the file I sent him (not immediately, but eventually), is having problems with your function too (returns #NAME?), when I send him a simple file that works for me. He has EXCEL 2007, but should that matter? Both of us do NOT have Analysis ToolPak - VBA installed now. Your function works for me, but apparently not for him when he receives the file and opens it on his computer. Does this make any sense? Dean "NickHK" wrote in message ... Dean, Make it a Public function in a standard module, then you can call it as a worksheet function. As for the Lotus aspect, MS continued a bug that Lotus introduced in calculating one Leap year (1900 ?). Whilst this is probably not a problem for you, you did ask <g. NickHK "Dean" wrote in message ... I'm not sure what you are saying about Lotus. I'm using EXCEL - does is not work for leap years in EXCEL? I forget where to put such a function - is it on the "This Workbook" page? "NickHK" wrote in message ... Dean, As it is using VBA's date calculation engine, rather than my own, it will return the correct date (apart from the built-in Lotus compatibility leap year/non leap year). Basically, all it is goes to the first of the month after the one we want, then comes back 1 day. Hence the last day of the month we want. As for all the add-in stuff, I tend to avoid them, unless they provide something that is absolutely essential. From your description, I cannot reproduce these problem by installing/uninstalling either addin, in XL2002. NickHK "Dean" wrote in message ... Does your home grown function work for every date, leap year or not? Just to be sure, are you saying that, if you go to a blank spreadsheet and type in an eomonth function into some cell, then go to edit links, there is no link? Do you have the analysis toolpak FOR VBA installed also, or just the regular analysis toolpak? If just the regular one, then I might have some clues. I did some googling and found a post by our own Tom Ogilvy on some other forum which told me something I didn't know, that atpvbaen is really the add in for the VBA analysis toolpak, not the regular toolpack. So. I unchecked the VBA version in my EXCEL and then noticed that the eomonth function died, however if I retyped it in, it worked, and there was no edit link. However, as soon as I added the VBA add-in back in and typed a new eomomth function in another cell, I got the edit link back again - just for that one new eomonth function, not the others. So it seems that, at least on my computer, if I use the eomonth function, it looks to find it first in the analysis toolpak VBA, not just the regular analysis toolpak. I'm not sure I really need the analysis toolpak VBA, but it seems like it might be of some use! Does anyone have any idea how to tell EXCEL to look at the regular analysis toolpak first, or does the VBA one supersede the regular one, making this edit link unavoidable? The link seems to be problematic in that, if you send a file to someone else, it wants to update that atpvbaen link, which they may not even have. Thanks much, Nick! Dean "NickHK" wrote in message ... Dean, No, I see no EditLink using this function. By the way, if this is the only function you use, you can roll your own quite easily, then drop the dependence on this add-in: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function NickHK "Dean" wrote in message ... I sometimes use some functions that require the analysis toolpack, the basic EXCEL one, not the VBA one. A typical function is eomonth(date, # of months), which finds the ending date of the month that is so maybe months later. I notice that, when I save a file with this function, though there is no message asking me to update links, when I re-open this file, there is a link when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was this way before. I occasionally get messages suggesting the add-ins are not installed even though clearly they are, since the equation is still there and changes when I change the date it is computing from. In any event, I also sent such a file to someone else to use and he ended up losing all cells that had this eomonth function in them - they all show up as =#N/A, though the right answer is still there, probably because he did not update links, or maybe because he did. In any event, he loses the equations. I even notice that when I open this file, sometimes, I see the formula change so that, in the formula bar, it shows something like atpvbaen.xls//eomonth(x,y), which seems the cause of the problem. My first question is whether this link to ATPVBAEN.xla should show up as a link, under edit links - can someone else try it on their computer - just type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell me if a link to atpvbaen.xla shows up for them (assuming you have the analysis toolpak installed)? Also, how can I fix things so that, when I send the file to someone else, he won't have the problem with his computer not being able to find this link? Can he just change the source, assuming he can find his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL 2007, which may not deal with add-ins in the same way. Help, please! Thanks Dean |
ATPVBAEN Problems
Nick,
The guy has sent me back the file (the one that, eventually, mysteriously, wipes out all eomonth functions, leaving only N/A in their wakes) and I have attempted to use your myeomonth function in it, but all it returns is #NAME. I also have a very simple new file open that I created with your function in it and it works fine there. A clue may be that, in the file where it doesn't work, no matter how I type it in (lower case, upper case, etc), it changes it to: =myEOMONTH(O3,P3) that is, the first 2 letters are lower case and the next 7 letters are upper case. Seemingly, this is tacit recognition that it (the first file) does not comprehend the function. Yes, the function is in a macro sheet within both files; also macro security is set to low. What could explain this? Dean "NickHK" wrote in message ... Dean, Make it a Public function in a standard module, then you can call it as a worksheet function. As for the Lotus aspect, MS continued a bug that Lotus introduced in calculating one Leap year (1900 ?). Whilst this is probably not a problem for you, you did ask <g. NickHK "Dean" wrote in message ... I'm not sure what you are saying about Lotus. I'm using EXCEL - does is not work for leap years in EXCEL? I forget where to put such a function - is it on the "This Workbook" page? "NickHK" wrote in message ... Dean, As it is using VBA's date calculation engine, rather than my own, it will return the correct date (apart from the built-in Lotus compatibility leap year/non leap year). Basically, all it is goes to the first of the month after the one we want, then comes back 1 day. Hence the last day of the month we want. As for all the add-in stuff, I tend to avoid them, unless they provide something that is absolutely essential. From your description, I cannot reproduce these problem by installing/uninstalling either addin, in XL2002. NickHK "Dean" wrote in message ... Does your home grown function work for every date, leap year or not? Just to be sure, are you saying that, if you go to a blank spreadsheet and type in an eomonth function into some cell, then go to edit links, there is no link? Do you have the analysis toolpak FOR VBA installed also, or just the regular analysis toolpak? If just the regular one, then I might have some clues. I did some googling and found a post by our own Tom Ogilvy on some other forum which told me something I didn't know, that atpvbaen is really the add in for the VBA analysis toolpak, not the regular toolpack. So. I unchecked the VBA version in my EXCEL and then noticed that the eomonth function died, however if I retyped it in, it worked, and there was no edit link. However, as soon as I added the VBA add-in back in and typed a new eomomth function in another cell, I got the edit link back again - just for that one new eomonth function, not the others. So it seems that, at least on my computer, if I use the eomonth function, it looks to find it first in the analysis toolpak VBA, not just the regular analysis toolpak. I'm not sure I really need the analysis toolpak VBA, but it seems like it might be of some use! Does anyone have any idea how to tell EXCEL to look at the regular analysis toolpak first, or does the VBA one supersede the regular one, making this edit link unavoidable? The link seems to be problematic in that, if you send a file to someone else, it wants to update that atpvbaen link, which they may not even have. Thanks much, Nick! Dean "NickHK" wrote in message ... Dean, No, I see no EditLink using this function. By the way, if this is the only function you use, you can roll your own quite easily, then drop the dependence on this add-in: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function NickHK "Dean" wrote in message ... I sometimes use some functions that require the analysis toolpack, the basic EXCEL one, not the VBA one. A typical function is eomonth(date, # of months), which finds the ending date of the month that is so maybe months later. I notice that, when I save a file with this function, though there is no message asking me to update links, when I re-open this file, there is a link when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was this way before. I occasionally get messages suggesting the add-ins are not installed even though clearly they are, since the equation is still there and changes when I change the date it is computing from. In any event, I also sent such a file to someone else to use and he ended up losing all cells that had this eomonth function in them - they all show up as =#N/A, though the right answer is still there, probably because he did not update links, or maybe because he did. In any event, he loses the equations. I even notice that when I open this file, sometimes, I see the formula change so that, in the formula bar, it shows something like atpvbaen.xls//eomonth(x,y), which seems the cause of the problem. My first question is whether this link to ATPVBAEN.xla should show up as a link, under edit links - can someone else try it on their computer - just type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell me if a link to atpvbaen.xla shows up for them (assuming you have the analysis toolpak installed)? Also, how can I fix things so that, when I send the file to someone else, he won't have the problem with his computer not being able to find this link? Can he just change the source, assuming he can find his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL 2007, which may not deal with add-ins in the same way. Help, please! Thanks Dean |
ATPVBAEN Problems
Oh, I forgot to answer this. That fixed it for him. Thanks, but see my
other post today, please, Nick. "NickHK" wrote in message ... Dean, With Security set to high (and unsigned/not trusted code), the code will not run. Reduced security to medium and click allow macros. NickHK "Dean" wrote in message ... Actually, he is now telling me he has his macro security setting at high. This would explain the problem with the homegrown function, right? But not the problem with EXCEL's function, I think. Do you agree? "Dean" wrote in message ... The same person who was having problems with EXCEL's eomonth functions disappearing from the file I sent him (not immediately, but eventually), is having problems with your function too (returns #NAME?), when I send him a simple file that works for me. He has EXCEL 2007, but should that matter? Both of us do NOT have Analysis ToolPak - VBA installed now. Your function works for me, but apparently not for him when he receives the file and opens it on his computer. Does this make any sense? Dean "NickHK" wrote in message ... Dean, Make it a Public function in a standard module, then you can call it as a worksheet function. As for the Lotus aspect, MS continued a bug that Lotus introduced in calculating one Leap year (1900 ?). Whilst this is probably not a problem for you, you did ask <g. NickHK "Dean" wrote in message ... I'm not sure what you are saying about Lotus. I'm using EXCEL - does is not work for leap years in EXCEL? I forget where to put such a function - is it on the "This Workbook" page? "NickHK" wrote in message ... Dean, As it is using VBA's date calculation engine, rather than my own, it will return the correct date (apart from the built-in Lotus compatibility leap year/non leap year). Basically, all it is goes to the first of the month after the one we want, then comes back 1 day. Hence the last day of the month we want. As for all the add-in stuff, I tend to avoid them, unless they provide something that is absolutely essential. From your description, I cannot reproduce these problem by installing/uninstalling either addin, in XL2002. NickHK "Dean" wrote in message ... Does your home grown function work for every date, leap year or not? Just to be sure, are you saying that, if you go to a blank spreadsheet and type in an eomonth function into some cell, then go to edit links, there is no link? Do you have the analysis toolpak FOR VBA installed also, or just the regular analysis toolpak? If just the regular one, then I might have some clues. I did some googling and found a post by our own Tom Ogilvy on some other forum which told me something I didn't know, that atpvbaen is really the add in for the VBA analysis toolpak, not the regular toolpack. So. I unchecked the VBA version in my EXCEL and then noticed that the eomonth function died, however if I retyped it in, it worked, and there was no edit link. However, as soon as I added the VBA add-in back in and typed a new eomomth function in another cell, I got the edit link back again - just for that one new eomonth function, not the others. So it seems that, at least on my computer, if I use the eomonth function, it looks to find it first in the analysis toolpak VBA, not just the regular analysis toolpak. I'm not sure I really need the analysis toolpak VBA, but it seems like it might be of some use! Does anyone have any idea how to tell EXCEL to look at the regular analysis toolpak first, or does the VBA one supersede the regular one, making this edit link unavoidable? The link seems to be problematic in that, if you send a file to someone else, it wants to update that atpvbaen link, which they may not even have. Thanks much, Nick! Dean "NickHK" wrote in message ... Dean, No, I see no EditLink using this function. By the way, if this is the only function you use, you can roll your own quite easily, then drop the dependence on this add-in: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function NickHK "Dean" wrote in message ... I sometimes use some functions that require the analysis toolpack, the basic EXCEL one, not the VBA one. A typical function is eomonth(date, # of months), which finds the ending date of the month that is so maybe months later. I notice that, when I save a file with this function, though there is no message asking me to update links, when I re-open this file, there is a link when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was this way before. I occasionally get messages suggesting the add-ins are not installed even though clearly they are, since the equation is still there and changes when I change the date it is computing from. In any event, I also sent such a file to someone else to use and he ended up losing all cells that had this eomonth function in them - they all show up as =#N/A, though the right answer is still there, probably because he did not update links, or maybe because he did. In any event, he loses the equations. I even notice that when I open this file, sometimes, I see the formula change so that, in the formula bar, it shows something like atpvbaen.xls//eomonth(x,y), which seems the cause of the problem. My first question is whether this link to ATPVBAEN.xla should show up as a link, under edit links - can someone else try it on their computer - just type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell me if a link to atpvbaen.xla shows up for them (assuming you have the analysis toolpak installed)? Also, how can I fix things so that, when I send the file to someone else, he won't have the problem with his computer not being able to find this link? Can he just change the source, assuming he can find his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL 2007, which may not deal with add-ins in the same way. Help, please! Thanks Dean |
ATPVBAEN Problems
One other thing. Though I don't think anyone thinks it would matter, his
file was one I created in EXCEL 2002 but which he altered somewhat and re-saved in EXCEL 2007. "Dean" wrote in message ... Nick, The guy has sent me back the file (the one that, eventually, mysteriously, wipes out all eomonth functions, leaving only N/A in their wakes) and I have attempted to use your myeomonth function in it, but all it returns is #NAME. I also have a very simple new file open that I created with your function in it and it works fine there. A clue may be that, in the file where it doesn't work, no matter how I type it in (lower case, upper case, etc), it changes it to: =myEOMONTH(O3,P3) that is, the first 2 letters are lower case and the next 7 letters are upper case. Seemingly, this is tacit recognition that it (the first file) does not comprehend the function. Yes, the function is in a macro sheet within both files; also macro security is set to low. What could explain this? Dean "NickHK" wrote in message ... Dean, Make it a Public function in a standard module, then you can call it as a worksheet function. As for the Lotus aspect, MS continued a bug that Lotus introduced in calculating one Leap year (1900 ?). Whilst this is probably not a problem for you, you did ask <g. NickHK "Dean" wrote in message ... I'm not sure what you are saying about Lotus. I'm using EXCEL - does is not work for leap years in EXCEL? I forget where to put such a function - is it on the "This Workbook" page? "NickHK" wrote in message ... Dean, As it is using VBA's date calculation engine, rather than my own, it will return the correct date (apart from the built-in Lotus compatibility leap year/non leap year). Basically, all it is goes to the first of the month after the one we want, then comes back 1 day. Hence the last day of the month we want. As for all the add-in stuff, I tend to avoid them, unless they provide something that is absolutely essential. From your description, I cannot reproduce these problem by installing/uninstalling either addin, in XL2002. NickHK "Dean" wrote in message ... Does your home grown function work for every date, leap year or not? Just to be sure, are you saying that, if you go to a blank spreadsheet and type in an eomonth function into some cell, then go to edit links, there is no link? Do you have the analysis toolpak FOR VBA installed also, or just the regular analysis toolpak? If just the regular one, then I might have some clues. I did some googling and found a post by our own Tom Ogilvy on some other forum which told me something I didn't know, that atpvbaen is really the add in for the VBA analysis toolpak, not the regular toolpack. So. I unchecked the VBA version in my EXCEL and then noticed that the eomonth function died, however if I retyped it in, it worked, and there was no edit link. However, as soon as I added the VBA add-in back in and typed a new eomomth function in another cell, I got the edit link back again - just for that one new eomonth function, not the others. So it seems that, at least on my computer, if I use the eomonth function, it looks to find it first in the analysis toolpak VBA, not just the regular analysis toolpak. I'm not sure I really need the analysis toolpak VBA, but it seems like it might be of some use! Does anyone have any idea how to tell EXCEL to look at the regular analysis toolpak first, or does the VBA one supersede the regular one, making this edit link unavoidable? The link seems to be problematic in that, if you send a file to someone else, it wants to update that atpvbaen link, which they may not even have. Thanks much, Nick! Dean "NickHK" wrote in message ... Dean, No, I see no EditLink using this function. By the way, if this is the only function you use, you can roll your own quite easily, then drop the dependence on this add-in: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function NickHK "Dean" wrote in message ... I sometimes use some functions that require the analysis toolpack, the basic EXCEL one, not the VBA one. A typical function is eomonth(date, # of months), which finds the ending date of the month that is so maybe months later. I notice that, when I save a file with this function, though there is no message asking me to update links, when I re-open this file, there is a link when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was this way before. I occasionally get messages suggesting the add-ins are not installed even though clearly they are, since the equation is still there and changes when I change the date it is computing from. In any event, I also sent such a file to someone else to use and he ended up losing all cells that had this eomonth function in them - they all show up as =#N/A, though the right answer is still there, probably because he did not update links, or maybe because he did. In any event, he loses the equations. I even notice that when I open this file, sometimes, I see the formula change so that, in the formula bar, it shows something like atpvbaen.xls//eomonth(x,y), which seems the cause of the problem. My first question is whether this link to ATPVBAEN.xla should show up as a link, under edit links - can someone else try it on their computer - just type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell me if a link to atpvbaen.xla shows up for them (assuming you have the analysis toolpak installed)? Also, how can I fix things so that, when I send the file to someone else, he won't have the problem with his computer not being able to find this link? Can he just change the source, assuming he can find his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL 2007, which may not deal with add-ins in the same way. Help, please! Thanks Dean |
ATPVBAEN Problems
Dean,
Not sure if the saving in 2007 version is an issue. Possibly just general corruption of the WB. Maybe one of the Code Cleaners would help. Have you checked for missing references under the VBE, ToolsReferences ? Any marlked as MISSING ? NickHK "Dean" wrote in message ... Nick, The guy has sent me back the file (the one that, eventually, mysteriously, wipes out all eomonth functions, leaving only N/A in their wakes) and I have attempted to use your myeomonth function in it, but all it returns is #NAME. I also have a very simple new file open that I created with your function in it and it works fine there. A clue may be that, in the file where it doesn't work, no matter how I type it in (lower case, upper case, etc), it changes it to: =myEOMONTH(O3,P3) that is, the first 2 letters are lower case and the next 7 letters are upper case. Seemingly, this is tacit recognition that it (the first file) does not comprehend the function. Yes, the function is in a macro sheet within both files; also macro security is set to low. What could explain this? Dean "NickHK" wrote in message ... Dean, Make it a Public function in a standard module, then you can call it as a worksheet function. As for the Lotus aspect, MS continued a bug that Lotus introduced in calculating one Leap year (1900 ?). Whilst this is probably not a problem for you, you did ask <g. NickHK "Dean" wrote in message ... I'm not sure what you are saying about Lotus. I'm using EXCEL - does is not work for leap years in EXCEL? I forget where to put such a function - is it on the "This Workbook" page? "NickHK" wrote in message ... Dean, As it is using VBA's date calculation engine, rather than my own, it will return the correct date (apart from the built-in Lotus compatibility leap year/non leap year). Basically, all it is goes to the first of the month after the one we want, then comes back 1 day. Hence the last day of the month we want. As for all the add-in stuff, I tend to avoid them, unless they provide something that is absolutely essential. From your description, I cannot reproduce these problem by installing/uninstalling either addin, in XL2002. NickHK "Dean" wrote in message ... Does your home grown function work for every date, leap year or not? Just to be sure, are you saying that, if you go to a blank spreadsheet and type in an eomonth function into some cell, then go to edit links, there is no link? Do you have the analysis toolpak FOR VBA installed also, or just the regular analysis toolpak? If just the regular one, then I might have some clues. I did some googling and found a post by our own Tom Ogilvy on some other forum which told me something I didn't know, that atpvbaen is really the add in for the VBA analysis toolpak, not the regular toolpack. So. I unchecked the VBA version in my EXCEL and then noticed that the eomonth function died, however if I retyped it in, it worked, and there was no edit link. However, as soon as I added the VBA add-in back in and typed a new eomomth function in another cell, I got the edit link back again - just for that one new eomonth function, not the others. So it seems that, at least on my computer, if I use the eomonth function, it looks to find it first in the analysis toolpak VBA, not just the regular analysis toolpak. I'm not sure I really need the analysis toolpak VBA, but it seems like it might be of some use! Does anyone have any idea how to tell EXCEL to look at the regular analysis toolpak first, or does the VBA one supersede the regular one, making this edit link unavoidable? The link seems to be problematic in that, if you send a file to someone else, it wants to update that atpvbaen link, which they may not even have. Thanks much, Nick! Dean "NickHK" wrote in message ... Dean, No, I see no EditLink using this function. By the way, if this is the only function you use, you can roll your own quite easily, then drop the dependence on this add-in: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function NickHK "Dean" wrote in message ... I sometimes use some functions that require the analysis toolpack, the basic EXCEL one, not the VBA one. A typical function is eomonth(date, # of months), which finds the ending date of the month that is so maybe months later. I notice that, when I save a file with this function, though there is no message asking me to update links, when I re-open this file, there is a link when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was this way before. I occasionally get messages suggesting the add-ins are not installed even though clearly they are, since the equation is still there and changes when I change the date it is computing from. In any event, I also sent such a file to someone else to use and he ended up losing all cells that had this eomonth function in them - they all show up as =#N/A, though the right answer is still there, probably because he did not update links, or maybe because he did. In any event, he loses the equations. I even notice that when I open this file, sometimes, I see the formula change so that, in the formula bar, it shows something like atpvbaen.xls//eomonth(x,y), which seems the cause of the problem. My first question is whether this link to ATPVBAEN.xla should show up as a link, under edit links - can someone else try it on their computer - just type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell me if a link to atpvbaen.xla shows up for them (assuming you have the analysis toolpak installed)? Also, how can I fix things so that, when I send the file to someone else, he won't have the problem with his computer not being able to find this link? Can he just change the source, assuming he can find his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL 2007, which may not deal with add-ins in the same way. Help, please! Thanks Dean |
ATPVBAEN Problems
Actually, if I try it in a brand new file, it doesn't work there either
(though I no longer have that case sensitivity issue). It only works in the one file I created last week. So, it seems to be a problem in my system too (in fact, it may well work in this guy's system, I'll find out tomorrow), but only in some EXCEL files! There files are open all at the same time, all have the function copied into VBE, yet it only works in one. Just to be clear, do I need to dimension anything in your function, or is it self dimensioning, assuming that means anything? I do NOT have the analysis toolpak add-in VBA installed, only the analysis toolpak without VBA. Is that fine? And the public function can be inserted in any VBE sheet, right, if under option explicit (or does it need to be in the "This Workbook" sheet)? Also, I'm not sure what a missing reference looks like. I see hundreds of choices but only the first four are checked, but nothing says missing. Kindly clarify. Thanks, Nick! Dean "NickHK" wrote in message ... Dean, Not sure if the saving in 2007 version is an issue. Possibly just general corruption of the WB. Maybe one of the Code Cleaners would help. Have you checked for missing references under the VBE, ToolsReferences ? Any marlked as MISSING ? NickHK "Dean" wrote in message ... Nick, The guy has sent me back the file (the one that, eventually, mysteriously, wipes out all eomonth functions, leaving only N/A in their wakes) and I have attempted to use your myeomonth function in it, but all it returns is #NAME. I also have a very simple new file open that I created with your function in it and it works fine there. A clue may be that, in the file where it doesn't work, no matter how I type it in (lower case, upper case, etc), it changes it to: =myEOMONTH(O3,P3) that is, the first 2 letters are lower case and the next 7 letters are upper case. Seemingly, this is tacit recognition that it (the first file) does not comprehend the function. Yes, the function is in a macro sheet within both files; also macro security is set to low. What could explain this? Dean "NickHK" wrote in message ... Dean, Make it a Public function in a standard module, then you can call it as a worksheet function. As for the Lotus aspect, MS continued a bug that Lotus introduced in calculating one Leap year (1900 ?). Whilst this is probably not a problem for you, you did ask <g. NickHK "Dean" wrote in message ... I'm not sure what you are saying about Lotus. I'm using EXCEL - does is not work for leap years in EXCEL? I forget where to put such a function - is it on the "This Workbook" page? "NickHK" wrote in message ... Dean, As it is using VBA's date calculation engine, rather than my own, it will return the correct date (apart from the built-in Lotus compatibility leap year/non leap year). Basically, all it is goes to the first of the month after the one we want, then comes back 1 day. Hence the last day of the month we want. As for all the add-in stuff, I tend to avoid them, unless they provide something that is absolutely essential. From your description, I cannot reproduce these problem by installing/uninstalling either addin, in XL2002. NickHK "Dean" wrote in message ... Does your home grown function work for every date, leap year or not? Just to be sure, are you saying that, if you go to a blank spreadsheet and type in an eomonth function into some cell, then go to edit links, there is no link? Do you have the analysis toolpak FOR VBA installed also, or just the regular analysis toolpak? If just the regular one, then I might have some clues. I did some googling and found a post by our own Tom Ogilvy on some other forum which told me something I didn't know, that atpvbaen is really the add in for the VBA analysis toolpak, not the regular toolpack. So. I unchecked the VBA version in my EXCEL and then noticed that the eomonth function died, however if I retyped it in, it worked, and there was no edit link. However, as soon as I added the VBA add-in back in and typed a new eomomth function in another cell, I got the edit link back again - just for that one new eomonth function, not the others. So it seems that, at least on my computer, if I use the eomonth function, it looks to find it first in the analysis toolpak VBA, not just the regular analysis toolpak. I'm not sure I really need the analysis toolpak VBA, but it seems like it might be of some use! Does anyone have any idea how to tell EXCEL to look at the regular analysis toolpak first, or does the VBA one supersede the regular one, making this edit link unavoidable? The link seems to be problematic in that, if you send a file to someone else, it wants to update that atpvbaen link, which they may not even have. Thanks much, Nick! Dean "NickHK" wrote in message ... Dean, No, I see no EditLink using this function. By the way, if this is the only function you use, you can roll your own quite easily, then drop the dependence on this add-in: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function NickHK "Dean" wrote in message ... I sometimes use some functions that require the analysis toolpack, the basic EXCEL one, not the VBA one. A typical function is eomonth(date, # of months), which finds the ending date of the month that is so maybe months later. I notice that, when I save a file with this function, though there is no message asking me to update links, when I re-open this file, there is a link when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was this way before. I occasionally get messages suggesting the add-ins are not installed even though clearly they are, since the equation is still there and changes when I change the date it is computing from. In any event, I also sent such a file to someone else to use and he ended up losing all cells that had this eomonth function in them - they all show up as =#N/A, though the right answer is still there, probably because he did not update links, or maybe because he did. In any event, he loses the equations. I even notice that when I open this file, sometimes, I see the formula change so that, in the formula bar, it shows something like atpvbaen.xls//eomonth(x,y), which seems the cause of the problem. My first question is whether this link to ATPVBAEN.xla should show up as a link, under edit links - can someone else try it on their computer - just type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell me if a link to atpvbaen.xla shows up for them (assuming you have the analysis toolpak installed)? Also, how can I fix things so that, when I send the file to someone else, he won't have the problem with his computer not being able to find this link? Can he just change the source, assuming he can find his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL 2007, which may not deal with add-ins in the same way. Help, please! Thanks Dean |
ATPVBAEN Problems
Dean,
To be visible as a worksheet function, it needs to be in a standard module, NOT a worksheet, NOR ThisWorkbook. As for the function: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function it works as is. Only VBA so does not depend on other components. If the module containing this function is called 'Module1" and in the Immediate, if you type: ?Module1. do you get Intellsense with the function nema then its signature ? And references: You would one/some marked as "MISSING" if there was a problem. NickHK "Dean" wrote in message ... Actually, if I try it in a brand new file, it doesn't work there either (though I no longer have that case sensitivity issue). It only works in the one file I created last week. So, it seems to be a problem in my system too (in fact, it may well work in this guy's system, I'll find out tomorrow), but only in some EXCEL files! There files are open all at the same time, all have the function copied into VBE, yet it only works in one. Just to be clear, do I need to dimension anything in your function, or is it self dimensioning, assuming that means anything? I do NOT have the analysis toolpak add-in VBA installed, only the analysis toolpak without VBA. Is that fine? And the public function can be inserted in any VBE sheet, right, if under option explicit (or does it need to be in the "This Workbook" sheet)? Also, I'm not sure what a missing reference looks like. I see hundreds of choices but only the first four are checked, but nothing says missing. Kindly clarify. Thanks, Nick! Dean "NickHK" wrote in message ... Dean, Not sure if the saving in 2007 version is an issue. Possibly just general corruption of the WB. Maybe one of the Code Cleaners would help. Have you checked for missing references under the VBE, ToolsReferences ? Any marlked as MISSING ? NickHK "Dean" wrote in message ... Nick, The guy has sent me back the file (the one that, eventually, mysteriously, wipes out all eomonth functions, leaving only N/A in their wakes) and I have attempted to use your myeomonth function in it, but all it returns is #NAME. I also have a very simple new file open that I created with your function in it and it works fine there. A clue may be that, in the file where it doesn't work, no matter how I type it in (lower case, upper case, etc), it changes it to: =myEOMONTH(O3,P3) that is, the first 2 letters are lower case and the next 7 letters are upper case. Seemingly, this is tacit recognition that it (the first file) does not comprehend the function. Yes, the function is in a macro sheet within both files; also macro security is set to low. What could explain this? Dean ------------------ CUT ------------------------ |
ATPVBAEN Problems
Ok, I had it in ThisWorkbook, that was the problem. Thanks!
Dean "NickHK" wrote in message ... Dean, To be visible as a worksheet function, it needs to be in a standard module, NOT a worksheet, NOR ThisWorkbook. As for the function: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function it works as is. Only VBA so does not depend on other components. If the module containing this function is called 'Module1" and in the Immediate, if you type: ?Module1. do you get Intellsense with the function nema then its signature ? And references: You would one/some marked as "MISSING" if there was a problem. NickHK "Dean" wrote in message ... Actually, if I try it in a brand new file, it doesn't work there either (though I no longer have that case sensitivity issue). It only works in the one file I created last week. So, it seems to be a problem in my system too (in fact, it may well work in this guy's system, I'll find out tomorrow), but only in some EXCEL files! There files are open all at the same time, all have the function copied into VBE, yet it only works in one. Just to be clear, do I need to dimension anything in your function, or is it self dimensioning, assuming that means anything? I do NOT have the analysis toolpak add-in VBA installed, only the analysis toolpak without VBA. Is that fine? And the public function can be inserted in any VBE sheet, right, if under option explicit (or does it need to be in the "This Workbook" sheet)? Also, I'm not sure what a missing reference looks like. I see hundreds of choices but only the first four are checked, but nothing says missing. Kindly clarify. Thanks, Nick! Dean "NickHK" wrote in message ... Dean, Not sure if the saving in 2007 version is an issue. Possibly just general corruption of the WB. Maybe one of the Code Cleaners would help. Have you checked for missing references under the VBE, ToolsReferences ? Any marlked as MISSING ? NickHK "Dean" wrote in message ... Nick, The guy has sent me back the file (the one that, eventually, mysteriously, wipes out all eomonth functions, leaving only N/A in their wakes) and I have attempted to use your myeomonth function in it, but all it returns is #NAME. I also have a very simple new file open that I created with your function in it and it works fine there. A clue may be that, in the file where it doesn't work, no matter how I type it in (lower case, upper case, etc), it changes it to: =myEOMONTH(O3,P3) that is, the first 2 letters are lower case and the next 7 letters are upper case. Seemingly, this is tacit recognition that it (the first file) does not comprehend the function. Yes, the function is in a macro sheet within both files; also macro security is set to low. What could explain this? Dean ------------------ CUT ------------------------ |
ATPVBAEN Problems
I am having some other flaky problems with analysis toolpak (aka
atpvbaen.xla). When I use some of its advanced functions, atpvbaen.xla shows up under edit links, though it doesn't query me about updating that link when I open the file. I assume it should not even show up under edit links, right? Thanks! Dean "NickHK" wrote in message ... Dean, Not sure if the saving in 2007 version is an issue. Possibly just general corruption of the WB. Maybe one of the Code Cleaners would help. Have you checked for missing references under the VBE, ToolsReferences ? Any marlked as MISSING ? NickHK "Dean" wrote in message ... Nick, The guy has sent me back the file (the one that, eventually, mysteriously, wipes out all eomonth functions, leaving only N/A in their wakes) and I have attempted to use your myeomonth function in it, but all it returns is #NAME. I also have a very simple new file open that I created with your function in it and it works fine there. A clue may be that, in the file where it doesn't work, no matter how I type it in (lower case, upper case, etc), it changes it to: =myEOMONTH(O3,P3) that is, the first 2 letters are lower case and the next 7 letters are upper case. Seemingly, this is tacit recognition that it (the first file) does not comprehend the function. Yes, the function is in a macro sheet within both files; also macro security is set to low. What could explain this? Dean "NickHK" wrote in message ... Dean, Make it a Public function in a standard module, then you can call it as a worksheet function. As for the Lotus aspect, MS continued a bug that Lotus introduced in calculating one Leap year (1900 ?). Whilst this is probably not a problem for you, you did ask <g. NickHK "Dean" wrote in message ... I'm not sure what you are saying about Lotus. I'm using EXCEL - does is not work for leap years in EXCEL? I forget where to put such a function - is it on the "This Workbook" page? "NickHK" wrote in message ... Dean, As it is using VBA's date calculation engine, rather than my own, it will return the correct date (apart from the built-in Lotus compatibility leap year/non leap year). Basically, all it is goes to the first of the month after the one we want, then comes back 1 day. Hence the last day of the month we want. As for all the add-in stuff, I tend to avoid them, unless they provide something that is absolutely essential. From your description, I cannot reproduce these problem by installing/uninstalling either addin, in XL2002. NickHK "Dean" wrote in message ... Does your home grown function work for every date, leap year or not? Just to be sure, are you saying that, if you go to a blank spreadsheet and type in an eomonth function into some cell, then go to edit links, there is no link? Do you have the analysis toolpak FOR VBA installed also, or just the regular analysis toolpak? If just the regular one, then I might have some clues. I did some googling and found a post by our own Tom Ogilvy on some other forum which told me something I didn't know, that atpvbaen is really the add in for the VBA analysis toolpak, not the regular toolpack. So. I unchecked the VBA version in my EXCEL and then noticed that the eomonth function died, however if I retyped it in, it worked, and there was no edit link. However, as soon as I added the VBA add-in back in and typed a new eomomth function in another cell, I got the edit link back again - just for that one new eomonth function, not the others. So it seems that, at least on my computer, if I use the eomonth function, it looks to find it first in the analysis toolpak VBA, not just the regular analysis toolpak. I'm not sure I really need the analysis toolpak VBA, but it seems like it might be of some use! Does anyone have any idea how to tell EXCEL to look at the regular analysis toolpak first, or does the VBA one supersede the regular one, making this edit link unavoidable? The link seems to be problematic in that, if you send a file to someone else, it wants to update that atpvbaen link, which they may not even have. Thanks much, Nick! Dean "NickHK" wrote in message ... Dean, No, I see no EditLink using this function. By the way, if this is the only function you use, you can roll your own quite easily, then drop the dependence on this add-in: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function NickHK "Dean" wrote in message ... I sometimes use some functions that require the analysis toolpack, the basic EXCEL one, not the VBA one. A typical function is eomonth(date, # of months), which finds the ending date of the month that is so maybe months later. I notice that, when I save a file with this function, though there is no message asking me to update links, when I re-open this file, there is a link when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was this way before. I occasionally get messages suggesting the add-ins are not installed even though clearly they are, since the equation is still there and changes when I change the date it is computing from. In any event, I also sent such a file to someone else to use and he ended up losing all cells that had this eomonth function in them - they all show up as =#N/A, though the right answer is still there, probably because he did not update links, or maybe because he did. In any event, he loses the equations. I even notice that when I open this file, sometimes, I see the formula change so that, in the formula bar, it shows something like atpvbaen.xls//eomonth(x,y), which seems the cause of the problem. My first question is whether this link to ATPVBAEN.xla should show up as a link, under edit links - can someone else try it on their computer - just type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell me if a link to atpvbaen.xla shows up for them (assuming you have the analysis toolpak installed)? Also, how can I fix things so that, when I send the file to someone else, he won't have the problem with his computer not being able to find this link? Can he just change the source, assuming he can find his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL 2007, which may not deal with add-ins in the same way. Help, please! Thanks Dean |
ATPVBAEN Problems
Hi Nick & Dean,
I don't have a real solution to offer but just want to note that I've been getting the same problem recently. I've been using =eomonth(), =edate(), =yearfrac()= for years and never had a problem until recently starting may be two months or so ago. For completely unknown reasons, in select files where I use those date functions, excel all by itself started looking for them in atpvbaen.xla. But since those date functions are not there, I get "c:\Program Files\MS Office\Excel\atpvbaen.xla!eomonth" or something inserted in place of all those date functions which shows #NAME as value. To resolve it, I just did a global search & replace to take out the "c:\Program Files\... " bit and it worked. However, since doing that, one of these files I did the search/replace started showing the same problem again. I then did another round of search/replace. For the last couple of weeks I have not seen this recur. I'd like to find out if anyone has a definitive way of reproducing this problem. So far I've not the time to figure it out. The only other observation I can make is that those files that showed this problem uses 3rd party add-ins to grab data into excel over the web. "Dean" wrote: I am having some other flaky problems with analysis toolpak (aka atpvbaen.xla). When I use some of its advanced functions, atpvbaen.xla shows up under edit links, though it doesn't query me about updating that link when I open the file. I assume it should not even show up under edit links, right? Thanks! Dean "NickHK" wrote in message ... Dean, Not sure if the saving in 2007 version is an issue. Possibly just general corruption of the WB. Maybe one of the Code Cleaners would help. Have you checked for missing references under the VBE, ToolsReferences ? Any marlked as MISSING ? NickHK "Dean" wrote in message ... Nick, The guy has sent me back the file (the one that, eventually, mysteriously, wipes out all eomonth functions, leaving only N/A in their wakes) and I have attempted to use your myeomonth function in it, but all it returns is #NAME. I also have a very simple new file open that I created with your function in it and it works fine there. A clue may be that, in the file where it doesn't work, no matter how I type it in (lower case, upper case, etc), it changes it to: =myEOMONTH(O3,P3) that is, the first 2 letters are lower case and the next 7 letters are upper case. Seemingly, this is tacit recognition that it (the first file) does not comprehend the function. Yes, the function is in a macro sheet within both files; also macro security is set to low. What could explain this? Dean "NickHK" wrote in message ... Dean, Make it a Public function in a standard module, then you can call it as a worksheet function. As for the Lotus aspect, MS continued a bug that Lotus introduced in calculating one Leap year (1900 ?). Whilst this is probably not a problem for you, you did ask <g. NickHK "Dean" wrote in message ... I'm not sure what you are saying about Lotus. I'm using EXCEL - does is not work for leap years in EXCEL? I forget where to put such a function - is it on the "This Workbook" page? "NickHK" wrote in message ... Dean, As it is using VBA's date calculation engine, rather than my own, it will return the correct date (apart from the built-in Lotus compatibility leap year/non leap year). Basically, all it is goes to the first of the month after the one we want, then comes back 1 day. Hence the last day of the month we want. As for all the add-in stuff, I tend to avoid them, unless they provide something that is absolutely essential. From your description, I cannot reproduce these problem by installing/uninstalling either addin, in XL2002. NickHK "Dean" wrote in message ... Does your home grown function work for every date, leap year or not? Just to be sure, are you saying that, if you go to a blank spreadsheet and type in an eomonth function into some cell, then go to edit links, there is no link? Do you have the analysis toolpak FOR VBA installed also, or just the regular analysis toolpak? If just the regular one, then I might have some clues. I did some googling and found a post by our own Tom Ogilvy on some other forum which told me something I didn't know, that atpvbaen is really the add in for the VBA analysis toolpak, not the regular toolpack. So. I unchecked the VBA version in my EXCEL and then noticed that the eomonth function died, however if I retyped it in, it worked, and there was no edit link. However, as soon as I added the VBA add-in back in and typed a new eomomth function in another cell, I got the edit link back again - just for that one new eomonth function, not the others. So it seems that, at least on my computer, if I use the eomonth function, it looks to find it first in the analysis toolpak VBA, not just the regular analysis toolpak. I'm not sure I really need the analysis toolpak VBA, but it seems like it might be of some use! Does anyone have any idea how to tell EXCEL to look at the regular analysis toolpak first, or does the VBA one supersede the regular one, making this edit link unavoidable? The link seems to be problematic in that, if you send a file to someone else, it wants to update that atpvbaen link, which they may not even have. Thanks much, Nick! Dean "NickHK" wrote in message ... Dean, No, I see no EditLink using this function. By the way, if this is the only function you use, you can roll your own quite easily, then drop the dependence on this add-in: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function NickHK "Dean" wrote in message ... I sometimes use some functions that require the analysis toolpack, the basic EXCEL one, not the VBA one. A typical function is eomonth(date, # of months), which finds the ending date of the month that is so maybe months later. I notice that, when I save a file with this function, though there is no message asking me to update links, when I re-open this file, there is a link when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was this way before. I occasionally get messages suggesting the add-ins are not installed even though clearly they are, since the equation is still there and changes when I change the date it is computing from. In any event, I also sent such a file to someone else to use and he ended up losing all cells that had this eomonth function in them - they all show up as =#N/A, though the right answer is still there, probably because he did not update links, or maybe because he did. In any event, he loses the equations. I even notice that when I open this file, sometimes, I see the formula change so that, in the formula bar, it shows something like atpvbaen.xls//eomonth(x,y), which seems the cause of the problem. My first question is whether this link to ATPVBAEN.xla should show up as a link, under edit links - can someone else try it on their computer - just type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell me if a link to atpvbaen.xla shows up for them (assuming you have the analysis toolpak installed)? Also, how can I fix things so that, when I send the file to someone else, he won't have the problem with his computer not being able to find this link? Can he just change the source, assuming he can find his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL 2007, which may not deal with add-ins in the same way. Help, please! Thanks Dean |
ATPVBAEN Problems
Very nice to know that someone else has seen this, too. My files with
problems do not use any third party add ins so you can probably eliminate that. Nick may have home grown functions for you - he had one for me for eomonth. It worked nicely. "rockhammer" wrote in message ... Hi Nick & Dean, I don't have a real solution to offer but just want to note that I've been getting the same problem recently. I've been using =eomonth(), =edate(), =yearfrac()= for years and never had a problem until recently starting may be two months or so ago. For completely unknown reasons, in select files where I use those date functions, excel all by itself started looking for them in atpvbaen.xla. But since those date functions are not there, I get "c:\Program Files\MS Office\Excel\atpvbaen.xla!eomonth" or something inserted in place of all those date functions which shows #NAME as value. To resolve it, I just did a global search & replace to take out the "c:\Program Files\... " bit and it worked. However, since doing that, one of these files I did the search/replace started showing the same problem again. I then did another round of search/replace. For the last couple of weeks I have not seen this recur. I'd like to find out if anyone has a definitive way of reproducing this problem. So far I've not the time to figure it out. The only other observation I can make is that those files that showed this problem uses 3rd party add-ins to grab data into excel over the web. "Dean" wrote: I am having some other flaky problems with analysis toolpak (aka atpvbaen.xla). When I use some of its advanced functions, atpvbaen.xla shows up under edit links, though it doesn't query me about updating that link when I open the file. I assume it should not even show up under edit links, right? Thanks! Dean "NickHK" wrote in message ... Dean, Not sure if the saving in 2007 version is an issue. Possibly just general corruption of the WB. Maybe one of the Code Cleaners would help. Have you checked for missing references under the VBE, ToolsReferences ? Any marlked as MISSING ? NickHK "Dean" wrote in message ... Nick, The guy has sent me back the file (the one that, eventually, mysteriously, wipes out all eomonth functions, leaving only N/A in their wakes) and I have attempted to use your myeomonth function in it, but all it returns is #NAME. I also have a very simple new file open that I created with your function in it and it works fine there. A clue may be that, in the file where it doesn't work, no matter how I type it in (lower case, upper case, etc), it changes it to: =myEOMONTH(O3,P3) that is, the first 2 letters are lower case and the next 7 letters are upper case. Seemingly, this is tacit recognition that it (the first file) does not comprehend the function. Yes, the function is in a macro sheet within both files; also macro security is set to low. What could explain this? Dean "NickHK" wrote in message ... Dean, Make it a Public function in a standard module, then you can call it as a worksheet function. As for the Lotus aspect, MS continued a bug that Lotus introduced in calculating one Leap year (1900 ?). Whilst this is probably not a problem for you, you did ask <g. NickHK "Dean" wrote in message ... I'm not sure what you are saying about Lotus. I'm using EXCEL - does is not work for leap years in EXCEL? I forget where to put such a function - is it on the "This Workbook" page? "NickHK" wrote in message ... Dean, As it is using VBA's date calculation engine, rather than my own, it will return the correct date (apart from the built-in Lotus compatibility leap year/non leap year). Basically, all it is goes to the first of the month after the one we want, then comes back 1 day. Hence the last day of the month we want. As for all the add-in stuff, I tend to avoid them, unless they provide something that is absolutely essential. From your description, I cannot reproduce these problem by installing/uninstalling either addin, in XL2002. NickHK "Dean" wrote in message ... Does your home grown function work for every date, leap year or not? Just to be sure, are you saying that, if you go to a blank spreadsheet and type in an eomonth function into some cell, then go to edit links, there is no link? Do you have the analysis toolpak FOR VBA installed also, or just the regular analysis toolpak? If just the regular one, then I might have some clues. I did some googling and found a post by our own Tom Ogilvy on some other forum which told me something I didn't know, that atpvbaen is really the add in for the VBA analysis toolpak, not the regular toolpack. So. I unchecked the VBA version in my EXCEL and then noticed that the eomonth function died, however if I retyped it in, it worked, and there was no edit link. However, as soon as I added the VBA add-in back in and typed a new eomomth function in another cell, I got the edit link back again - just for that one new eomonth function, not the others. So it seems that, at least on my computer, if I use the eomonth function, it looks to find it first in the analysis toolpak VBA, not just the regular analysis toolpak. I'm not sure I really need the analysis toolpak VBA, but it seems like it might be of some use! Does anyone have any idea how to tell EXCEL to look at the regular analysis toolpak first, or does the VBA one supersede the regular one, making this edit link unavoidable? The link seems to be problematic in that, if you send a file to someone else, it wants to update that atpvbaen link, which they may not even have. Thanks much, Nick! Dean "NickHK" wrote in message ... Dean, No, I see no EditLink using this function. By the way, if this is the only function you use, you can roll your own quite easily, then drop the dependence on this add-in: Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As Date MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd + 1, 0) End Function NickHK "Dean" wrote in message ... I sometimes use some functions that require the analysis toolpack, the basic EXCEL one, not the VBA one. A typical function is eomonth(date, # of months), which finds the ending date of the month that is so maybe months later. I notice that, when I save a file with this function, though there is no message asking me to update links, when I re-open this file, there is a link when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was this way before. I occasionally get messages suggesting the add-ins are not installed even though clearly they are, since the equation is still there and changes when I change the date it is computing from. In any event, I also sent such a file to someone else to use and he ended up losing all cells that had this eomonth function in them - they all show up as =#N/A, though the right answer is still there, probably because he did not update links, or maybe because he did. In any event, he loses the equations. I even notice that when I open this file, sometimes, I see the formula change so that, in the formula bar, it shows something like atpvbaen.xls//eomonth(x,y), which seems the cause of the problem. My first question is whether this link to ATPVBAEN.xla should show up as a link, under edit links - can someone else try it on their computer - just type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell me if a link to atpvbaen.xla shows up for them (assuming you have the analysis toolpak installed)? Also, how can I fix things so that, when I send the file to someone else, he won't have the problem with his computer not being able to find this link? Can he just change the source, assuming he can find his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL 2007, which may not deal with add-ins in the same way. Help, please! Thanks Dean |
| All times are GMT +1. The time now is 07:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com