Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"User-defined type not defined" message in Excel | Excel Discussion (Misc queries) | |||
Unable to add user-defined data types to a collection | Excel Programming | |||
User Defined type not defined | Excel Programming | |||
User defined data type | Excel Programming | |||
Word.Document - user defined type not defined | Excel Programming |