CODEWITHSUNDEEP
excelvba
Manish Jain
Manish Jain

Reputation: 1343

How to pass parameter in Macros?

I am calling below function as

Dim SheetName As String
Dim FirstName As String, LastName As String
FirstName = "Patricia "
LastName = "Katts"
SheetName = GetFullName(FirstName, LastName)


Private Function GetFullName$(First As String, Last As String)
Dim FName As String
Sheets.Add
ActiveSheet.Name = _
WorksheetFunction.Text(FName)
FName = First & Last    
GetFullName = FName
End Function

but I got error as Compile Error : Argument Not Optional. What's wrong in this code. I do not understand.

Upvotes: 0

Views: 60

Answers (1)

ProfoundlyOblivious
ProfoundlyOblivious

Reputation: 1485

You are missing a parameter here: WorksheetFunction.Text(Value, Format_Text)

But you don't need the worksheet function, you can use ActiveSheet.Name = fName With the caveat that you change the operation order. It would evaluate to vbNullString in the current order.

This should work fine:

Sheets.Add
FName = First & Last    
ActiveSheet.Name = FName
GetFullName = FName

It is possible you may expect .Textto do something it is not meant to do. You might want to go back to your worksheet and type "=text(" in the formula bar and read the tool tip.

I'm not sure what's going on with the top part of your example. You are receiving the parameter in the function just fine but you aren't passing it from anywhere. Maybe I am just confused by your formatting.

These must be inside a sub, function, or property and cannot be in the open like you have them.

Dim SheetName As String
Dim FirstName As String, LastName As String
FirstName = "Patricia "
LastName = "Katts"
SheetName = GetFullName(FirstName, LastName)

Something like this

Sub PassingToFunction()

Dim SheetName As String
Dim FirstName As String, LastName As String
FirstName = "Patricia "
LastName = "Katts"
SheetName = GetFullName(FirstName, LastName)

End Sub

I literally copied and pasted your example. Made the edits I suggested and it works. 

Sub WorksJustFine()
    Dim SheetName As String
    Dim FirstName As String, LastName As String
    FirstName = "Patricia "
    LastName = "Katts"
    SheetName = GetFullName(FirstName, LastName)
End Sub

Private Function GetFullName$(First As String, Last As String)
    Dim FName As String
    Sheets.Add
    FName = First & Last
    ActiveSheet.Name = FName
    GetFullName = FName
End Function

You're doing something that isn't being explained.

Upvotes: 2

Related Questions