CODEWITHSUNDEEP
excelexcel-2010vba
Cimbian
Cimbian

Reputation: 173

VBA pass the name of activesheet to a function

I have created a function to receive three parameters.:

When I compile I am getting a compile error stating:

Compile Error: Expected:=

My call is:

Sub C_Button_ImportBOM_Click()

  Dim strFilePathName As String
  Dim strFileLine As String
  Dim v As Variant
  Dim RowIndex As Long
  Dim mySheet As Worksheet

  ActiveSheet.Name = "Import"

  mySheet = Worksheets("Import")

  strFilePathName = ImportFilePicker

  v = QuickRead(strFilePathName)
  For RowIndex = 0 To UBound(v)
    PopulateNewLine (v(RowIndex), mySheet, RowIndex)
  Next

End Sub

The function declaration is:

Function PopulateNewLine(SourceString As String, ImportSheet As Worksheet, CurrentRow As Long)

I have tried many things to no avail. Initially with just the first argument declared and used this worked okay.

Any ideas greatly appreciated.

Excel 2010 VBA

Upvotes: 1

Views: 13243

Answers (1)

Doug Glancy
Doug Glancy

Reputation: 27478

You are calling your function like a subroutine, i.e., you're not returning a value from it and assigning it to a variable, as you'd normally do with a function. That means you need to call it without the parentheses surrounding the arguments:

PopulateNewLine v(RowIndex), mySheet, RowIndex

If you really aren't returning anything from it, you should probably turn it into a Sub for clarity's sake:

Sub PopulateNewLine(SourceString As String, ImportSheet As Worksheet, CurrentRow As Long)
... your code here
End Sub

On another note, you should qualify your mySheet reference, and as I mentioned in the comments, use Set. It should look something like:

Set mySheet = ActiveWorkbook.Worksheets("Import")

Substitute whatever workbook Import is in for ActiveWorkbook above.

Upvotes: 1

Related Questions