VB6 macro : UDT function error message

Excel macro having function returning User Defined datatype,

Page 1 of 1

11 Replies - 2728 Views - Last Post: 22 May 2010 - 01:58 AM Rate Topic: -----

#1 coleine  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 16-May 10

VB6 macro : UDT function error message

Posted 20 May 2010 - 07:07 AM

Hi,

I doing programming after a long time and having challenge in a macro made in VB editor of Excel.

I did some coding about 8-10 years back in C /C++

I am making function which will fetch data from the Excel worksheet and will return the values in a user defined data type. while doing so I am getting an error message.

I have attached the error message. and given below is the text for it:

Compile error:

Private Enum and User defined types cannot be used as parameters or return types for public procedures, public data members, or feilds of public user defined types.

Can use functions to return user defined types in VB?

Given below is the code.
+++++++++++++++++++++++++++++++++++


Const MAXVERTIX As Integer = 30
Const MAXNODES As Integer = 10

Private Type vertex

    NodeA As String
    NodeB As String
    
    Distance As Integer
    Capacity As Integer
    Vertex_ID As Integer

End Type

Private Type node

    Node_name As String
    Node_id As Integer

End Type



Sub test_type()

Dim Nodes(MAXNODES) As String
Dim table_array(20, 20)

Dim AllVertex(MAXVERTIX) As vertex

VertexCounter = 1


For i = 0 To MAXVERTIX

    'Feeding the tables
    
    Cells(i + 63, 4) = AllVertex(i).NodeA
    Cells(i + 63, 5) = AllVertex(i).NodeB
    Cells(i + 63, 6) = AllVertex(i).Distance
    Cells(i + 63, 7) = AllVertex(i).Capacity
    
Next



End Sub

Function get_all_vertex_data() As vertex

    buffer_vertex(MAXVERTIX) As vertex
    
    For i = 0 To 30
    
        buffer_vertex(i).Vertex_ID = Cells(i + 3, 4)
        buffer_vertex(i).NodeA = Cells(i + 3, 5)
        buffer_vertex(i).NodeB = Cells(i + 3, 6)
        buffer_vertex(i).Capacity = Cells(i + 3, 7)
        buffer_vertex(i).Vertex_ID = Cells(i + 3, 8)
    
    Next
    
    get_all_vertex_data = buffer_vertex

End Function



++++++++++++++++++++++++++++++++++

Is This A Good Question/Topic? 0
  • +

Replies To: VB6 macro : UDT function error message

#2 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 464
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: VB6 macro : UDT function error message

Posted 20 May 2010 - 12:35 PM

as far as i see you try to return an array to an single element:
buffer_vertex(MAXVERTIX) As vertex
witch is array and you try to pass it to a
Function get_all_vertex_data() As vertex
usually if you use this
Option Explicit
Private Type my_str
    str1 As String
    str2 As String
End Type
Private Sub Command1_Click()
    Dim p As my_str
    p = fuc1
    Debug.Print p.str1
    Debug.Print p.str2
End Sub

Private Function fuc1() As my_str
    Dim m_str1 As my_str
    m_str1.str1 = "1"
    m_str1.str2 = "2"
    fuc1 = m_str1
End Function



you will have no problems
now i can suggest you using a byref property of the function to return array:
Sub get_all_vertex_data(ByRef arrvertex() as vertex)

    buffer_vertex(MAXVERTIX) As vertex
    
    For i = 0 To 30
    
        buffer_vertex(i).Vertex_ID = Cells(i + 3, 4)
        buffer_vertex(i).NodeA = Cells(i + 3, 5)
        buffer_vertex(i).NodeB = Cells(i + 3, 6)
        buffer_vertex(i).Capacity = Cells(i + 3, 7)
        buffer_vertex(i).Vertex_ID = Cells(i + 3, 8)
    
    Next
    
    arrvertex = buffer_vertex

End Function



hope it help you :)
Was This Post Helpful? 0
  • +
  • -

#3 coleine  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 16-May 10

Re: VB6 macro : UDT function error message

Posted 20 May 2010 - 02:37 PM

I am still getting the same error Message.

is it because I am using VB editor comes with excel.
Was This Post Helpful? 0
  • +
  • -

#4 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 464
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: VB6 macro : UDT function error message

Posted 20 May 2010 - 02:51 PM

the error say that your using a private enum or type as parameter of public function. what line is selected and can we see your modify code

This post has been edited by NoBrain: 20 May 2010 - 02:51 PM

Was This Post Helpful? 0
  • +
  • -

#5 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: VB6 macro : UDT function error message

Posted 21 May 2010 - 01:41 AM

yes that's the obvious error statement

i think, declare the structure Vertex as public instead of private will solve your problem
Was This Post Helpful? 0
  • +
  • -

#6 coleine  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 16-May 10

Re: VB6 macro : UDT function error message

Posted 21 May 2010 - 05:50 AM

@ thava I did that as well its still not helping. :angry2:

All I am trying to use is function rather is than writing entire code in the main subroutine it was so easy in C / C++ :sad2:


looking forward to hear from you guys...
Was This Post Helpful? 0
  • +
  • -

#7 vb5prgrmr  Icon User is offline

  • D.I.C Lover

Reputation: 109
  • View blog
  • Posts: 1,016
  • Joined: 21-March 09

Re: VB6 macro : UDT function error message

Posted 21 May 2010 - 06:22 AM

You have a module, and in that module you have a "Private Type MyPrivateType" but you have a function that is public ("Public Function SomeFunction(MPT As MyPrivateType) As MyPrivateType") and that is where you are getting your error. You are trying to pass something that is private to someplace that does not know about the type because it is private... To solve this, change the private types to public types... or if the function is not called or used outside of the module, then change the public function to a private function...

It is all a matter of scope...



Good Luck
Was This Post Helpful? 0
  • +
  • -

#8 coleine  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 16-May 10

Re: VB6 macro : UDT function error message

Posted 21 May 2010 - 06:32 AM

View Postvb5prgrmr, on 21 May 2010 - 05:22 AM, said:

You have a module, and in that module you have a "Private Type MyPrivateType" but you have a function that is public ("Public Function SomeFunction(MPT As MyPrivateType) As MyPrivateType") and that is where you are getting your error. You are trying to pass something that is private to someplace that does not know about the type because it is private... To solve this, change the private types to public types... or if the function is not called or used outside of the module, then change the public function to a private function...

It is all a matter of scope...



Good Luck



Hi VB editor is not allowing me to define UDT (user defined data type) as public. I am getting an error while compile so I am forced to keep it private.

Regards,
Was This Post Helpful? 0
  • +
  • -

#9 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 464
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: VB6 macro : UDT function error message

Posted 21 May 2010 - 01:20 PM

impossible what error your getting when you define public type i find it hard to believe. what VBA are you using any way
Was This Post Helpful? 0
  • +
  • -

#10 coleine  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 16-May 10

Re: VB6 macro : UDT function error message

Posted 21 May 2010 - 01:30 PM

I am using VBA that comes office 2007 Excel.

I reach vb editor by pressing Alt + f11
Was This Post Helpful? 0
  • +
  • -

#11 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 464
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: VB6 macro : UDT function error message

Posted 21 May 2010 - 01:37 PM

no if you do this
'In module1'
Option Explicit
Public Type my_str
    str1 As String
    str2 As String
End Type
Public m_ As m_str
'in some class or form'
Private Function fuc1() As my_str
    Dim m_str1 As my_str
    m_str1.str1 = "1"
    m_str1.str2 = "2"
    fuc1 = m_str1
End Function



what error do you get
Was This Post Helpful? 0
  • +
  • -

#12 vb5prgrmr  Icon User is offline

  • D.I.C Lover

Reputation: 109
  • View blog
  • Posts: 1,016
  • Joined: 21-March 09

Re: VB6 macro : UDT function error message

Posted 22 May 2010 - 01:58 AM

So that means you are in a "Private" module and private modules are tied to forms. To solve, move code to a "Module" and then declare types as public.



Good Luck
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1