My VBA code is not shown in the Macro dialog box.

[GN0001]

I have a VBA code to trim the value in the cell:

Function ToNum(X as Variant) As String
Dim A as String
A=Trim(str(x))
ToNum = A
End Function

I inserted this code on the code sheet of sheet 1 and I also inserted a module and write the code there.

I can't see the code in the Macro Dialog box to run the code from there.

Is it because the code is a function?
How should I modify it to be seen in the Macro dialog box?

Thanks in advance for your help.
GN0001

 

[NARAYANK991]

Hi ,

There are two types of procedures in standard code modules ( I am excluding class modules here ) - subroutines and functions.

Subroutines basically perform a set of actions and exit ; they may or may not have parameters passed to them.

Functions perform a set of actions using parameters which have been passed to them , and return a result.

Functions can be further subdivided into standard functions , which can be called from subroutines or other functions , and UDFs ( User Defined Functions ) which are like native Excel functions , in that they need to be used in worksheet cells ; thus a UDF which concatenates the text from a range of cells ( something for which there is no native Excel function ) can be called by the following formula :

=ConCat(A1:A5)

The above formula can be entered in any worksheet cell , and the UDF will return the result in that worksheet cell.

To illustrate with a few examples , suppose we have a subroutine called , JustaMessage , and we code it as :

Public Sub JustaMessage()
          MsgBox "Returning from JustaMessage procedure in " & Application.VBE.ActiveCodePane.CodeModule.Name
End Sub

This same procedure ( subroutine ) can be located in any Sheet section , in the ThisWorkbook section or in a standard code module.

If you click on Macros , you will see the following 3 macros :

JustaMessage -------------------- located in a standard code module

Sheet1.JustaMessage ----------- located in the Sheet1 section

ThisWorkbook.JustaMessage --- located in the ThisWorkbook section

You can run any of them , and you will see the message box with the appropriate message.

When we come to functions , functions will not be displayed in the Macros box , since functions are not meant to be run as standalone procedures ; they are either to be called from subroutines or other functions , or to be used in formulae in Excel worksheet cells.

Narayan

 

[GN0001]

Hi ,

There are two types of procedures in standard code modules ( I am excluding class modules here ) - subroutines and functions.

Subroutines basically perform a set of actions and exit ; they may or may not have parameters passed to them.

Functions perform a set of actions using parameters which have been passed to them , and return a result.

Functions can be further subdivided into standard functions , which can be called from subroutines or other functions , and UDFs ( User Defined Functions ) which are like native Excel functions , in that they need to be used in worksheet cells ; thus a UDF which concatenates the text from a range of cells ( something for which there is no native Excel function ) can be called by the following formula :

=ConCat(A1:A5)

The above formula can be entered in any worksheet cell , and the UDF will return the result in that worksheet cell.

To illustrate with a few examples , suppose we have a subroutine called , JustaMessage , and we code it as :

Public Sub JustaMessage()
          MsgBox "Returning from ConCat procedure in " & Application.VBE.ActiveCodePane.CodeModule.Name
End Sub

This same procedure ( subroutine ) can be located in any Sheet section , in the ThisWorkbook section or in a standard code module.

If you click on Macros , you will see the following 3 macros :

JustaMessage -------------------- located in a standard code module

Sheet1.JustaMessage ----------- located in the Sheet1 section

ThisWorkbook.JustaMessage --- located in the ThisWorkbook section

You can run any of them , and you will see the message box with the appropriate message.

When we come to functions , functions will not be displayed in the Macros box , since functions are not meant to be run as standalone procedures ; they are either to be called from subroutines or other functions , or to be used in formulae in Excel worksheet cells.

Narayan

Narayan,
Thanks a lot for all this. GN0001

 

[GN0001]

Hello Narayn,
I understood why it is not being shown in the list of Macros.

What would be the corresponding function on the spreadsheet for this code?
I enter as =Str(A1), it gives error message as #Name?
I enter as =ToNum(A1), it gives error message as #Name?

I appreciate your response.
I have attached the file to ease my question.
Regards,
GN0001

 

[NARAYANK991]

Hi ,

For you to be able to use a function in a worksheet cell , it has to be in a standard code module ; move your procedure ToNum to a standard code module. It will work.

The problem with =Str(A1) is that there is no procedure anywhere in your file ; after you write this , this will also work , provided you have placed it in a standard code module.

Narayan

 

[GN0001]

Hello Narayan,
I have read your response long time ago, I was thinking about it. Thanks a lot for you response.
From what you say function can only be entered in a standard code module, they can't be entered in: any Sheet section , in the ThisWorkbook section. Would you confirm this?
GN0001