Mismatch error

Fooku

New Member
Joined
Jun 19, 2008
Messages
5
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
Code:
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

Code:
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

Code:
Sub CHART_START()
 
Call NEW_SHEET(ByVal S)

I would be greatly debted to whoever can help me.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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.
Help said:
ByVal and ByRef can be used with Call only when calling a DLL procedure.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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...
 
Last edited:

Fooku

New Member
Joined
Jun 19, 2008
Messages
5

ADVERTISEMENT

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

New Member
Joined
Jun 19, 2008
Messages
5
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

Well-known Member
Joined
May 22, 2007
Messages
1,035

ADVERTISEMENT

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

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

Sub New_Sheet(s)
 MsgBox (s)
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Makrini

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

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,466
Members
414,069
Latest member
StudExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top