VBA function is not "seen"

[pshacham]

Hi
I wrote a VBA function, itworks fine in the excel sheet. However, I wanted to combine
this function in another Sub I wrote (the same editor of course). When I type (in the VBA editor) the command WorkSheetFunction.
a window is opened, from which I can choose a function from all the list shown there but my function
is not there , in contrast to all other excel built-in functions. Of course my file is saved under the extension xlsm (enabled macros).
This is strange, since as I mentioned, in the sheet I see and use this function.

Pazi

 

[Hui]

Pazi

Can you post your file?

 

[pshacham]

Pazi

Can you post your file?

Yes. Note that the file name is in Hebrew so It shall look to you strange... However, if you look at C5 for example. The function
I wrote is called VisibleSpectrum, it returns the wave length (In hebrew) as a string, in response to the input which is the
wave length in number. You can see this simple function in the VAB

Pazi

 

[Marc L]

Hi,​

​

as you have created your own function aka an UDF (user defined function) so it is very not aa Excel inner function​

then it's normal you can't see it within the VBA WorksheetFunction statement …​

​

So in your VBA procedure just call its name without this specific statement like V = VisibleSpectrun(566) as any VBA function …​

​

 

[pshacham]

Thanks.
Is there a way to define my own function which shall be like inner and be part of the list?

 

[Marc L]

No as it's very not an Excel built-in function but just a VBA function …​

​

 

[Hui]

Pazi

on your worksheet you can call VisibleSpectrun simply
ie: in I11: =VisibleSpectrun(G11)
it returns violet ñâåì

To use the function in another subroutine or function simply use as an example i rewrote your VisibleSpectrumValue sub

Sub VisibleSpectrumValue()

   Dim c As Range
  
   For Each c In Range("WaveLengths")
       c.Offset(0, 1) = VisibleSpectrun(c.Value)
   Next

End Sub

You may be confused as there maybe a spelling mistake as your function is called VisibleSpectrun not VisibleSpectrum

 

[pshacham]

Pazi

on your worksheet you can call VisibleSpectrun simply
ie: in I11: =VisibleSpectrun(G11)
it returns violet ñâåì

To use the function in another subroutine or function simply use as an example i rewrote your VisibleSpectrumValue sub

Sub VisibleSpectrumValue()

   Dim c As Range
 
   For Each c In Range("WaveLengths")
       c.Offset(0, 1) = VisibleSpectrun(c.Value)
   Next

End Sub

You may be confused as there maybe a spelling mistake as your function is called VisibleSpectrun not VisibleSpectrum

Yes, I wrote something very similar ,
Thanks.