|
Array of UDT
I changed it as suggested. It gives the error:
Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions. "Tushar Mehta" wrote: What version of XL / VBA are you using? Does the compiler highlight a particular line as the offending line? At the very least you should change the return type of the function from readers() to reader() -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have defined the following: Type reader r_sname As String r_name As String r_restel As String r_offtel As String r_mobile As String End Type and am trying to use it in the following function: Function GetReaderRecordsArray(rngTimeCell As Range) As readers() Dim rngCommRng As Range Dim u_readers() As reader Dim bCommArr() As Boolean Dim i As Integer Dim j As Integer Dim c_comm As Integer Set rngCommRng = GetCommitRng(rngTimeCell) bCommArr = GetCommFromCommitRng(rngCommRng) c_comm = GetYesCountFromCommRng(bCommArr) If c_comm 0 Then ReDim u_readers(c_comm) i = 0 For j = LBound(bCommArr) To UBound(bCommArr) If bCommArr(j) Then i = i + 1 u_readers(i) = GetReaderRecord(rngCommRng.Cells(j, 1)) Else End If Next j End If GetReaderRecordsArray = u_readers End Function I get a compile error msg. Can anyone help me fix this? |
Array of UDT
You still haven't answered the first question.
Also, where -- what kind of module(s), that is -- do you have all this code? And, did you write the code or did you get it from somewhere else? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I changed it as suggested. It gives the error: Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions. "Tushar Mehta" wrote: What version of XL / VBA are you using? Does the compiler highlight a particular line as the offending line? At the very least you should change the return type of the function from readers() to reader() -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have defined the following: Type reader r_sname As String r_name As String r_restel As String r_offtel As String r_mobile As String End Type and am trying to use it in the following function: Function GetReaderRecordsArray(rngTimeCell As Range) As readers() Dim rngCommRng As Range Dim u_readers() As reader Dim bCommArr() As Boolean Dim i As Integer Dim j As Integer Dim c_comm As Integer Set rngCommRng = GetCommitRng(rngTimeCell) bCommArr = GetCommFromCommitRng(rngCommRng) c_comm = GetYesCountFromCommRng(bCommArr) If c_comm 0 Then ReDim u_readers(c_comm) i = 0 For j = LBound(bCommArr) To UBound(bCommArr) If bCommArr(j) Then i = i + 1 u_readers(i) = GetReaderRecord(rngCommRng.Cells(j, 1)) Else End If Next j End If GetReaderRecordsArray = u_readers End Function I get a compile error msg. Can anyone help me fix this? |
Array of UDT
Hi,
This revised and shortened code works for me. Apart from the return type reader instead of readers, which others have pointed out. I guess the main problem is with the declaration of the variable in the calling routine. In this case the xyz array. You do not show this code by either you have declared it as variant or not declared it's type so it is defaulting to variant. Type reader r_sname As String r_name As String r_restel As String r_offtel As String r_mobile As String End Type Sub Test() Dim xyz() As reader xyz = GetReaderRecordsArray(Range("A1")) Debug.Print xyz(1).r_name Debug.Print xyz(10).r_name End Sub Function GetReaderRecordsArray(rngTimeCell As Range) As reader() Dim u_readers() As reader ReDim u_readers(10) u_readers(1).r_name = "Test1" u_readers(10).r_name = "Test10" GetReaderRecordsArray = u_readers End Function Cheers Andy justahelper wrote: XL version - 2003 (11.5612.5606) Part of Microsoft Office Professional Edition 2003 VBA - Micorsoft Visual Basic 6.3 The function is in a normal module. I have written this piece of code. I also have the function for GetReaderRecord in the same module. If I change the return value to just reader and not reader() and assign the return value as GetReaderRecordsArray = u_readers(1) the function works fine and returns the correct value. Also in break mode I can see not all the items in the array. Question is - why does it fail when I change the return value to an array? "Tushar Mehta" wrote: You still haven't answered the first question. Also, where -- what kind of module(s), that is -- do you have all this code? And, did you write the code or did you get it from somewhere else? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I changed it as suggested. It gives the error: Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions. "Tushar Mehta" wrote: What version of XL / VBA are you using? Does the compiler highlight a particular line as the offending line? At the very least you should change the return type of the function from readers() to reader() -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have defined the following: Type reader r_sname As String r_name As String r_restel As String r_offtel As String r_mobile As String End Type and am trying to use it in the following function: Function GetReaderRecordsArray(rngTimeCell As Range) As readers() Dim rngCommRng As Range Dim u_readers() As reader Dim bCommArr() As Boolean Dim i As Integer Dim j As Integer Dim c_comm As Integer Set rngCommRng = GetCommitRng(rngTimeCell) bCommArr = GetCommFromCommitRng(rngCommRng) c_comm = GetYesCountFromCommRng(bCommArr) If c_comm 0 Then ReDim u_readers(c_comm) i = 0 For j = LBound(bCommArr) To UBound(bCommArr) If bCommArr(j) Then i = i + 1 u_readers(i) = GetReaderRecord(rngCommRng.Cells(j, 1)) Else End If Next j End If GetReaderRecordsArray = u_readers End Function I get a compile error msg. Can anyone help me fix this? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Array of UDT
Did you, and if so how, define the return variable as being an array
within the immediate window? Cheers Andy justahelper wrote: Hi Andy, That's strange. I spent hours looking at the result in the immediate window and could not figure out why it didn't work. I never bothered to try calling it from another routine because I didn't have any success in the immediate window. I wonder though why it fails there. "Andy Pope" wrote: Hi, This revised and shortened code works for me. Apart from the return type reader instead of readers, which others have pointed out. I guess the main problem is with the declaration of the variable in the calling routine. In this case the xyz array. You do not show this code by either you have declared it as variant or not declared it's type so it is defaulting to variant. Type reader r_sname As String r_name As String r_restel As String r_offtel As String r_mobile As String End Type Sub Test() Dim xyz() As reader xyz = GetReaderRecordsArray(Range("A1")) Debug.Print xyz(1).r_name Debug.Print xyz(10).r_name End Sub Function GetReaderRecordsArray(rngTimeCell As Range) As reader() Dim u_readers() As reader ReDim u_readers(10) u_readers(1).r_name = "Test1" u_readers(10).r_name = "Test10" GetReaderRecordsArray = u_readers End Function Cheers Andy justahelper wrote: XL version - 2003 (11.5612.5606) Part of Microsoft Office Professional Edition 2003 VBA - Micorsoft Visual Basic 6.3 The function is in a normal module. I have written this piece of code. I also have the function for GetReaderRecord in the same module. If I change the return value to just reader and not reader() and assign the return value as GetReaderRecordsArray = u_readers(1) the function works fine and returns the correct value. Also in break mode I can see not all the items in the array. Question is - why does it fail when I change the return value to an array? "Tushar Mehta" wrote: You still haven't answered the first question. Also, where -- what kind of module(s), that is -- do you have all this code? And, did you write the code or did you get it from somewhere else? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I changed it as suggested. It gives the error: Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions. "Tushar Mehta" wrote: What version of XL / VBA are you using? Does the compiler highlight a particular line as the offending line? At the very least you should change the return type of the function from readers() to reader() -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have defined the following: Type reader r_sname As String r_name As String r_restel As String r_offtel As String r_mobile As String End Type and am trying to use it in the following function: Function GetReaderRecordsArray(rngTimeCell As Range) As readers() Dim rngCommRng As Range Dim u_readers() As reader Dim bCommArr() As Boolean Dim i As Integer Dim j As Integer Dim c_comm As Integer Set rngCommRng = GetCommitRng(rngTimeCell) bCommArr = GetCommFromCommitRng(rngCommRng) c_comm = GetYesCountFromCommRng(bCommArr) If c_comm 0 Then ReDim u_readers(c_comm) i = 0 For j = LBound(bCommArr) To UBound(bCommArr) If bCommArr(j) Then i = i + 1 u_readers(i) = GetReaderRecord(rngCommRng.Cells(j, 1)) Else End If Next j End If GetReaderRecordsArray = u_readers End Function I get a compile error msg. Can anyone help me fix this? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Array of UDT
What I typed in the immediate window was this:
?GetReaderRecordsArray(1).r_name "Andy Pope" wrote: Did you, and if so how, define the return variable as being an array within the immediate window? Cheers Andy justahelper wrote: Hi Andy, That's strange. I spent hours looking at the result in the immediate window and could not figure out why it didn't work. I never bothered to try calling it from another routine because I didn't have any success in the immediate window. I wonder though why it fails there. "Andy Pope" wrote: Hi, This revised and shortened code works for me. Apart from the return type reader instead of readers, which others have pointed out. I guess the main problem is with the declaration of the variable in the calling routine. In this case the xyz array. You do not show this code by either you have declared it as variant or not declared it's type so it is defaulting to variant. Type reader r_sname As String r_name As String r_restel As String r_offtel As String r_mobile As String End Type Sub Test() Dim xyz() As reader xyz = GetReaderRecordsArray(Range("A1")) Debug.Print xyz(1).r_name Debug.Print xyz(10).r_name End Sub Function GetReaderRecordsArray(rngTimeCell As Range) As reader() Dim u_readers() As reader ReDim u_readers(10) u_readers(1).r_name = "Test1" u_readers(10).r_name = "Test10" GetReaderRecordsArray = u_readers End Function Cheers Andy justahelper wrote: XL version - 2003 (11.5612.5606) Part of Microsoft Office Professional Edition 2003 VBA - Micorsoft Visual Basic 6.3 The function is in a normal module. I have written this piece of code. I also have the function for GetReaderRecord in the same module. If I change the return value to just reader and not reader() and assign the return value as GetReaderRecordsArray = u_readers(1) the function works fine and returns the correct value. Also in break mode I can see not all the items in the array. Question is - why does it fail when I change the return value to an array? "Tushar Mehta" wrote: You still haven't answered the first question. Also, where -- what kind of module(s), that is -- do you have all this code? And, did you write the code or did you get it from somewhere else? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I changed it as suggested. It gives the error: Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions. "Tushar Mehta" wrote: What version of XL / VBA are you using? Does the compiler highlight a particular line as the offending line? At the very least you should change the return type of the function from readers() to reader() -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have defined the following: Type reader r_sname As String r_name As String r_restel As String r_offtel As String r_mobile As String End Type and am trying to use it in the following function: Function GetReaderRecordsArray(rngTimeCell As Range) As readers() Dim rngCommRng As Range Dim u_readers() As reader Dim bCommArr() As Boolean Dim i As Integer Dim j As Integer Dim c_comm As Integer Set rngCommRng = GetCommitRng(rngTimeCell) bCommArr = GetCommFromCommitRng(rngCommRng) c_comm = GetYesCountFromCommRng(bCommArr) If c_comm 0 Then ReDim u_readers(c_comm) i = 0 For j = LBound(bCommArr) To UBound(bCommArr) If bCommArr(j) Then i = i + 1 u_readers(i) = GetReaderRecord(rngCommRng.Cells(j, 1)) Else End If Next j End If GetReaderRecordsArray = u_readers End Function I get a compile error msg. Can anyone help me fix this? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Array of UDT
I would image because the returned value is actual coerced in to a
variant when you use ? Cheers Andy justahelper wrote: What I typed in the immediate window was this: ?GetReaderRecordsArray(1).r_name "Andy Pope" wrote: Did you, and if so how, define the return variable as being an array within the immediate window? Cheers Andy justahelper wrote: Hi Andy, That's strange. I spent hours looking at the result in the immediate window and could not figure out why it didn't work. I never bothered to try calling it from another routine because I didn't have any success in the immediate window. I wonder though why it fails there. "Andy Pope" wrote: Hi, This revised and shortened code works for me. Apart from the return type reader instead of readers, which others have pointed out. I guess the main problem is with the declaration of the variable in the calling routine. In this case the xyz array. You do not show this code by either you have declared it as variant or not declared it's type so it is defaulting to variant. Type reader r_sname As String r_name As String r_restel As String r_offtel As String r_mobile As String End Type Sub Test() Dim xyz() As reader xyz = GetReaderRecordsArray(Range("A1")) Debug.Print xyz(1).r_name Debug.Print xyz(10).r_name End Sub Function GetReaderRecordsArray(rngTimeCell As Range) As reader() Dim u_readers() As reader ReDim u_readers(10) u_readers(1).r_name = "Test1" u_readers(10).r_name = "Test10" GetReaderRecordsArray = u_readers End Function Cheers Andy justahelper wrote: XL version - 2003 (11.5612.5606) Part of Microsoft Office Professional Edition 2003 VBA - Micorsoft Visual Basic 6.3 The function is in a normal module. I have written this piece of code. I also have the function for GetReaderRecord in the same module. If I change the return value to just reader and not reader() and assign the return value as GetReaderRecordsArray = u_readers(1) the function works fine and returns the correct value. Also in break mode I can see not all the items in the array. Question is - why does it fail when I change the return value to an array? "Tushar Mehta" wrote: You still haven't answered the first question. Also, where -- what kind of module(s), that is -- do you have all this code? And, did you write the code or did you get it from somewhere else? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I changed it as suggested. It gives the error: Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions. "Tushar Mehta" wrote: What version of XL / VBA are you using? Does the compiler highlight a particular line as the offending line? At the very least you should change the return type of the function from readers() to reader() -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have defined the following: Type reader r_sname As String r_name As String r_restel As String r_offtel As String r_mobile As String End Type and am trying to use it in the following function: Function GetReaderRecordsArray(rngTimeCell As Range) As readers() Dim rngCommRng As Range Dim u_readers() As reader Dim bCommArr() As Boolean Dim i As Integer Dim j As Integer Dim c_comm As Integer Set rngCommRng = GetCommitRng(rngTimeCell) bCommArr = GetCommFromCommitRng(rngCommRng) c_comm = GetYesCountFromCommRng(bCommArr) If c_comm 0 Then ReDim u_readers(c_comm) i = 0 For j = LBound(bCommArr) To UBound(bCommArr) If bCommArr(j) Then i = i + 1 u_readers(i) = GetReaderRecord(rngCommRng.Cells(j, 1)) Else End If Next j End If GetReaderRecordsArray = u_readers End Function I get a compile error msg. Can anyone help me fix this? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Array of UDT
Thanks Andy.
"Andy Pope" wrote: I would image because the returned value is actual coerced in to a variant when you use ? Cheers Andy justahelper wrote: What I typed in the immediate window was this: ?GetReaderRecordsArray(1).r_name "Andy Pope" wrote: Did you, and if so how, define the return variable as being an array within the immediate window? Cheers Andy justahelper wrote: Hi Andy, That's strange. I spent hours looking at the result in the immediate window and could not figure out why it didn't work. I never bothered to try calling it from another routine because I didn't have any success in the immediate window. I wonder though why it fails there. "Andy Pope" wrote: Hi, This revised and shortened code works for me. Apart from the return type reader instead of readers, which others have pointed out. I guess the main problem is with the declaration of the variable in the calling routine. In this case the xyz array. You do not show this code by either you have declared it as variant or not declared it's type so it is defaulting to variant. Type reader r_sname As String r_name As String r_restel As String r_offtel As String r_mobile As String End Type Sub Test() Dim xyz() As reader xyz = GetReaderRecordsArray(Range("A1")) Debug.Print xyz(1).r_name Debug.Print xyz(10).r_name End Sub Function GetReaderRecordsArray(rngTimeCell As Range) As reader() Dim u_readers() As reader ReDim u_readers(10) u_readers(1).r_name = "Test1" u_readers(10).r_name = "Test10" GetReaderRecordsArray = u_readers End Function Cheers Andy justahelper wrote: XL version - 2003 (11.5612.5606) Part of Microsoft Office Professional Edition 2003 VBA - Micorsoft Visual Basic 6.3 The function is in a normal module. I have written this piece of code. I also have the function for GetReaderRecord in the same module. If I change the return value to just reader and not reader() and assign the return value as GetReaderRecordsArray = u_readers(1) the function works fine and returns the correct value. Also in break mode I can see not all the items in the array. Question is - why does it fail when I change the return value to an array? "Tushar Mehta" wrote: You still haven't answered the first question. Also, where -- what kind of module(s), that is -- do you have all this code? And, did you write the code or did you get it from somewhere else? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I changed it as suggested. It gives the error: Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions. "Tushar Mehta" wrote: What version of XL / VBA are you using? Does the compiler highlight a particular line as the offending line? At the very least you should change the return type of the function from readers() to reader() -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have defined the following: Type reader r_sname As String r_name As String r_restel As String r_offtel As String r_mobile As String End Type and am trying to use it in the following function: Function GetReaderRecordsArray(rngTimeCell As Range) As readers() Dim rngCommRng As Range Dim u_readers() As reader Dim bCommArr() As Boolean Dim i As Integer Dim j As Integer Dim c_comm As Integer Set rngCommRng = GetCommitRng(rngTimeCell) bCommArr = GetCommFromCommitRng(rngCommRng) c_comm = GetYesCountFromCommRng(bCommArr) If c_comm 0 Then ReDim u_readers(c_comm) i = 0 For j = LBound(bCommArr) To UBound(bCommArr) If bCommArr(j) Then i = i + 1 u_readers(i) = GetReaderRecord(rngCommRng.Cells(j, 1)) Else End If Next j End If GetReaderRecordsArray = u_readers End Function I get a compile error msg. Can anyone help me fix this? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
| All times are GMT +1. The time now is 05:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com