Function Spreadsheet check not working

[ReinhardtSteyn]

Good Day

I created a test spreadsheet in order to test coding to determine if spreadsheet name exists. The following coding was used:

'Inputs:
'WhatSheet - String Name of Sheet (ex "Sheet1")
' WhatRange (Optional, Default = "A1") - String Name of Range (ex "A1")
Function RangeExists(WhatSheet As String, Optional ByVal WhatRange As String = "A1") As Boolean
    'WhatSheet = ActiveWorkbook.Sheets("SHEET5").Range("a" & i1)
    Dim test As Range
    On Error Resume Next
    Set test = ActiveWorkbook.Sheets(WhatSheet).Range(WhatRange)
    RangeExists = Err.Number = 0
    On Error GoTo 0
End Function

Sub CheckSheetexists()
Dim i1 As Integer
Dim WhatSheet As String

For i1 = 7 To 12
WhatSheet = ActiveWorkbook.Sheets("SHEET5").Range("E" & i1)
ActiveWorkbook.Sheets("SHEET5").Range("F" & i1).Formula = "=RangeExists(E" & i1 & ")"


Next i1

End Sub

Following results was returned which is correct.

When I apply this same coding to the spreadsheet intendent to following results are returned:

I'm not sure why this is happening. When I use the F8 in the VBA to perform the run by line to check the coding, I noted that the function is not triggered. The function is perfectly triggered in my test spreadsheet.

The intendent spreadsheet is saved in ".xlsb" format. Even tried saving the spreadsheet in a micro enabled format, but still the function is not triggered.

Help will be much appreciated

 

[jasonb75]

If I remember correctly, you need to include the filename in the formula, e.g.

=[Personal.xlsb]!RangeExists(A1)

 

[RoryA]

Did you put the function code in the workbook where it is not working? If so, where (i.e. in which module)? It needs to be in a normal module. If it's not in that workbook, then as Jason said, you need to include the name of the workbook that does have the code in it.

 

[ReinhardtSteyn]

Thank you so much for the help.

Just to give you guys some idea of what I'm doing. I created a macro to import employee information from another workbook, but required the function to work in this other workbook. Did not realize I had to add a separate module in the other workbook. I created a new module in the separate workbook and added the function coding in that workbook. Works perfectly now.

I still new at macro so still learning how macros work.