Error when creating a 2-arguments subroutine

[Michael Lam]

Dear all,
I am writing a VBA for an excel application. I have encountered some
problems regarding the multi-parameters subroutine. Here is the simplied
code:

'*******************************************************
'The subroutine
'*******************************************************

Sub LineUpMyCharts(group As Integer, amount As Integer)
Dim MyWidth As Single, MyHeight As Single
Dim NumWide As Long
Dim iChtIx As Integer
Dim iChtCt As Integer

Dim n As Integer

MyWidth = 150
MyHeight = 100
NumWide = 3
n = 0
iChtCt = ActiveSheet.ChartObjects.count
For iChtIx = 1 To iChtCt
With ActiveSheet.ChartObjects(iChtIx)
If Left(.Name, 5) <> "hello" Then
.Width = MyWidth
.Height = MyHeight
.Left = n * MyWidth
.Top = (group Mod 2) * MyHeight
.Name = "hello" & iChtIx
n = n + 1
End If
End With

Next
End Sub

'*******************************************************
'Main Function
'*******************************************************

Dim i, j, k as integer
i = 0
j = 0
k= 0

Sub Macro1()
LineUpMyCharts(i, j)
End Sub



When I started to run it, an error message as shown belows poped out:
Compile Error:
Syntax Error:

Then, I tried to modify the LineUpMyCharts into a function not a subroutine
as shown:

Function LineUpMyCharts(group As Integer, amount As Integer) as integer

When I called the function through the following code:

Dim dummy as integer
dummy = LineUpMyCharts(i, j)

Another error poped out:
Compile Error:
ByRef argument type mismatch

I am not really an expert in programming and I appreciate if you can help
me. Thanks

Michael

 

[Andy Pope]

Hi Michael,

You LineUpMyCharts routine is expecting 2 Integer arguments.

The Dim statement you have used only declares k as an integer.
The variables i and j will be treated as variants.

Dim i, j, k as integer

replace with
Dim i as integer , j as integer , k as integer


Also the use of brackets around the passing arguments is only necessary
if you use the Call statement, assuming LineUpMyCharts is a subroutine.

So,

Call LineUpMyCharts(i, j) ' valid

LineUpMyCharts i, j ' also valid


Cheers
Andy

 

[Michael Lam]

Wow,
Thank you so much!

Michael

Hi Michael,

You LineUpMyCharts routine is expecting 2 Integer arguments.

The Dim statement you have used only declares k as an integer.
The variables i and j will be treated as variants.

Dim i, j, k as integer

replace with
Dim i as integer , j as integer , k as integer


Also the use of brackets around the passing arguments is only necessary
if you use the Call statement, assuming LineUpMyCharts is a subroutine.

So,

Call LineUpMyCharts(i, j) ' valid

LineUpMyCharts i, j ' also valid


Cheers
Andy

 

[George Nicholson]

1) > iChtCt = ActiveSheet.ChartObjects.count
Count properties are generally Long Integers, as this one is. Try:
"Dim iChtCt As Long" instead.

2) > Dim i, j, k as integer
i and j will be Variants, k is the only Integer.
Use "i as Integer, j as Integer, k as integer" or use 3 separate lines
or coerce them by using Cint before you pass them.

3) Changing LineUpMyCharts to a function shouldn't be necessary, and may be
counter productive if you didn't add a line within the function assigning a
return value to the Function name.

I don't know if changing these will make the problems go away, but they will
eliminate them as possible causes.