VBA Code not running

[kungfujam]

Completely stumped as to why my code will no longer run. It definitely works as I can call it from another Sub but will not run on its own. When I am in the code editing window (in Excel) and I click the run button or f5 it asks me which macro to run, my macro is not selectable...help?! It's very annoying having to run it from another macro!!!

Option Explicit

Sub Guess_Email(Optional ByRef FirstNmCol As Long, _
    Optional ByRef LastNmCol As Long, _
    Optional ByRef EmailCol As Long, _
    Optional ByRef AccountNmCol As Long)
    
'
'Guess Email Macro
'Macro designed 25/08/2011 by [e-mail]
'

    ''''''''''''
    'Declaring Variables
    ''''''''''''
    
    Dim TheAtpos As Long
    
    Dim SelectedCell As Range
    Dim EmailCell As Range
    Dim UsrSlct As Range
    
    Dim FirstName As String
    Dim SecondName As String
    Dim Email As String
    Dim Company As String
    
    Set SelectedCell = ActiveCell
    
    
    'this enables this macro to be used standalone or called from another sub
    'am planning to write another sub to loop this process and apply to an entire sheet
    If AccountNmCol = 0 Then
        Set UsrSlct = Application.InputBox(prompt:= _
        "Select the first name column header cell", Type:=8)
        FirstNmCol = UsrSlct.Column
    
        Set UsrSlct = Application.InputBox(prompt:= _
        "Select the last name column header cell", Type:=8)
        LastNmCol = UsrSlct.Column
    
        Set UsrSlct = Application.InputBox(prompt:= _
        "Select the email column header cell", Type:=8)
        EmailCol = UsrSlct.Column
    
        Set UsrSlct = Application.InputBox(prompt:= _
        "Select the account name column header cell", Type:=8)
        AccountNmCol = UsrSlct.Column
        
    End If
     
    'Define user's details
    FirstName = Cells(SelectedCell.Row, FirstNmCol).Value
    SecondName = Cells(SelectedCell.Row, LastNmCol).Value
    Company = Cells(SelectedCell.Row, AccountNmCol).Value
     
    ''''''''''
    'Search for a email of someone in same company below
    ''''''''''
    Set EmailCell = SelectedCell.Offset(1, 0)
    
    
    If EmailCell = "" Then
        MsgBox "No good email match found... :("
    End If
    
    'Find the @
    TheAtpos = InStr(1, EmailCell.Value, "@", 1)

    
    Email = EmailCell.Characters(TheAtpos + _
    1, (Len(EmailCell) - TheAtpos)).Text

    SelectedCell.FormulaR1C1 = FirstName & "." & SecondName & "@" & Email
        
End Sub

[kungfujam]

Though I have not solved the problem, I've worked round it. Before I worked round it I narrowed it down to being a problem with declaring the optional arguaments in the Sub statement:

Sub Guess_Email(Optional ByRef FirstNmCol As Long, _
Optional ByRef LastNmCol As Long, _
Optional ByRef EmailCol As Long, _
Optional ByRef AccountNmCol As Long)

The way I alleviated the problem was making the variables global to the workbook and just running using

Sub Guess_Email()
...stuff...
End Sub

and calling Guess_Email in my other Module. Still interested if anyone knows wherin my problem lies

[kungfujam]

I've now solved this! Silly misunderstanding to how this worked and seems obvious now. When you make a sub or function take arguaments you need to supply it with some arguaments before it will run. I guess you just need to set up a test sub that runs your sub with some arguaments in order to test it.