Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel: links to UDF on other page go blank during save
I have a spreadsheet that has several cells that have links to a cell located
in a different, (i.e. ='Sheet 3'!AC8). The cell on the different sheet calls a user defined function. When I'm in the first sheet and save the file, those cells that link back to sheet 3 go to zeroes. If I display sheet 3 and then redisplay sheet 1, the cells will contain the correct values, otherwise they will contain zeroes. My UDF called from sheet 3, has Application.Volatile, all Office and OS maint is up to date. Anybody have any guesses as to what I'm doing wrong or where to look? |
#2
|
|||
|
|||
Hi
you may post your UDF code -- Regards Frank Kabel Frankfurt, Germany Richards wrote: I have a spreadsheet that has several cells that have links to a cell located in a different, (i.e. ='Sheet 3'!AC8). The cell on the different sheet calls a user defined function. When I'm in the first sheet and save the file, those cells that link back to sheet 3 go to zeroes. If I display sheet 3 and then redisplay sheet 1, the cells will contain the correct values, otherwise they will contain zeroes. My UDF called from sheet 3, has Application.Volatile, all Office and OS maint is up to date. Anybody have any guesses as to what I'm doing wrong or where to look? |
#3
|
|||
|
|||
"Frank Kabel" wrote: Hi you may post your UDF code -- Regards Frank Kabel Frankfurt, Germany Richards wrote: I have a spreadsheet that has several cells that have links to a cell located in a different, (i.e. ='Sheet 3'!AC8). The cell on the different sheet calls a user defined function. When I'm in the first sheet and save the file, those cells that link back to sheet 3 go to zeroes. If I display sheet 3 and then redisplay sheet 1, the cells will contain the correct values, otherwise they will contain zeroes. My UDF called from sheet 3, has Application.Volatile, all Office and OS maint is up to date. Anybody have any guesses as to what I'm doing wrong or where to look? The code for the UDF - please be gentle - this is part of my first VBA code Public Function ColumnCountIf(searchColRng As Range, compColRng As Range, compStr As String) As Integer Application.Volatile On Error GoTo erh Dim searchColStr As String Dim compColStr As String Dim col As Integer Dim row As Integer Dim compCol As Integer Dim compRow As Integer Dim count As Integer count = 0 searchColStr = searchColRng.Address(RowAbsolute:=False, ColumnAbsolute:=False) compColStr = compColRng.Address(RowAbsolute:=False, ColumnAbsolute:=False) col = getCellCol(searchColStr) row = getCellRow(searchColStr) Dim temp As String compCol = getCellCol(compColStr) compRow = getCellRow(compColStr) While IsEmpty(Cells(row, col)) = False temp = Cells(compRow, compCol).Value If UCase(Cells(compRow, compCol).Value) = UCase(compStr) Then count = count + 1 End If row = row + 1 compRow = compRow + 1 Wend ColumnCountIf = count Exit Function erh: ColumnCountIf = CVErr(xlErrValue) End Function |
#4
|
|||
|
|||
Hi
I'd guess that your problem is at least in the following lines of code: ---------- col = getCellCol(searchColStr) row = getCellRow(searchColStr) Dim temp As String compCol = getCellCol(compColStr) compRow = getCellRow(compColStr) While IsEmpty(Cells(row, col)) = False temp = Cells(compRow, compCol).Value ---------- Not sure what getCellCol and getCellRow do (and why you use them at all) but you're using in the following lines Cells without specifying the worksheet object. So this takes the values from your CURRENT ACTIVE sheet. So you may use searchColRng.parent.cells(....) to use the right worksheet object. Also I'm not sure what you're trying to do with this but I'd guess you could achieve the same with a worksheet formula -- Regards Frank Kabel Frankfurt, Germany Richards wrote: "Frank Kabel" wrote: Hi you may post your UDF code -- Regards Frank Kabel Frankfurt, Germany |
#5
|
|||
|
|||
"Frank Kabel" wrote: Hi I'd guess that your problem is at least in the following lines of code: ---------- col = getCellCol(searchColStr) row = getCellRow(searchColStr) Dim temp As String compCol = getCellCol(compColStr) compRow = getCellRow(compColStr) While IsEmpty(Cells(row, col)) = False temp = Cells(compRow, compCol).Value ---------- Not sure what getCellCol and getCellRow do (and why you use them at all) but you're using in the following lines Cells without specifying the worksheet object. So this takes the values from your CURRENT ACTIVE sheet. So you may use searchColRng.parent.cells(....) to use the right worksheet object. Also I'm not sure what you're trying to do with this but I'd guess you could achieve the same with a worksheet formula -- Regards Frank Kabel Frankfurt, Germany Richards wrote: "Frank Kabel" wrote: Hi you may post your UDF code -- Regards Frank Kabel Frankfurt, Germany Frank, That was it. I added the "sheet " qualifier and that "Fixed " my problem. Guess I should add the name of the workbook also as there may be times when I could have this workbook open at the same time as another workbook is loaded. Question: What is the normal method for calling a UDF? Do you pass the name of the workbook and sheet in the parms along with the Cell or Range? (getCellCol and getCellRow accept a string format rference to a single cell , (like "A1"), and return an intger representing the column/ row . Handy if you want to loop through a range. The entire function returns the number of entries in a column that match the supplied string, starting at location searchColRng, and going down until it encounters a blank cell .) |
#6
|
|||
|
|||
[...]
That was it. I added the "sheet " qualifier and that "Fixed " my problem. Guess I should add the name of the workbook also as there may be times when I could have this workbook open at the same time as another workbook is loaded. Question: What is the normal method for calling a UDF? Do you pass the name of the workbook and sheet in the parms along with the Cell or Range? Personally I'd use range.parent for this (if you really need it). But in your case I don't think this is necessary. Why not use something like sub foo(rng as range) dim cell as range for each cell in range 'now use the cell range object next (getCellCol and getCellRow accept a string format rference to a single cell , (like "A1"), and return an intger representing the column/ row . Handy if you want to loop through a range. Why not something like sub foo(rng as range) msgbox rng.cells(1,1).row msgbox rng.cells(1,1).column The entire function returns the number of entries in a column that match the supplied string, starting at location searchColRng, and going down until it encounters a blank cell .) as stated could also be achieved with a worksheet formula :-) Frank |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel crashing on save as | Excel Discussion (Misc queries) | |||
How do I Break links Using Excel 2000 | Excel Discussion (Misc queries) | |||
Getting Excel 2003 to save Custom Toolbars | Excel Discussion (Misc queries) | |||
How do I save a record from an excel template to a database in a . | Excel Discussion (Misc queries) | |||
No Smart Tag help: just a blank "MS Excel Help" window - Excel 2003 | Excel Discussion (Misc queries) |