Excel: links to UDF on other page go blank during save

R

Richards

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?
 
R

Richards

Frank Kabel said:
Hi
you may post your UDF code
Click to expand...

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
 
F

Frank Kabel

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
 
R

Richards

Frank Kabel said:
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
Click to expand...
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 .)
 
F

Frank Kabel

[...]
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?
Click to expand...

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.
Click to expand...

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 .)
Click to expand...

as stated could also be achieved with a worksheet formula :)

Frank
 
Top