'Application.ThisCell" equivelent for VBA 2000?

gws_byte · January 31st, 2006, 07:56 AM

Hi Guys,

I'm trying to transfer some code i wrote on my 2003 XL to a machine running XL2K, everything works EXCEPT the Application.ThisCell part, does anyone have an idea how to do the same thing in 2K? (Code follows - Mileage is a cell with a True/False value)

Thanks

Function OdometerDiff(Mileage)
If Mileage Then
    If Application.ThisCell.Offset(-1, 0) = "" Then
    OdometerDiff = Application.ThisCell.Offset(0, -1) - Application.ThisCell.Offset(-2, -1)
    Else: OdometerDiff = Application.ThisCell.Offset(0, -1) - Application.ThisCell.Offset(-1, -1)
    End If
Else: OdometerDiff = ""
End If

End Function

Dave - MAD

nil illegitimi carborundum

Semper in excreta, sumus solum profundum variat

gws_byte · January 31st, 2006, 08:12 AM

OOPS!!

Re-thought the problem and used a formula instead

I would still be interested to hear if anyone knows the VBA answer though...

Thanks

Dave - MAD

nil illegitimi carborundum

Semper in excreta, sumus solum profundum variat

maccas · January 31st, 2006, 08:44 AM

Dave,

I think the ThisCell Property was only added to Excel at XL2003 so no backwards compatability I'm afraid.

I know this because historically it was nigh on impossible to replicate the same functionality. You can see why they restricted it because if your UDF tried to amend the value of the currently calculating cell (containing the UDF) then nasty things happen to Excel's calculation flow. Still it was mighty annoying for those of us that could be trusted not to crash Excel and wanted to do something useful.

The VBA answer to your problem would be a little tedious - you'd need to pass a reference to the cell next to the UDF cell as an additional argument to the function. You could then use this object to check for blank nieghbouring cells etc.

Maccas

gws_byte · January 31st, 2006, 09:17 AM

Hey Maccas,

Thanks for the quick reply! I was afraid of something like that...

I achieved the same result with a nested 'If' function, makes me wonder how useful UDF's were in 2K in a scenario where you wanted to use predictable data patterns arrayed around a formula cell, where the formula cells adress was not fixed...? Oh well...

Dave - MAD

nil illegitimi carborundum

Semper in excreta, sumus solum profundum variat