Mismatch error

[Fooku]

Hello. I am fairly new to VB programming, and find it rather more difficult than C. Appreciate whoever can help me.

I am making a template for a family owned engineering firm, which calculates winloads on specific products within the city of FT Lauderdale. Everything had been working until I tried to pass a variable to another sub. Here are the two subs:

This sub determines whether a new sheet needs to be created

Sub CHART_START()
Dim CT, PNC, PN, CT3, C3, R3, NS, S, C As Integer
CT = 0
PN = Cells(5, 2)
C = 6
Do While CT <= PN
PNC = PNC + Cells(CT + 7, C)
COUNT = COUNT + 1
Loop
CT3 = 1
ROW3 = 11
COLUMN3 = 1
NS = 13
S = 5
Do While CT3 <= PNC
    If CT3 > NS Then
        Call NEW_SHEET(ByVal S)
        S = S + 1
        NS = NS + 13
    End If
    Sheets(S).Select
    Cells(R, C) = CT3
    CT3 = CT3 + 1
    R = R + 2
Loop
End Sub

and this sub creates the new sheet

Sub NEW_SHEET(S As Integer)
    Sheets("BLANK").Select
    Sheets("BLANK").Copy After:=Sheets(S)
    Sheets(S + 1).Select
    Sheets(S + 1).Name = "CHART " & S - 5
End Sub

When I compile the cursor stops and highlights the S in the second line and highlights in yellow the first line. It then gives the "COMPILE ERROR: TYPE MISMATCH" message box

Sub CHART_START()
 
Call NEW_SHEET(ByVal S)

I would be greatly debted to whoever can help me.

 

[Makrini]

Call New_Sheet(s)

- the "byval" is causing your issue

 

[Norie]

The only variable in CHART_START that's an actual integer is C.

All the other variables are variants.

And as far as I'm aware you don't use ByVal with Call.

ByVal and ByRef can be used with Call only when calling a DLL procedure.

 

[Jonmo1]

Welcome to the board...

I think the problem lies in how you've dimmed your variables...

Dim CT, PNC, PN, CT3, C3, R3, NS, S, C As Integer

Surprisingly, that is not declaring all variabels as integer, only C is getting Dimmed as Integer..

The way you wrote it would be the same as doing it this way
Dim CT
Dim PNC
Dim PN
Dim CT3
Dim C3
Dim R3
Dim NS
Dim S
Dim C as Integer

And when the AS is left out of the dim statement, it's defaulted to Variant.

So S got dimmed As Variant

And since your sub New_Sheet has S dimmed as Integer, it's a mismatch..


So you have to Dim Each Variable AS something specificlly, or it defaults to Variant...

so

Dim CT As Integer, PNC As Integer, PN As Integer, CT3 As Integer, C3 As Integer
Dim R3 As Integer, NS As Integer, S As Integer, C As Integer


Hope that helps...

 

[Fooku]

Call New_Sheet(s)

- the "byval" is causing your issue

If I remove the byval I get the error message
"COMPILE ERROR: BYREF ARGUMENT TYPE MISMATCH"

 

[Fooku]

Welcome to the board...

I think the problem lies in how you've dimmed your variables...

Dim CT, PNC, PN, CT3, C3, R3, NS, S, C As Integer

Surprisingly, that is not declaring all variabels as integer, only C is getting Dimmed as Integer..

The way you wrote it would be the same as doing it this way
Dim CT
Dim PNC
Dim PN
Dim CT3
Dim C3
Dim R3
Dim NS
Dim S
Dim C as Integer

And when the AS is left out of the dim statement, it's defaulted to Variant.

So S got dimmed As Variant

And since your sub New_Sheet has S dimmed as Integer, it's a mismatch..


So you have to Dim Each Variable AS something specificlly, or it's Variant...

so

Dim CT As Integer, PNC As Integer, PN As Integer, CT3 As Integer, C3 As Integer
Dim R3 As Integer, NS As Integer, S As Integer, C As Integer


Hope that helps...

Thank You So much. I am used to working in C where you can list. 1000karma points to your life. If I could I would by you a beer. Thank You so much once again.

 

[Makrini]

I'm not sure what is going on... the below works perfect for me.. Excel 2003

Sub tester()
Dim s As Integer
s = 5
   Call New_Sheet(s)
   
End Sub

Sub New_Sheet(s)
 MsgBox (s)
End Sub

 

[Norie]

Makrini

Your code works because the data type of the parameter s isn't specified in the New_Sheet sub.

 

[Makrini]

lol - ofc - been a long night

 

[Jonmo1]

I am used to working in C where you can list. 1000karma points to your life

It would just make more sense to be able to do it in one line like that, Even for someone like me who has no experience at all with C. I don't know why they programmed it that way...