In Excel 2007 (I'm on SP2), if I enter this formula: =SUM(A65537) It works fine. But if I enter this formula: =MYUDF(A65537) Excel turns it into this: =MYUDF(#REF!) That isn't the UDF's fault' Excel is doing that before the UDF has the opportunity to do anything at all. Is there a workaround or fix? Thanks, Greg

0 |

5/11/2010 8:02:36 PM

It sounds like you are working in Compatibility Mode. How many rows does your workbook have on a sheet? I created a simple MyUDF function, and it accepts any range up to 1048576 unless the workbook calling it was saved in an earlier Excel format (.xls instead of .xlsx). Hope this helps, Hutch "Greg Lovern" wrote: > In Excel 2007 (I'm on SP2), if I enter this formula: > > =SUM(A65537) > > It works fine. But if I enter this formula: > > =MYUDF(A65537) > > Excel turns it into this: > > =MYUDF(#REF!) > > > That isn't the UDF's fault' Excel is doing that before the UDF has the > opportunity to do anything at all. Is there a workaround or fix? > > > Thanks, > > Greg > . >

0 |

5/11/2010 9:09:01 PM

Hi Tom, I'm in a .xlsx saved out from Excel 2007 as a standard Excel .xlsx file. The last cell is XFD1048576. I've been working with it more and found that the problem was not as simple as I'd thought. The problem seems to require that the UDF have two arguments (both required args). Then, the problem happens even if only one arg is passed, which is invalid for the UDF but even so, the reference in the formula shouldn't change to #REF!. So to get a simple repro, change the UDF to require two args, then reference cells below row 65536 in those args: MYUDF(A65537,A65537) Result: Excel changes the formula to: MYUDF(#REF!,A65537) Any thoughts? BTW I've been working with UDFs in an .xla that has stubs that call an ActiveX DLL. I'll see if I get the same problem with a simpler scenario. Come to think of it, the .xla is in Excel 97-2003 format (for compatibility with those Excel versions). Thanks, Greg On May 11, 2:09=A0pm, Tom Hutchins <TomHutch...@discussions.microsoft.com> wrote: > It sounds like you are working in Compatibility Mode. How many rows does = your > workbook have on a sheet? I created a simple MyUDF function, and it accep= ts > any range up to 1048576 unless the workbook calling it was saved in an > earlier Excel format (.xls instead of .xlsx). > > Hope this helps, > > Hutch > > > > "Greg Lovern" wrote: > > In Excel 2007 (I'm on SP2), if I enter this formula: > > > =3DSUM(A65537) > > > It works fine. But if I enter this formula: > > > =3DMYUDF(A65537) > > > Excel turns it into this: > > > =3DMYUDF(#REF!) > > > That isn't the UDF's fault' Excel is doing that before the UDF has the > > opportunity to do anything at all. Is there a workaround or fix? > > > Thanks, > > > Greg > > .- Hide quoted text - > > - Show quoted text -

0 |

5/11/2010 9:35:10 PM

I've found that if I go into the .xla and comment out the call to the DLL, and just add the two references together, it works fine. So I'm going to have to step through the DLL next, which I don't have with me at the moment. No matter what the DLL might be doing wrong, Excel shouldn't change the formula like that. Any error should be returned as the return value, not as a change to the formula itself. Are there any circumstances under which Excel is expected to change the formula when just calculating the formula? Thanks, Greg On May 11, 2:35=A0pm, Greg Lovern <gr...@gregl.net> wrote: > Hi Tom, > > I'm in a .xlsx saved out from Excel 2007 as a standard Excel .xlsx > file. The last cell is XFD1048576. > > I've been working with it more and found that the problem was not as > simple as I'd thought. > > The problem seems to require that the UDF have two arguments (both > required args). Then, the problem happens even if only one arg is > passed, which is invalid for the UDF but even so, the reference in the > formula shouldn't change to #REF!. > > So to get a simple repro, change the UDF to require two args, then > reference cells below row 65536 in those args: > > MYUDF(A65537,A65537) > > Result: Excel changes the formula to: > > MYUDF(#REF!,A65537) > > Any thoughts? > > BTW I've been working with UDFs in an .xla that has stubs that call an > ActiveX DLL. I'll see if I get the same problem with a simpler > scenario. Come to think of it, the .xla is in Excel 97-2003 format > (for compatibility with those Excel versions). > > Thanks, > > Greg > > On May 11, 2:09=A0pm, Tom Hutchins > > > > <TomHutch...@discussions.microsoft.com> wrote: > > It sounds like you are working in Compatibility Mode. How many rows doe= s your > > workbook have on a sheet? I created a simple MyUDF function, and it acc= epts > > any range up to 1048576 unless the workbook calling it was saved in an > > earlier Excel format (.xls instead of .xlsx). > > > Hope this helps, > > > Hutch > > > "Greg Lovern" wrote: > > > In Excel 2007 (I'm on SP2), if I enter this formula: > > > > =3DSUM(A65537) > > > > It works fine. But if I enter this formula: > > > > =3DMYUDF(A65537) > > > > Excel turns it into this: > > > > =3DMYUDF(#REF!) > > > > That isn't the UDF's fault' Excel is doing that before the UDF has th= e > > > opportunity to do anything at all. Is there a workaround or fix? > > > > Thanks, > > > > Greg > > > .- Hide quoted text - > > > - Show quoted text -- Hide quoted text - > > - Show quoted text -

0 |

5/11/2010 9:54:42 PM

You hadn't mentioned any .DLL or .xla files. I think that one (or both) of them must be based on an Excel 2003 or earlier format. Is the 65636-row limit hard-coded in the DLL? Even though your current workbook is in Excel 2007 format, the UDF (and it sounds like the .DLL is the culprit) still has the limitations of XL2003. The .DLL and .xla files need to be rewritten/recreated in an XL2007-compatible format. Hutch "Greg Lovern" wrote: > I've found that if I go into the .xla and comment out the call to the > DLL, and just add the two references together, it works fine. So I'm > going to have to step through the DLL next, which I don't have with me > at the moment. > > No matter what the DLL might be doing wrong, Excel shouldn't change > the formula like that. Any error should be returned as the return > value, not as a change to the formula itself. Are there any > circumstances under which Excel is expected to change the formula when > just calculating the formula? > > > Thanks, > > Greg > > > On May 11, 2:35 pm, Greg Lovern <gr...@gregl.net> wrote: > > Hi Tom, > > > > I'm in a .xlsx saved out from Excel 2007 as a standard Excel .xlsx > > file. The last cell is XFD1048576. > > > > I've been working with it more and found that the problem was not as > > simple as I'd thought. > > > > The problem seems to require that the UDF have two arguments (both > > required args). Then, the problem happens even if only one arg is > > passed, which is invalid for the UDF but even so, the reference in the > > formula shouldn't change to #REF!. > > > > So to get a simple repro, change the UDF to require two args, then > > reference cells below row 65536 in those args: > > > > MYUDF(A65537,A65537) > > > > Result: Excel changes the formula to: > > > > MYUDF(#REF!,A65537) > > > > Any thoughts? > > > > BTW I've been working with UDFs in an .xla that has stubs that call an > > ActiveX DLL. I'll see if I get the same problem with a simpler > > scenario. Come to think of it, the .xla is in Excel 97-2003 format > > (for compatibility with those Excel versions). > > > > Thanks, > > > > Greg > > > > On May 11, 2:09 pm, Tom Hutchins > > > > > > > > <TomHutch...@discussions.microsoft.com> wrote: > > > It sounds like you are working in Compatibility Mode. How many rows does your > > > workbook have on a sheet? I created a simple MyUDF function, and it accepts > > > any range up to 1048576 unless the workbook calling it was saved in an > > > earlier Excel format (.xls instead of .xlsx). > > > > > Hope this helps, > > > > > Hutch > > > > > "Greg Lovern" wrote: > > > > In Excel 2007 (I'm on SP2), if I enter this formula: > > > > > > =SUM(A65537) > > > > > > It works fine. But if I enter this formula: > > > > > > =MYUDF(A65537) > > > > > > Excel turns it into this: > > > > > > =MYUDF(#REF!) > > > > > > That isn't the UDF's fault' Excel is doing that before the UDF has the > > > > opportunity to do anything at all. Is there a workaround or fix? > > > > > > Thanks, > > > > > > Greg > > > > .- Hide quoted text - > > > > > - Show quoted text -- Hide quoted text - > > > > - Show quoted text - > > . >

0 |

5/12/2010 2:36:01 AM

On Tue, 11 May 2010 14:35:10 -0700 (PDT), Greg Lovern <gregl@gregl.net> wrote: >BTW I've been working with UDFs in an .xla that has stubs that call an >ActiveX DLL. I'll see if I get the same problem with a simpler >scenario. Come to think of it, the .xla is in Excel 97-2003 format >(for compatibility with those Excel versions). Try it in an .xlam, which is the Excel 2007 version of the .xla FWIW, your problem does not reproduce in my Excel 2007. --ron

0 |

5/12/2010 11:37:09 AM

When I got home last night, I tried it again, to work on a workaround until I have a chance to find and fix the bug. To my surprise, it did NOT repro on my home computer! When I got back here this morning, I verified that it does repro here. On both computers, I'm running Excel 2007 SP2, and I was using the same version of my add-in, with the xla and dll both installed by the same release package. To repro the problem, in both cases I was using the same sample .xlsx file. I have no idea why it works on one computer and not the other. Greg On May 12, 4:37=A0am, Ron Rosenfeld <ronrosenf...@nospam.org> wrote: > On Tue, 11 May 2010 14:35:10 -0700 (PDT), Greg Lovern <gr...@gregl.net> w= rote: > >BTW I've been working with UDFs in an .xla that has stubs that call an > >ActiveX DLL. I'll see if I get the same problem with a simpler > >scenario. Come to think of it, the .xla is in Excel 97-2003 format > >(for compatibility with those Excel versions). > > Try it in an .xlam, which is the Excel 2007 version of the .xla > > FWIW, your problem does not reproduce in my Excel 2007. > > --ron

0 |

5/12/2010 2:24:46 PM

Sorry, I forgot to add: If the problem only occured on one computer, I'd be inclined to think it was a problem with the Excel installation on that computer. But the problem exists on at least two computers -- the problem was originally reported to me by a customer, and it was his sample .xlsx file I used on both of my computers to repro. So the problem happens on his computer and one of mine, but not on another computer of mine. Greg On May 12, 7:24=A0am, Greg Lovern <gr...@gregl.net> wrote: > When I got home last night, I tried it again, to work on a workaround > until I have a chance to find and fix the bug. To my surprise, it did > NOT repro on my home computer! When I got back here this morning, I > verified that it does repro here. > > On both computers, I'm running Excel 2007 SP2, and I was using the > same version of my add-in, with the xla and dll both installed by the > same release package. To repro the problem, in both cases I was using > the same sample .xlsx file. > > I have no idea why it works on one computer and not the other. > > Greg > > On May 12, 4:37=A0am, Ron Rosenfeld <ronrosenf...@nospam.org> wrote: > > > > > On Tue, 11 May 2010 14:35:10 -0700 (PDT), Greg Lovern <gr...@gregl.net>= wrote: > > >BTW I've been working with UDFs in an .xla that has stubs that call an > > >ActiveX DLL. I'll see if I get the same problem with a simpler > > >scenario. Come to think of it, the .xla is in Excel 97-2003 format > > >(for compatibility with those Excel versions). > > > Try it in an .xlam, which is the Excel 2007 version of the .xla > > > FWIW, your problem does not reproduce in my Excel 2007. > > > --ron- Hide quoted text - > > - Show quoted text -

0 |

5/12/2010 2:30:31 PM

On Wed, 12 May 2010 07:24:46 -0700 (PDT), Greg Lovern <gregl@gregl.net> wrote: >When I got home last night, I tried it again, to work on a workaround >until I have a chance to find and fix the bug. To my surprise, it did >NOT repro on my home computer! When I got back here this morning, I >verified that it does repro here. > >On both computers, I'm running Excel 2007 SP2, and I was using the >same version of my add-in, with the xla and dll both installed by the >same release package. To repro the problem, in both cases I was using >the same sample .xlsx file. > >I have no idea why it works on one computer and not the other. > > >Greg > > Try saving the file as an .xlsm file. --ron

0 |

5/12/2010 7:46:26 PM

On Wed, 12 May 2010 07:30:31 -0700 (PDT), Greg Lovern <gregl@gregl.net> wrote: >Sorry, I forgot to add: > >If the problem only occured on one computer, I'd be inclined to think >it was a problem with the Excel installation on that computer. But the >problem exists on at least two computers -- the problem was originally >reported to me by a customer, and it was his sample .xlsx file I used >on both of my computers to repro. > >So the problem happens on his computer and one of mine, but not on >another computer of mine. > >Greg I think it has to do with compatibility mode incompatibilities. --ron

0 |

5/12/2010 8:03:34 PM

It stopped reproducing here after I restarted Excel. I tried several different ways but haven't been able to get the problem to happen again. I'll pick it up again if I can get a repro. Also, when I can get the time I'll step through the DLL function to see if I can find the problem. But the .xla is just passing values to the DLL, not range references. Greg On May 12, 1:03=A0pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote: > On Wed, 12 May 2010 07:30:31 -0700 (PDT), Greg Lovern <gr...@gregl.net> w= rote: > >Sorry, I forgot to add: > > >If the problem only occured on one computer, I'd be inclined to think > >it was a problem with the Excel installation on that computer. But the > >problem exists on at least two computers -- the problem was originally > >reported to me by a customer, and it was his sample .xlsx file I used > >on both of my computers to repro. > > >So the problem happens on his computer and one of mine, but not on > >another computer of mine. > > >Greg > > I think it has to do with compatibility mode incompatibilities. > --ron

0 |

5/12/2010 10:02:07 PM

Hello, I have created a data query in Excel using DATA - IMPORT EXTERNAL DATA - NEW DATABASE QUERY and then selecting Access and browsing to an query within an Access database. This query returns all data into excel, but I would like to limit the criteria based on what a user has typed into a cell in the Excel workbook. I thought this could be accomplished by clicking DATA - IMPORT EXTERNAL DATA then PARAMETERS but the PARAMETERS option is grayed out. I can select EDIT CURRENT QUERY, but this just brings me into the Microsoft query, where I don't see any options to query based on...

I have a number of Access queries linked and displayed in an Excel workbook by way of Data, Import External Data, Import Data. Some of the criteria and select statements in the queries use the keyword "Like" (e.g. Like "*Smith" to pull up all names ending in "Smith"). These statements seem to be completely ignored by Excel so that records I should see are not appearing in the workbook. This worked fine in versions 97 (from which we've just upgraded) so is this a version 2003 bug? If so, is there a fix? ...

Does anyone know if there is a way to change excel's 2007 autofilter functionality back to like excel 2003 autoflilter use to work? I need to quickly bring up like values and compare other columns based upon the filtered values and the extra clicking of the check boxes is driving me nuts! If it helps any (and if you haven't thought about it yet), you can click the "(Select All)" entry twice to remove your current selections. -- Rick (MVP - Excel) "jonnybrovo815" <jonnybrovo815@discussions.microsoft.com> wrote in message news:36837406-1...

How do you compare 2 excel documents with track changes and merge them together after accepting the changes in Excel 2007? ...

I have created an workbook with Excel and put a photo on the first page. when I publish the workbook as an web page (interactivity) my photo is missing. Anyone know what I am doing wrong? I have try everything I know to do = insert picture, putting copying photo to clipboard and shift, pasta picture and still logo is missing. Lee ...

I was wondering if I could get some help on how to create a CVP graph in Excel 2007. A cost volume profit shows how costs, revenues, and profits vary with volume (sales). You can either plot total cost, or fixed and variable costs, which add up to total costs. One way to show this is to make a break-even chart. Put unit sales (number of items sold) in the first column, fixed costs in the second (which are a constant), variable costs in the third column (these are typically a straight line through zero), and in the fourth insert formulas that sum fixed and variable costs to make total ...

I am using Excel 2007 in the Vista OS. I am trying to use conditional formatting with an icon set in a column but Excel will not allow relative references. Example: A1=10 min B1=20 max C1=30 oh D1=a formula { =IF(C1=0,B1,(B1-C1)) } The condtional formatting of D1 would be: if the value showing in D1 is >B1 the cell would show a red 'X', if the value showing in D1 is <A1 the cell would show a green 'check mark', and if the value showing in D1 is between the values in A1 and B1 it would show a yellow 'exclamation point'. I can get the D1 ...

Greetings, Let's say I have a min, a max, and an increment, all decimal values. I want to autofill or autopopulate a row or column -- without manually dragging cels -- over the range [min,max] incrementing by the increment value each cell. Can this be done somehow? Also, what if you don't know how many cells you will need initially (you return some variable integer from a calculation)--can you tell Excel to fill only that number of cells each time? For example, let's say I compute some number and get a 7 one time and a 10 the next, and I want 7 cells filled with data and then ...

Anyone out there know a way to split a single excel cell diagonally in order to have it contain 2 pieces of information? Forrest, As far as I know, you can not split a cell diagonally so that it can contain 2 pieces of information. However, you can merge cells which might give you the effect that you want. To do so, go to the standard toolbar and hit Format -> Cells -> Alignment Tab -> Text Control and work with the merged cells option. ---- Regards, John Mansfield http://www.pdbook.com "Forrest" wrote: > Anyone out there know a way to split a single excel ce...

Is there a way in code to copy data out of an Excel file even if Excel is not up and running? If Excel were open, I'd copy, say, the first 30 rows and paste the info to PowerPoint. Then, since rows 1-5 are for column headings, I'd hide rows 6-30 and copy a new range which would look involve 30 rows, but since rows 6-30 were hidden would be 1-5 and then 31-55 as a contiguous block. I am trying to do this in VBA without opening Excel and instatiating objects, etc. Is it possible? What VBA commands would I use? You would need to treat the excel file as a database and use ADO to g...

A good example of a missing mode indicator one for whether th calculation mode is set to manual or automatic. Isn't there any othe way to find out other than going to the Tools>Options>Calculatio dialog? This has messed me up almost any time calculation gets set t manual. Another example is that trying to use stored Custom Views for filtere lists gives the error "Some view settings could not be applied" on som worksheets (usually complex ones), but works OK in others (usuall simpler ones). I know sheet protection is one thing that can caus this (which itself has no mod...

Hello UserA wanted UsersB mailbox. diabled both exchange mailbox and then reconnect userB to userA. UsersA is prompted for userB AD login info. the only way around this was to give userA sendas and full rights to the userB mailfile. Is there another way to fix it. ? what will happen when the AD account for userB is deleted for AD? ...

Hi, I have a word template which in autonew makes a mail merge and sends e-mail. This have worked fine but now one of my customers have updated to windows7 and office 2007 and suddenly no mail is sent. I have other customers running XP and office 2007 were it works fine. But for this client the question from outlook is raised asking if its OK to send mail from an other application but no mail is sent. The code: Sub Autonew() Dim SQL_sats As String Registry_Path = "HKEY_CURRENT_USER\Software\The company\Directories" Registry_Key = "CustDocs" Fo...

Over the last few months I have had the same problem in excel with all charts where I've used a two colour fill effect for either a data series or background. When I use this feature the entire chart is only viewable in black and white and excel takes a long time to actually draw the chart. However if I print the chart or even copy and paste into excel all the colours are as they should be. The mpeg moving shows how excel draws an example chart when the chart sheet is activated http://www.paperlessbooks.co.uk/barchart_example.avi and to see how excel prints to pdf see the pdf belo...

When using the filter only the first 1000 unique entries in a list will appear when you click on the arrow. I have more than 1000 unique entries [ie. company names] that I would like to see when filtering. Is there any way of being able to accomplish this? Hi you may have a look at http://www.contextures.com/xlautofilter02.html#Limits for a workaround Frank Gail wrote: > When using the filter only the first 1000 unique entries in a list > will appear when you click on the arrow. I have more than 1000 > unique entries [ie. company names] that I would like to see when > filterin...

This is a copy/paste with minor edits from post submitted yesterday ... Excel 2000 ... I have a single page spread sheet (my own) ... built in Excel 2000 (still Excel 2000) that was working fine. After expanding the spread sheet to handle more equipment I received error message stating: Excel can not SAVE all of the added data & formatting ... Here I had the option to select OK without fixing ... CANCEL ... or ... HELP. I selected HELP & ended up on a blank HELP Page ... So now I am turning to this board. Anybody know what gives? Could I be running into a formatting limit i...

I am a teacher. I have created my gradebook in excel. (I don't have access). I have 7 worksheets (one for each class that I teach). The worksheets are not exactly the same. I would like to be able to print out the top two rows (column labels) with each students data (row). This way the student can see row 1 (name of assignment), row 2 (points), their row (their personal data for each assignment). Any help?? Go to File > Page Setup > Sheet Then in the "Rows to repeat at top" highlight the label headings row(s). HTH -- _______________________ Naz, London...

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I get a coloured wheel of death every time i use excel from office 2008, almost every time I type into a cell, for about 3 - 6 seconds. I've looked on here and there seems to be a lot of discussion about hp drivers, but I don't have an hp printer. I'm working in normal view. Can anyone help? First, be certain that both Office (12.2.3) & OS X (10.6.2) are fully updated. Run Disk Utility - Repair Disk Permissions, then see if things improve after a restart of your Mac. If things are still ...

I wish to have rows automatically numbered in column "A" according to the content in column "B". If column "B" has text or numbers in a cell I do not want to number that row in column "A". If column "B" is blank in a cell I want to number that row in column "A". I found the following formula that works the opposite of what I want "=IF(B1<>"",COUNTA($B$1:B1)&".","")" . What would the formula be if I wanted to numerically count the blank cells in "B" and skip the conte...

I attempted to use a combo-box to look-up records on a form. The RowSource query that is generated by the wizard included 2 data fields. I wanted to show only a distinct list of the second, non-key data element (col1). When I changed the RowSource query to SELECT distinct Col1, the combo-box displays nothing. I then created a stored query with the SELECT distinct Col1. They query runs correctly, but when I put in in the RowSource, it displays nothing. Any clue what I could be doing wrong? On Fri, 26 Oct 2007 12:17:03 -0700, JHC wrote: > I attempted to use a combo-box to look-u...

Cell BM6 Sheet1, has a formula that gives me a numeric total. The total belongs in Column D1 Sheet2 after the last entry, and there will be cells that will remain blank in that column before the last entry. Sheet1 is a work sheet, which will be saved with a new file name at the end of the month after the totals have been sent to Sheet2. Sheet2 is a permanent, ongoing record. The numbers on original Sheet1 will then be erased and the worksheet reused for the new month. :confused: How do I get the values into the appropriate row of Sheet2 and have them remain there when I reuse the workshee...

I am using Excel 2003, and here is the problem. I have an Excel sheet that contains financials. Different departments need different sections of the data, so I copy and paste out only those sections they need and paste it into a new Excel sheet. The colums are broken up into dates, and formated to show date-year (Dec-05). For some reason, when I copy and paste sections, the date will change, for example Dec-05 will change to Dec-01. I have to go thru and manually correct all the dates. Can anyone tell me what is causing this? . .. maybe the cells in the New worksheet are preset to a c...

http://microsoft-crm-3.blogspot.com/ ...

Hi I had office 2003 and in the outlook new task menu, i used to fill in details as per my requirement. There was a field named contacts at the bottom of the page, where in i used to give some reference number and save. Once the task is saved it used to appear on the task menu with all other tasks. I had renamed the contacts column as ref no..column. Now I had installed office 2007. There is no filed appearing as contacts as it used to appear in 2003. Hence Im not able to fill my ref no in that field. It may be noted that Im not able to get the required field in the contacts menu avail...

I am working with rather large & detailed spreadsheets. I have hidden rows and columns with supporting details that feed into the viewable results (formulas). I need to take the “zipped up” data and copy/paste to a new worksheet. When I paste the data, it appears with all of the hidden columns and rows visible one again. Is there any way to paste the zipped up data without the reappearance of the previously hidden rows and columns? Can you paste a link without all of the hidden data reappearing? Select range to copy which includes hidden rows and columns. F5>Special...