Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default User-defined data type; Error: Only User-defined types...

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default User-defined data type; Error: Only User-defined types...

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" wrote in message
...
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
"User-defined type not defined" message in Excel RW1946 Excel Discussion (Misc queries) 0 August 31st 05 12:14 PM
Unable to add user-defined data types to a collection Adrian[_7_] Excel Programming 3 July 14th 04 08:01 PM
User Defined type not defined Karpagam Excel Programming 1 May 16th 04 05:41 PM
User defined data type augustus108 Excel Programming 1 April 10th 04 05:11 PM
Word.Document - user defined type not defined jowatkins[_7_] Excel Programming 0 January 20th 04 09:46 AM


All times are GMT +1. The time now is 08:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"