User-defined data type; Error: Only User-defined types...

G

Guest

Compile error: Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late bound functions

The following code is called from the Public Sub reMain() procedure. The purpose of which is to count the number of letters in each paragraph (assume that the Word file referred to-- see below-- has already been opened at this point) and then return this value to a paragraph header line that also contains some identifying information and finally dump this data to an excel worksheet and from there it will be further analyzed.

Background Information: As it stands now, this procedure takes 3-4 minutes to execute. I want to reduce this time as much as possible. The overall goal being to analyze the Word document referred to above 3390 times. The Word file contains approx. 3.5 million characters-- without spaces (i.e. the doc is one big paragraph). Each time the program is run it is broken into fragments-- 'paragraphs', based on a particular sequence of characters that are unique each time the program is run.

Problem: I want to create a 'temporary' array, fill it with data (e.g. character count, the starting/ending character sequence number-- which assumes that this first character in the Word doc is #1, and then some identifying information, etc...), and then when the program has finished searching the document, copy the data to an Excel worksheet. I am working with a module contained within the PERSONAL.XLS project folder-- I don't think that is particular relevant but I want to be thorough.

What I Have Done So Far:
1. Created user-defined data type (module level declaration):
Option Explicit
Type FragmentInfo
xlRestrictionFragmentID As String
FragmentStart As Long
FragEnd_dBase As Long
CaraCount As Long
End Type
2. Tested program w/o printing array to worksheet. In the immediate window, I saw the correct data.

So the array has been correctly filled, but when I try to output the data:
With xlNewSheet
.Activate
Set rgOutput = .Range(Cells(2, 1), Cells(NextRowDown, 4))
rgOutput.Value = FragmentArray
End With
I get the error message described above. ANY suggestions would be greatly appreciated. TIA. --tiger_PRM.
Private Sub bpCount()
Dim rgColumnMaxMin As Range
Dim MaxFrag As Long
Dim MinFrag As Long
Dim FragmentStart As Long
Dim FragmentEnd As Long
Dim FragEnd_dBase As Long
Dim CycleNumber As Long
Dim xlPDBRestrictionFragmentID As String
Dim f As Long
Dim rgOutput As Excel.Range
NextRowDown = 1

For Each oPara In gFile.Paragraphs
f = f + 1
CycleNumber = CycleNumber + 1
NextRowDown = NextRowDown + 1
Set rgColumnMaxMin = xlNewSheet.Columns("D")
MaxFrag = WorksheetFunction.Max(rgColumnMaxMin)
MinFrag = WorksheetFunction.Min(rgColumnMaxMin)
With oPara
Set oParaRg = .Range
CaraCount = oParaRg.Characters.Count - 1
Select Case CaraCount
Case Is > 0
Select Case CircularAnswer
Case Is = vbYes
Select Case CycleNumber
Case Is = 1
FragmentStart = CYFPCaraCount + 1
FragmentEnd = CaraCount + FragmentStart
FragEnd_dBase = FragmentEnd - 1
Case Is < ParaCount
FragmentStart = FragmentEnd
FragmentEnd = FragmentEnd + CaraCount
FragEnd_dBase = FragmentEnd - 1
Case Is = ParaCount
FragmentStart = FragmentEnd
FragmentEnd = CYFPCaraCount
FragEnd_dBase = FragmentEnd
End Select
Case Is = vbNo
Select Case CycleNumber
Case Is = 1
FragmentStart = 1
FragmentEnd = FragmentEnd + CaraCount
FragEnd_dBase = FragmentEnd
Case Is > 1
FragmentStart = FragmentEnd
FragmentEnd = FragmentEnd + CaraCount
FragEnd_dBase = FragmentEnd
End Select
End Select
RestrictionFragmentID = RestrictionEnzyme + CStr(CycleNumber) & Chr(59) & _
Chr(32) & CStr(CaraCount) & "bp" & Chr(91) & CStr(FragmentStart) & _
Chr(45) & CStr(FragEnd_dBase) & Chr(93) & Chr(13)
xlRestrictionFragmentID = RestrictionEnzyme + CStr(CycleNumber)
xlPDBRestrictionFragmentID = RestrictionEnzyme + CStr(CycleNumber) & Chr(59) & _
Chr(32) & CStr(CaraCount) & "bp" & Chr(91) & CStr(FragmentStart) & _
Chr(45) & CStr(FragEnd_dBase) & Chr(93)
oParaRg.InsertBefore ">" & CStr(RestrictionFragmentID)
oParaRg.Style = wdStyleNormal
If CaraCount > MaxFrag Then
maxCaraCount = CaraCount
End If
If CaraCount < MinFrag Then
minCaraCount = CaraCount
End If
ReDim Preserve FragmentArray(1 To f) As FragmentInfo
FragmentArray(f).xlRestrictionFragmentID = xlRestrictionFragmentID
Debug.Print FragmentArray(f).xlRestrictionFragmentID
FragmentArray(f).FragmentStart = FragmentStart
Debug.Print FragmentArray(f).FragmentStart
FragmentArray(f).FragEnd_dBase = FragEnd_dBase
Debug.Print FragmentArray(f).FragEnd_dBase
FragmentArray(f).CaraCount = CaraCount
Debug.Print FragmentArray(f).CaraCount
' With xlNewSheet
' .Activate
' Cells(NextRowDown, 1) = xlRestrictionFragmentID
' Cells(NextRowDown, 1).HorizontalAlignment = xlLeft
' Cells(NextRowDown, 2) = FragmentStart
' Cells(NextRowDown, 4).HorizontalAlignment = xlCenter
' Cells(NextRowDown, 3) = FragEnd_dBase
' Cells(NextRowDown, 4).HorizontalAlignment = xlCenter
' Cells(NextRowDown, 4) = CaraCount
' Cells(NextRowDown, 4).HorizontalAlignment = xlCenter
' End With
Call SearchProteindBase(ByVal FragmentStart, FragmentEnd, xlPDBRestrictionFragmentID, CycleNumber)
Case Is = 0
oParaRg.Delete
End Select
End With
Next oPara
With xlNewSheet
.Activate
Set rgOutput = .Range(Cells(2, 1), Cells(NextRowDown, 4))
rgOutput.Value = FragmentArray
End With
End Sub
 
T

Tom Ogilvy

You need to skip the User Defined type and use a two dimensional array if
you want to use the line of code that is giving you problems.

--
Regards,
Tom Ogilvy

tiger_PRM said:
Compile error: Only user-defined types defined in public object modules
Click to expand...
can be coerced to or from a variant or passed to late bound functions
The following code is called from the Public Sub reMain() procedure. The
Click to expand...
purpose of which is to count the number of letters in each paragraph (assume
that the Word file referred to-- see below-- has already been opened at this
point) and then return this value to a paragraph header line that also
contains some identifying information and finally dump this data to an excel
worksheet and from there it will be further analyzed.
Background Information: As it stands now, this procedure takes 3-4
Click to expand...
minutes to execute. I want to reduce this time as much as possible. The
overall goal being to analyze the Word document referred to above 3390
times. The Word file contains approx. 3.5 million characters-- without
spaces (i.e. the doc is one big paragraph). Each time the program is run it
is broken into fragments-- 'paragraphs', based on a particular sequence of
characters that are unique each time the program is run.
Problem: I want to create a 'temporary' array, fill it with data (e.g.
Click to expand...
character count, the starting/ending character sequence number-- which
assumes that this first character in the Word doc is #1, and then some
identifying information, etc...), and then when the program has finished
searching the document, copy the data to an Excel worksheet. I am working
with a module contained within the PERSONAL.XLS project folder-- I don't
think that is particular relevant but I want to be thorough.
What I Have Done So Far:
1. Created user-defined data type (module level declaration):
Option Explicit
Type FragmentInfo
xlRestrictionFragmentID As String
FragmentStart As Long
FragEnd_dBase As Long
CaraCount As Long
End Type
2. Tested program w/o printing array to worksheet. In the immediate
Click to expand...
window, I saw the correct data.
So the array has been correctly filled, but when I try to output the data:
With xlNewSheet
.Activate
Set rgOutput = .Range(Cells(2, 1), Cells(NextRowDown, 4))
rgOutput.Value = FragmentArray
End With
I get the error message described above. ANY suggestions would be greatly
Click to expand...
appreciated. TIA. --tiger_PRM.
Private Sub bpCount()
Dim rgColumnMaxMin As Range
Dim MaxFrag As Long
Dim MinFrag As Long
Dim FragmentStart As Long
Dim FragmentEnd As Long
Dim FragEnd_dBase As Long
Dim CycleNumber As Long
Dim xlPDBRestrictionFragmentID As String
Dim f As Long
Dim rgOutput As Excel.Range
NextRowDown = 1

For Each oPara In gFile.Paragraphs
f = f + 1
CycleNumber = CycleNumber + 1
NextRowDown = NextRowDown + 1
Set rgColumnMaxMin = xlNewSheet.Columns("D")
MaxFrag = WorksheetFunction.Max(rgColumnMaxMin)
MinFrag = WorksheetFunction.Min(rgColumnMaxMin)
With oPara
Set oParaRg = .Range
CaraCount = oParaRg.Characters.Count - 1
Select Case CaraCount
Case Is > 0
Select Case CircularAnswer
Case Is = vbYes
Select Case CycleNumber
Case Is = 1
FragmentStart = CYFPCaraCount + 1
FragmentEnd = CaraCount + FragmentStart
FragEnd_dBase = FragmentEnd - 1
Case Is < ParaCount
FragmentStart = FragmentEnd
FragmentEnd = FragmentEnd + CaraCount
FragEnd_dBase = FragmentEnd - 1
Case Is = ParaCount
FragmentStart = FragmentEnd
FragmentEnd = CYFPCaraCount
FragEnd_dBase = FragmentEnd
End Select
Case Is = vbNo
Select Case CycleNumber
Case Is = 1
FragmentStart = 1
FragmentEnd = FragmentEnd + CaraCount
FragEnd_dBase = FragmentEnd
Case Is > 1
FragmentStart = FragmentEnd
FragmentEnd = FragmentEnd + CaraCount
FragEnd_dBase = FragmentEnd
End Select
End Select
RestrictionFragmentID = RestrictionEnzyme + CStr(CycleNumber) & Chr(59) & _
Chr(32) & CStr(CaraCount) & "bp" & Chr(91) & CStr(FragmentStart) & _
Chr(45) & CStr(FragEnd_dBase) & Chr(93) & Chr(13)
xlRestrictionFragmentID = RestrictionEnzyme + CStr(CycleNumber)
xlPDBRestrictionFragmentID = RestrictionEnzyme +
Click to expand...
CStr(CycleNumber) & Chr(59) & _
Chr(32) & CStr(CaraCount) & "bp" & Chr(91) & CStr(FragmentStart) & _
Chr(45) & CStr(FragEnd_dBase) & Chr(93)
oParaRg.InsertBefore ">" & CStr(RestrictionFragmentID)
oParaRg.Style = wdStyleNormal
If CaraCount > MaxFrag Then
maxCaraCount = CaraCount
End If
If CaraCount < MinFrag Then
minCaraCount = CaraCount
End If
ReDim Preserve FragmentArray(1 To f) As FragmentInfo
FragmentArray(f).xlRestrictionFragmentID = xlRestrictionFragmentID
Debug.Print FragmentArray(f).xlRestrictionFragmentID
FragmentArray(f).FragmentStart = FragmentStart
Debug.Print FragmentArray(f).FragmentStart
FragmentArray(f).FragEnd_dBase = FragEnd_dBase
Debug.Print FragmentArray(f).FragEnd_dBase
FragmentArray(f).CaraCount = CaraCount
Debug.Print FragmentArray(f).CaraCount
' With xlNewSheet
' .Activate
' Cells(NextRowDown, 1) = xlRestrictionFragmentID
' Cells(NextRowDown, 1).HorizontalAlignment = xlLeft
' Cells(NextRowDown, 2) = FragmentStart
' Cells(NextRowDown, 4).HorizontalAlignment = xlCenter
' Cells(NextRowDown, 3) = FragEnd_dBase
' Cells(NextRowDown, 4).HorizontalAlignment = xlCenter
' Cells(NextRowDown, 4) = CaraCount
' Cells(NextRowDown, 4).HorizontalAlignment = xlCenter
' End With
Call SearchProteindBase(ByVal FragmentStart, FragmentEnd,
Click to expand...
xlPDBRestrictionFragmentID, CycleNumber)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

user defined type not defined issue 2
Compile error: User-defined type not defined 3
Excel Target.column for Data Validation would like to target some cells within column 1
Application defined or object defined error 2
User defined data type (losing its data) 2
doesn't read all data from cells into xml file 2
Application-defined or object -defined error 1
Wrong data type returned from user-defined function 9

Top