Changing a UDF to something that won't register as a security threat.

[whelanj]

Hey there,

I just spent the last couple of weeks building some spreadsheets usin
user defined functions and between the start (and initial test of th
file sharing) the network security for macros has been set to HIGH
effectively stopping my macros as anyone uses them. The powers that b
decided not to switch them back.

So I have to decide how to change my work (it still needs to be don
very soon) to be useable by people within the office as well as anyon
on the outside.

More or less, I want to have the functionality of customized function
(see sample code below) with the ease of operability of one file for
technologically challenged person.

Can the following VB code be changed into a series of non-threatenin
basic functions recognized by Excel? Or are there other options open t
me? Time is tight, money is nonexistant, and I'm a work term student.

*****************************************

Function OccDensity(Den_rng As Range) As Variant

If Den_rng.Cells.Count > 1 Then
OccDensity = CVErr(xlErrValue)
Exit Function
End If

If Den_rng.Value = "" Then
OccDensity = CVErr(xlErrValue)
Exit Function
End If

If Not IsNumeric(Den_rng.Value) Then
OccDensity = CVErr(xlErrValue)
Exit Function
End If

Select Case Den_rng.Value
Case Is > 15
OccDensity = 5
Case Is > 12
OccDensity = 4
Case Is > 10
OccDensity = 3
Case Is > 8
OccDensity = 2
Case Is > 0
OccDensity = 1
Case Else
OccDensity = CVErr(xlErrValue)
End Select
End Function

******************************************

 

[Frank Kabel]

Hi
try the following
1. Create a lookup table on a separate sheet (e.g. calles 'lookup').
You may hide this sheet afterwards:
A B
1 0 1
2 8 2
3 10 3
.....

2. Lets assume you want to process cell A1 on a separate sheet. Use the
following formula
=IF(AND(A1<>"",ISNUMBER(A1)),INDEX('lookup'!$B$1:$B$10,MATCH(A1-0.00000
1,'lookup'!$A$1:$A$10,1)),"Error")

Note the following differences to your UDF:
- if the number in cell A1 <= 0 the error #NV and not #VALUE is
returned
- There will be a text error message for blank cells or text values
- no check for multiple cell ranges (but you enter the formula
manually, so you have to take care of this)