Only User-defined Types Defined In Public Objects Modules Can Be Coerced To Or From A Variant Or Passed To Late-bound Functions. What?

Public Type Mode_S_Message_Record
    Mode_S_ID As String
    DF As String
    Msg_Type As String
    NACv As String
    ADSB_ID As String
    OTGI As String
    NACp As String
    SIL As String
    Lat As String
    Lon As String
    Alt As String
    CPR As String
    Mode_S_Message As String
End Type

Public Mode_S_Message_Records() As Mode_S_Message_Record

If IsEmpty(Mode_S_Message_Records) Then
    Populate_Mode_S_Message_Records
End If

Public Type MyType
    fred As Integer
    barney As Single
    betty As String
End Type


Sub Flint()
  Dim wilma As MyType
  Dim dino As MyType

  wilma.fred = 6           ' this works
  wilma.barney = 1.1     ' this works
  wilma.betty = "hello"   ' this works

  dino = [7, 2.2, "there"]   ' this gives me a compile error- "Only user defined 
                                ' types defined in public object modules can be               
                                ' coerced to or from a variant or passed to a 
                                ' late bound function"

End Sub

Sub DrawIssues()
    Dim i As Integer ' used to keep track of index in array
    Dim k As Integer ' used to keep track of time through loop
    Dim xDeals As Double ' number of deals in array
    Dim r As Integer
    Dim factor As Integer
    Call MakeIssue  14 And r < 46 Then
                ElseIf r = 47 Then
                    .MergeCells = True
                    ' .TextAlign (2)
                    .Interior.Color = RGB(204, 204, 204)
                ElseIf r > 47 And r < 51 Then
                    .MergeCells = True
                    ' .TextAlign (2)
                    .Interior.Color = RGB(255, 255, 255)
                ElseIf r > 51 And r < 58 Then
                    ' .TextAlign (2)
                    .Interior.Color = RGB(255, 255, 255)
                ElseIf r > 58 And r

Type CustomerInfo
    Company As String
    RegionCode As Integer
End Type

Sub TestDataType
    Dim Customer(1 To 100) As CustomerInfo
End Sub

Type CustomerInfo
    Company As String
    RegionCode As Integer
End Type

Type EveryCustomer
      EveryCompany As CustomerInfo
End Type

Hi

I'm defining a data type

Type JobInfo
JobId As String
JobCustId As String
JobCustName As String
JobBusName As String
JobShortDesc1 As String
JobLongDesc1 As String * 400
JobAccType1 As String
JobNoOfItem1 As Integer
JobItemPrice1 As Integer
JobShortDesc2 As String
JobLongDesc2 As String * 400
JobAccType2 As String
JobNoOfItem2 As Integer
JobItemPrice2 As Integer
JobShortDesc3 As String
JobLongDesc3 As String * 400
JobAccType3 As String
JobNoOfItem3 As Integer
JobItemPrice3 As Integer
JobShortDesc4 As String
JobLongDesc4 As String * 400
JobAccType4 As String
JobNoOfItem4 As Integer
JobItemPrice4 As Integer
JobShortDesc5 As String
JobLongDesc5 As String * 400
JobAccType5 As String
JobNoOfItem5 As Integer
JobItemPrice5 As Integer
End Type

is there a better way to do this with an arraY?

EG
Type JobInfo
JobId As String
JobCustId As String
JobCustName As String
JobBusName As String
JobMoreInfo 5 time
JobShortDesc As String
JobLongDesc As String * 400
JobAccType As String
JobNoOfItem As Integer
JobItemPrice As Integer
end type

Hi,

Is there a way to use "FieldInfo:=Array(Array(1, 2), Array(43, 2))"
when importing data instead of the individual array elements ie

"FieldInfo:=Array(Array(1, 2), Array(2, 2)...." And to make the
FieldInfo:=Array convert the information to TEXT instread of general?

Example code
**************************************************************************
Sub AIR_Report()
'
'===========================================================
Dim V As Variant 'This segment of dims is for split
Dim s As String
Dim newDate As String
Dim j As Long
'===========================================================
Dim X As Integer
Dim k As Integer
Dim kk As Integer
Dim aDate As Integer
Dim CntDels As Integer 'counts deleted rows
Dim lenX As Integer
Dim txt As Variant
Dim xCell As Range
'Dim fname As String
Dim vDate(1 To 4) As Integer
'========= Message ==================================
Dim msg, Style, Title, Response
'========== remove returns ==========================
Dim WS As Worksheet

'FieldInfo:=Array(Array(1, 2), Array(43, 2)) Indavidual cols formated
as general
'used to simplify data entry - doesn't work because it imports data as
general and not txt

Workbooks.OpenText Filename:=Fname, Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2),
Array(2, 2), _
Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7,
2), Array(8, 2), Array(9, 2), _
Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2),
Array(14, 2), Array(15, 2), Array( _
16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20,
2), Array(21, 2), Array(22, 2), _
Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 2),
Array(27, 2), Array(28, 2), Array( _
29, 2), Array(30, 2), Array(31, 2), Array(32, 2), Array(33,
2), Array(34, 2), Array(35, 2), _
Array(36, 2), Array(37, 2), Array(38, 2), Array(39, 2),
Array(40, 2), Array(41, 2), Array( _
42, 2), Array(43, 2)), TrailingMinusNumbers:=True

*****************************************************************************

So I have this custom Type:

Code:

Public Type Shape_Export_Data_Ver_1

    sedName As String
    sedText As String
    sedTop As Double
    sedLeft As Double
    sedWidth As Double
    sedHeight As Double
    sedShadow_Visible As Boolean
    sedShadow_Type As Integer
    sedType As Integer
    sedConnector_BeginShape As String
    sedConnector_EndShape As String
    sedConnector_BeginAnchor As Integer
    sedConnector_EndAnchor As Integer
    sedFont_Color As Integer
    sedFill_Color As Integer
    sedFont_Size As Integer
    sedConnector_Type As Integer
    sedGRIPS() As Double
    
End Type

But when I call the Join Function:

Code:

Dim Dat_Array() As Shape_Export_Data_Ver_1

...

Join(Dat_Array(Cnt).sedGRIPS, "|")

I get the following error:

Quote:

Runtime Error 5: Invalid Procedure Call or Argument

While the Isarray() function

Code:

IsArray(Dat_Array(Cnt).sedGRIPS)

Returns a True.

So the work around is child's play... yet I'm a little troubled that the original usage doesn't work. Has anyone encountered this, or have some explanation. I assume that it's something to do with the the fact that I have defined an array as a custom type that contains an array, and the interpretter is just getting confused by the recursion...

After much online digging, and experimentation, I think I determined earlier that you can't really expose an array all-at-once if it is an object/class property, as you can with an array variable. Now I'd like to confirm this, if I could, because it would be neater to run my simulation in an object instead of a giant and labrynthine user-defined type.

I try the following code:

Code:

'Class1
 
Private pArray(5) As Integer
 
Public Property Get ArrayProp(Index As Integer) As Integer 
    ArrayProp(Index) = pArray(Index)
End Property
Public Property Let ArrayProp(Index As Integer, Value As Integer)
    pArray(Index) = Value
End Property
'-------------------------------------------------------
 
Public Sub test()
Dim i As Integer
Dim r As Double
 
Dim x As Class1
Set x = New Class1
 
For i = 1 To 5
  x.ArrayProp(i) = 10 + i
Next i
 
r = Application.Average(x.ArrayProp)
 
End Sub

I get "Compile Error: Argument Not Optional" and the same if I try x.ArrayProp() . I know I tried declaring a public array variable in an object class before and that also gives a well known error. Maybe something could be done with a variant type that receives an array, but that might get too sketchy?

So is it the case that trying to take an average of an array property all-at-once, or writing an array to a cell range all-at-once, is not possible? (I am doing both of those with nested arrays in my UDT...)

Thanks and I hope this isn't a repetitive question, but of the many I've searched around on, it's been one of the toughest...

Good day,

Is there a way to declare a 2-dimensional array, starting from 1 upto 1000 for each dimension and most importantly consisting of different data type?

What I know is:
Code:

Dim myArray(1 to 1000) As Integer

This provides first array index 1 as opposed to 0 but it is one dimensional
Code:

Dim myArray(1 to 1000, 1 to 1000) As integer

This is a 2-D array but all dimensions of type integer, not of different type.
I want to declare an array one dimension of which is sth like the Type below:

Code:

 
Public Type myType
  x As Integer
  y As Integer
  str As String
  lng As Long
End Type

And based on what I know above, I tried:
Code:

 
Dim myArray(1 to 1000 As Integer, 1 to 1000 As myType)

but this fails...

Why on earth I would want this: Because, I want to run a loop to assign values to textBox objects which form a UserForm. By array-like indexing it is easy to reach out controls within loop. If I used Type decleration I would have to manually type each different bit of the myType type.

Many thanks in advance

I have a function in VBA of the type

Function MyFunc(Indx As Integer, k As Long, Rho As Range, A As Range) As Variant .... End Function

which is called as a user-defined function from within the Excel worksheet. When called with the last two arguments being a range (i.e. Result = MyFunc(1,98,A1:A2, B1:B2)) it works fine. However, when I try to directly use an array constant instead of a range (i.e. Result = MyFunc(1,98,{10,11}, {20,30}), it returns a #VALUE error.

I thought I could fix it by redefining the last two arguments as arrays of type double, but this didn't work either (i.e. Function MyFunc(Indx As Integer, k As Long, Rho() As Double, A() As Double) As Variant .... End Function ).

Does someone have a suggestion for a flexible solution, which would permit either calling method: by range, as well as by an array constant?

Thanks in advance!

I'm a newbie.

I'm trying to make my first VBA function work, and I know I'm very close, but I'm hitting some roadblocks.

I've got a couple functions defined in a single module, and both functions appear in the User-Defined list in the Insert Function dialogue. But, even though both functions take arguments, the dialogue is telling me that they take no arguments.

Secondly, whether I put arguments in or not, I only get a "#NAME?" error, which leads me to believe that for some reason Excel is not finding the functions properly.

I've tried all the little slight adjustments I can think of, and I've even tried turning the macro security to low, hoping that it might somehow be related. Further, when I tried downloading a sample workbook from an online Excel/VBA tutorial, the sample code gave me the exact same trouble, so I'm wondering if there's some preference or setting somewhere that I need to turn on/off...

Just in case, here are my functions. Obviously, the second one is just something simple I'm trying to test with.

I would really appreciate any insight or ideas on what I can try. Thanks in advance.


Function GetNth(team As Range, pos As String, nth As Integer) As String
Dim row As Integer
Dim done As Boolean
Dim found As Integer
Dim result As String

row = 0
done = False
found = 0
result = "..."

Do Until (done = True) Or (row = 14)
row = row + 1
If team.Cells(row, 1) = pos Then
found = found + 1
If found = nth Then
result = team.Cells(row, 2)
End If
done = True
End If
Loop
GetNth = result
End Function

Function AddOne(num As Integer) As Integer
AddOne = num + 1
End Function

Hey, Im having trouble setting up the notepad document that I need my array to load. Can someone please tell me how to set it up? the data table includes the following information: (the data table doesnt show up in the thread how i want it to, but it is separated by how many of each product was sold and for how much)
Data Table
Lamp Chair Sofa Table Desk
New York 25 64 23 45 14
Chicago 12 82 19 34 63
Los Angeles 54 22 17 43 35
Item Price $12.00 $17.95 $95.00 $86.50 $78.00

The code that I have so far is:
Code:

Option Explicit
Public Sub Calculations()
Dim Company_Array(3, 5) As Integer, Item_Cost(5) As Double, Store_Array(3) As Double, _
Item_Array(5) As Integer, Store_Items(3) As Integer, City_Array(3) As String, Items_Array(5) As String, i As Integer, j As Integer, x As Integer, y As Integer, z As Integer, _
k As Integer, SumItems As Double, SumRevenue As Double

        x = 3: y = 3: z = 4

        Range("a1").Value = "Initial Data:"

        Call Load_Arrays(Company_Array)

        Range("b2").Value = "Product"

        Range("c2").Value = "Amount"

        Range("d2").Value = "Price"

        For i = 1 To UBound(Company_Array)

            Sheet1.Cells(x, 1) = "City = " & City_Array(i)

                x = x + 5

            For j = LBound(Company_Array, 2) To UBound(Company_Array, 2)

                Sheet1.Cells(y, 2) = Items_Array(j)

                Sheet1.Cells(y, 3) = Company_Array(i, j)

                Sheet1.Cells(y, 4) = FormatCurrency(Item_Cost(j), 2)

                Store_Array(i) = Store_Array(i) + Item_Cost(j) * Company_Array(i, j)

                Item_Array(j) = Item_Array(j) + Company_Array(i, j)

                Store_Items(i) = Store_Items(i) + Company_Array(i, j)

                y = y + 1

            Next

        Next
End Sub

Private Sub Load_Arrays(sales() As Integer)

    Dim m As Integer, n As Integer, k As Integer, l As Integer, i As Integer

    Open "c:\cs385\Spring06\Sales_Data.txt" For Input As #1

        Do Until EOF(1) = True

            For m = 1 To UBound(sales)

                For n = 1 To UBound(sales, 2)

                    Input #1, sales(m, n)

                Next

            Next

        Loop

    Close #1

End Sub

Thanks

Hey all...
I in the below example I had to explicitly define the Array counts rather than use variables.
Is it possible to use variables in the Array definition?
If so, what should I be watching out for?

Option Base 1
Sub CompSummary()

Dim VendCt, FuncCt, Ct, Ct2, ReqCt, NumOfFunctions, NumOfVendors, NumOfReqs As Integer

'---Set number of Function Do LOOP iterations by the number of functions included
'I would like to use a CountA function to count the number of Functions and Vendors_ dynamically so I don't have to amend the macro when this happens in the source data sheet.

NumOfFunctions = 17
NumOfVendors = 20


'Dimension Arrays in terms of predefined constants******For SOME REASEON WONT LET ME DEFINE AS VARIABLE***
Dim FuncVend(17, 20) As Integer
Dim FuncVendInd(17, 20) As Integer

'do some stuff
End Sub

'I want to Dim like below, but then it doens't recognize the Array...I had to define explicityly as above.
Dim FuncVend(NumOfFunctions, NumOfVendors) As Integer
Dim FuncVendInd(17, 20) As Integer


Thanks for any suggestions,
-E

Hi all,
I'm currently passing an Array into a function which proceeds to pass it into another function.
The functions look something like:
function1(intArr() As Integer)
function2(iArr() As Integer)

Now when run, the program enters function1 just just fine and passes in the array without an issue. Then when it hits #2 it says "Array or user-defined type expected." and quits. Something like(Pseudo code):
Code:

Call function1(integerArray) < WORKS
Function1(intArr() As Integer) 
   Function2(intArr) < BREAKS
End Function
Function2(iArr() As Integer)
'Nothing even in here yet (in the actual program this is currently blank minus a debug.print statement that doesnt consist of any variables)
End Function

Maybe I'm not 100% clear on how it handles Array's but I mean when you pass in an array I'm going to guess that behind the scene's its just a pointer to the head of the array. Which is why I'm confused as to why the EXACT same parameter with the EXACT same type wont drop into the second function.

Any help is appreciated.
Thank you,
chuckury

I am having difficulty getting a user defined data type to function
with arrays. What I would like to do is create a data type that is
comprised of four 1-D arrays. Then I would like to load the individual
arrays in another module so I can trasnfer all four arrays as a single
argument (the new data type) in a function. Here's what I have done so
far

In one module;

Option Base 1

Public Type PlotScalars

Xscalar(1 To 4) As Integer
Yscalar(1 To 4) As Integer
Zscalar(1 To 4) As Integer

End Type

In another module

Dim MyPlotScalars As PlotScalars

MyPlotScalars.Xscalar = Array(1, 0, 1, 1)
MyPlotScalars.Yscalar = Array(1, 1, 0, 1)
MyPlotScalars.Zscalar = Array(1, 1, 1, 0)

I get an error "Compile error: can't assign to array." I would prefer
not to have to load each element of the data type arrays one element at
a time. Is there an easy way around this?

My main motivation for doing this is to cut down on the number of
arguments passed to a user defined function (which are limited to 30).

TIA

dan

I'm creating certain user-form for a data input.
For that purpose i defined a data type.
If i put its definition in the public area:
Code:

Public area:
Type DataX     'defining data type which has to 

contain everything about a product
    PriceX As Integer
    ColorX() As Variant
    SizeX() As Variant
End Type
.
.
.
Private Sub Fill()
Dim Products() As DataX
                    
Products(0).PriceX = 25
Products(0).ColorX = Array("red", "blue")
Products(0).SizeX = Array("L", "XL", "XXL")

i get:
Quote:

Compile Error:
Cannot define a public user-defined type within an

object module


But if i move it to the Private Sub i get:
Quote:

Compile Error:
Invalid inside procedure

How do i fill my array of arrays ?


Many thanks in advance

Hi


For the times when variant contains an integer, how do i convert it to
integer type?

is it some thing like?
......................
dim varies As Variant
dim x As integer

if i know varies contains an integer value
x = integer(varies)

Also can a function which is said to return a variant, return an integer
type and the cell calling the function display an integer type or will just
appear as a variant type. I know a variant has no specific type but does
this mean it can accept various types and which are then tread as the
original type or is variant a type in itself, rather than a variable which
can be a variety of types?

Many thanks
James

Im trying to use this code for multiplication of an array.

Public Function ithtpx(i As Integer, x As Range)
' to get the i-th mortality rate in the range
' to assign a value to qx array
Dim Qx() ' define dynamic array
Dim s As Integer, p As Integer
Dim j 'loop index
' loop to assign mortality rates to Qx
s = o ' initial value
p = 1
For Each j In x
If j.Value "" Then
s = s + 1
ReDim Preserve Qx(1 To s) ' re dim the qx and keep the previous value too
Qx(s) = j.Value ' assign the value to that dimension
End If
Next j
' assign
' you will need the for loop to the expected life time for n = 1 to m
ithtpx = Qx(i) * (1 - Qx(i))

End function


But its not multiplying at the end of the code. If i remove Qx(i) it will give me the right value of (1-Qx(i)) but when i try to multiply i get only the value of Qx(i).

Also is there way i can add all the element in this array and what would be the code.

Dear all,

I downloaded one XLL Add-in file. When i double clicked it, it added some user defined functions in the function list in excel application. Then i checked in Modules of Visual Basics editor for the Codes of those functions but there was no any code or module.
I dont know from where these functions are coming.

Does anyone know from where excatly the codes of this user defined functions are been stored ?

Regards,
Akash

I am trying to write a function that computes x^k modulo N where x, k, and N are 64 bit integers.

To start with, I can't even get excel to work with user defined functions. Here are my exact steps:

1. Create a worksheet with a few rows and columns of information (no functions yet)
2. Press Alt+F11
3. Insert Module
4. In the new module, I make a function Test:

Function Test(t As Integer) As Integer
Test = t * 10
End Function
5. Save worksheet
6. In a cell in my worksheet, I enter =Test(10)

And the cell becomes #NAME!, I assume because it can't find Test. why the hell not? I've followed many different websites instructions to the letter, and none of them ever work, with this exact same problem.

Once I get functions to even work, does excel have a big integer type? It was very difficult to write the function I'm describing in C++ (due to 128 bit integer intermediate values), which I simply can't do in VB. however, if excel has a large integer type, it will be trivial.

Thanks for any help

Hi all,

I'm having a problem with using worksheet functions on arrays stored in vba. I use the following code to generate my array from results of iterations in Excel:

Code:

Sub PopArray()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Runs As Integer
Dim Cols As Integer
Dim Y As Double
Dim X As Double
Dim L1A As Variant

Cols = 11
Runs = Range("Runs").Value

ReDim L1A(Runs, Cols)

For i = 1 To Runs
Application.CalculateFull

    For j = 1 To Cols
    X = Range("L1_START").Offset(0, j).Value
    L1A(i, j) = X      

    Next j

Next i

Y = WorksheetFunction.Average(L1A(1, 1), L1A(1, Runs))

End Sub

I generate the two dimensional array without any issues - the problems start when I try and get the average value in the first column (last code line). I cannot seem to get the result to stay as a value.

Endgame is to generate three statistics from the array for each column: The average, the 25th Percentile and the 75th Percentile, percentiles using the LARGE function. These then to be stored as arrays and pasted into Excel.

Can anybody give me a hand finsihing off the code?

Many thanks

I'm wondering why this code won't work when the sheet is opened in Excel for Mac.

Let me know if I need exact versions or such, or if there is an obvious coding issue.

I'll double check, but I think my user is getting a run time error - 1004 - Application defined or object defined error

Code works fine on my machine.

Dim Lname As String, cl As Integer, oData, c As Integer, oEnd As Integer
Dim Ray As Integer, Rw As Integer, Last As Integer, cl2 As Range, test As String
Lname = "MailFromAddress*" 'Match with sheet(1)
c = 1
Rw = Sheets(1).Range("a" & Rows.Count).End(xlUp).Row

For cl = 1 To Rw
Last = Range("A" & Rows.Count).End(xlUp).Row
ReDim oData(1 To 40, 1 To 2)
Do Until Sheets(1).Cells(cl, 1).Value = Lname Or cl = Rw
oData(c, 1) = Sheets(1).Cells(cl, 1).Value
oData(c, 2) = Sheets(1).Cells(cl, 2)
c = c + 1
cl = cl + 1
Loop

oEnd = c
cl = cl + 1
c = 1
For Each cl2 In Range(Range("A2"), Cells(2, Columns.Count).End(xlToLeft))
For Ray = 1 To UBound(oData)
If cl2.Value = oData(Ray, 1) Then
cl2.Offset(Last - 1, 0).Value = oData(Ray, 2)
End If
Next Ray
Next cl2
Next cl

Please help me summing the elements on a array, my code is the following ;

Public i As Integer, j As Integer, Size1 As Integer, Size2 As Integer, start As Variant, startingCell As Range, b As Integer

Sub FindInput()
Dim Values() As Double
Size1 = InputBox(" What is first dimension of your array? ")
Size2 = InputBox(" What is second dimension? ")

ReDim Values(Size1, Size2)
For i = 1 To Size1
For j = 1 To Size2
Values(i, j) = i * j
Next j
Next i

j = InputBox("Select a column of the array")
start = InputBox("Select a range from the spreadsheet")

Set startingCell = Range(start).Offset(0, 0)
startingCell.Offset(0, 0).Value = 1 * j

For i = 2 To Size1

b= sum( values(2, j) to values(i,j)) ( can you help me how should I write these statement to sum the values )
startingCell.Offset(0, (b+i-1)).Value = i * j

Next i

End Sub

Example:
Suppose Values is an array of 3x2 and the user specifies column 2 and A2. Values array should be;
1

2

2

4

3

6






and the final spreadsheet should look as follows;


A

B

C

D

E

F

G

H

I

J

K

L

M

N

1





























2

2









4













6

Hi,

Is it possible to change the type of a user define object within a code? (User defined is not one defined by me but of one obtained from an add in). I know VBA has Variant for instances of a predefined type being used if, for example, the user wants to use the variable as integer and string within a program.

In Java there is such a thing as "casting" which can be used to change a type but I'm not sure of VBA.

Any ideas?

Mbrolass