Byref type mismatch

emorris1000

Registered User.
Local time
Today, 12:43
Joined
Feb 22, 2011
Messages
125
I'm getting a byref type mismatch in some code and can't figure it out:

The bolded part is what's throwing the type mismatch, specifically the "Sec" variable. Its highlighted by the debugger when the mismatch is thrown, but to be honest I think it's the "xlbook" variable throwing the mismatch as its the only variable passed byref:

Code:
Private Sub ReformatTensileData(ByRef xlBook As Excel.Workbook)
 
Dim Lastrow, Maxlength, Sec, Laststop, Datastop, Lngth As Integer
Dim xlTemplateSheet, xlTDSheet, xlMDSheet As Excel.Worksheet
 
 
Set xlTDSheet = xlBook.Worksheets(1)
Set xlMDSheet = xlBook.Worksheets(2)
Set xlTemplateSheet = xlBook.Worksheets.Add
'xlTDSheet.Name = "TDData"
'xlMDSheet.Name = "MDdata"
xlTemplateSheet.Name = "Template"
Lastrow = xlMDSheet.Cells(Rows.Count, "A").End(xlUp).Row
xlMDSheet.Cells(Lastrow + 1, 2).Value = 1
 
Maxlength = 0
Sec = 0
Laststop = 2
Datastop = FindOne(2, xlMDSheet)
Lngth = 0
 
Do
    [B]Call CopyPastaStep1(xlBook, Sec, Laststop, Datastop - 1, 1, 4)[/B]
    Call CopyPastaMD(Sec, Datastop - Laststop, Lngth)
 
    Sec = Sec + 1
    Laststop = Datastop
    Datastop = FindOne(Datastop, xlMDSheet)
 
    If Lngth > Maxlength Then
        Maxlength = Lngth
    End If
 
Loop Until (Sec > 4)
 
 
End Sub

and here's the module it's calling:

Code:
Private Sub CopyPastaStep1(ByRef xlBook As Excel.Workbook, Sec As Integer, SecStart As Integer, SecEnd As Integer, SourceSheetNum As Integer, DestSheetnum As Integer)
 
'Copy TestNum and PointNum
xlBook.Worksheets(SourceSheetNum).Select
Range(Cells(SecStart, 1), Cells(SecEnd, 2)).Copy
xlBook.Worksheets(DestSheetnum).Select
Cells(3, Sec * 5 + 3).Select
xlBook.ActiveSheet.Paste
 
'copy Extension
xlBook.Worksheets(SourceSheetNum).Select
Range(Cells(SecStart, 5), Cells(SecEnd, 5)).Copy
xlBook.Worksheets(DestSheetnum).Select
Cells(3, Sec * 5 + 2).Select
xlBook.ActiveSheet.Paste
 
'Copy Stress
xlBook.Worksheets(SourceSheetNum).Select
Range(Cells(SecStart, 4), Cells(SecEnd, 4)).Copy
xlBook.Worksheets(DestSheetnum).Select
Cells(3, Sec * 5 + 5).Select
xlBook.ActiveSheet.Paste
 
End Sub

So, two things that are weird to me. First, as mentioned above, "sec" isn't passed byref so I don't think it's the one throwing the type mismatch, I think its the "xlbook" variable. Second, in the first bit of code you see some manipulation of the "xlbook" variable (adding a worksheet, changing some names). Those changes don't actually occur. It's like somehow the xlbook variable stops becoming an excel.workbook object (which would explain the type mismatch).

Any thoughts?

edit: Another confusing issue is that I have another sub in this same code that gets passed the workbook object byref right before the "ReformatTensileData" sub gets called and it works fine.

edit2: I'm going to include that other code here as well. This is the first sub that the xlbook gets passed to byref on the off chance that something here is changing the object type from a workbook object to something else:


Code:
Private Sub ExportRecordsetToExcel(tempSQL As String, ByRef xlBook As Excel.Workbook, ByRef xlApp As Excel.Application, SheetName As String)
 
Dim rs As DAO.Recordset
Dim xlSheet As Excel.Worksheet
Dim xlRange As Excel.Range

MsgBox tempSQL
Set rs = CurrentDb.OpenRecordset(tempSQL)
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = SheetName

Set xlRange = xlSheet.Range("A1")
For i = 0 To rs.Fields.Count - 1
    xlRange.Offset(0, i).Value = rs.Fields(i).Name
Next
xlRange.Offset(1, 0).CopyFromRecordset rs
Set rs = Nothing
 
Last edited:
ok....I have a suspcion that this has to do with me explicitly calling the ExportRecordsetToExcel sub when I am passing something to it byref....but I'm not sure. grrrrr

edit: yeah that didn't fix it. oh well
 
Sec is a Variant and you are trying to pass it ByRef to an Integer.

Chris.
 
how did sec get set as a variant? I thought it was declared as an integer in the top thing. Or is that method of dimming not settnig them all as integers?

edit: haha oh wow I just checked that and what a stupid mistake to make! I think I saw that in some code somewhere and I was like "oh man that's a super easy way to declare a bunch of variables" and this was the first thing I used it in....

Lazyness will get me nowhere (or everywhere)
 
Sec is defined without a data type and so becomes a Variant.
The only Integer on that row is Lngth.
(And why is Lngth spelled that way?)

Chris.
 
I just got in the habit a while ago of not spelling any common words with the normal spelling. The amount of times that a field named "Date" has hosed me has made me a little circumspect.
 
Using a naming convention will solve most of those problems:-
Dim intLength As Integer

Chris.
 

Users who are viewing this thread

Back
Top Bottom