Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

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

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

This most inconvient function occourred about this code. It's much longer than this, with a lot of user defined functions, but that's the general area.

Dim firstIdx As Integer 'first dimension array idx, and 2nd and 3rd next lines
Dim secondIdx As Integer
Dim thirdIdx As Integer

Dim product As String
Dim ender As Integer
Dim ender3 As Integer

'this double loop loads the all data array
Do While allData(firstIdx, 0, 0) "NULL"
product = allData(firstIdx, 0, 0)
secondIdx = 1
thirdIdx = 1
iTimeArray = timeKeeper(iRange, iDateStart, iDateInterval)
ender = arrayEnder

Do While secondIdx
View Answers     

Similar Excel Tutorials

List All Defined Names and Values in the Worksheet in Excel
Quickly list all Defined Names and their Values in the worksheet without using a macro. This allows you to view an ...
Enter a Constant Value in a Defined Name in Excel - Text, Numbers, Formulas, Etc.
In Excel you can store values in Defined Names.  Often people use a Defined Name to refer to a cell on a worksheet ...
Easy Way to Manage Names in Excel
How to manage Names in Excel so they are easy to view and change as needed. This is a little trick that I use to k ...
How to View the Selection Pane in Excel When there are No Visible Objects
The Selection Pane is a very useful tool in Excel that allows you to view all of the objects, shapes, charts, pictu ...

Helpful Excel Macros

Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Filter Data in Excel to Show Only the Top X Percent of that Data Set - AutoFilter
- This Excel macro filters a set of data in Excel and displays only the top X percent of that data. This means the highes
Extract a Word from a Sentence / Cell in Excel with this UDF - Allows for a User-Defined Delimiter
- Extract whole words from a cell or sentence in Excel with this UDF. This allows you to specify which word from a cell y
Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel - UDF
- This free Excel UDF allows you to get the numbers out of a cell that contain both text and numbers. This is a great fun
Extract the First Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
- This free Excel UDF (user defined function) returns the first word from a cell in Excel. This extracts the first word f

Similar Topics

I'd like to return an array with several variables of a user-defined type from a function. Couldn't find a solution!

I get a Compile error on Function() As Variant:
"Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions"
and a Compile error on Function As Variant:
"Type mismatch"

Please Login or Register  to view this content.

Any help is appreciated, thank you


In one of the modules for a workbook of mine, I have The following type defined:

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

and then the following declaration of an array of this type:


Public Mode_S_Message_Records() As Mode_S_Message_Record

I have a subroutine in that module that populates the Mode_S_Message_Records array; then some other functions in the module can use the array.

What I'd also like to be able to do is have the UDFs check if the array is empty (hasn't been populated) and run the sub to populate the array if need be. The problem I run into is as follows: If I try to do the following in as sub or function:


If IsEmpty(Mode_S_Message_Records) Then
End If

I get this error: "Compiler error: Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound function".
Can anyone tell me what I'm doing wrong? All I want to do is find out if my array is empty!

I get an error when using type:

Type complex
re as double
im as double
end type

I put the type code in a module, and access it from thestartup module in vba with :

dim akd(150) as complex

.... that seems to go ok, but when I use the array the error comes, for instance:


then the error comes:

Compiler Error: Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions


I've declared a User Defined Type as follows in Module 1:

Please Login or Register  to view this content.

Then inside a sub some array variables are declared as follows (in the same module):

Please Login or Register  to view this content.

These arrays are then populated with data. I now need to pass these arrays to another function that will combine some of the relevant data from each of the arrays and return me another array of the same UDT type e.g.:

Please Login or Register  to view this content.

However; There is some problem somewhere?

I get the following error message:
Compile error:

Only user-defined types defined in public modules can be coerced to or from a variant or passed to late-bound functions.

Any ideas what's wrong or how to do this?

Having trouble with a concept in Excel 2003 VBA- trying to make an aggregate assignment to a user defined type. The compiler error leads me to believe I have a scope/visibility problem. Go easy on me, I'm an EE, not a SW guru. Code snippet to demo the problem :


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

Any help greatly appreciated. I have written a function to do the assignment, but I want/need to know why the above won't work...


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


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:


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:

Dim myArray(1 to 1000) As Integer

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

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:


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:

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
GetNth = result
End Function

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


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

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:

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:

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

object module

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

Compile Error:
Invalid inside procedure

How do i fill my array of arrays ?

Many thanks in advance

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:


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

    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


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?


Hi all

When I try to add a UDT (defined using public type in the declarations section of a module) to a collection I get a compile error along the lines of 'Only user-defined types defined in a public object module can be coerced to or from...'. Am I doing something wrong or is it just that user-defined types cannot be added to a collection at all? Sorry if I'm repeating a previous post, I searched for a while and couldn't find anything.


I am pulling some price data and tying to put it into an array

I declare my array as

Dim ER2openorders(90000) As Integer
so I am using a fixed array i think will save processing time over a dynamic array

The data comes through as a string, so in the locals window it looks like

lastprice="81400" as string
volume="10" as string
so I want to put

but I get the error "type mis-match"

I have to include the array as an integer because I want to reference the array through a public function from another spreadsheet

for some reason I can make a calculation on lastprice as string in the VB code as in executes but not post that to an array

any help please on getting that data into an array so i can reference it



I have a User Defined Function (for finding the next minimum value in a range) that is not working for nonconcurrent ranges when called from within a worksheet. It returns #VALUE!

This works: =nextmin(A11:A17)
This does not work: =nextmin(D13,E13,F13,F16)

However calling the function from within vba works:

Private Sub testIng()
    Dim sampleInt As Integer
    sampleInt = NEXTMIN(Range("D13,E13,F13,F16"))
    MsgBox sampleInt
End Sub

Here is the function:

Public Function NEXTMIN(ByVal minRange As Range)
    Dim oCell As Range
    Dim compareVal As Integer
    Dim i As Integer
    Dim a() As Integer
    Dim fistSwitch As Integer
    For Each oCell In minRange
        compareVal = Application.Min(minRange)
        If oCell.Value <> compareVal Then
            ReDim Preserve a(i)
            a(i) = oCell.Value
            i = i + 1
        End If
    a = SortArray(a)
    NEXTMIN = a(LBound(a))
End Function

If interested, here is the subsequent array sorting function I found (with link to author page):

Public Function SortArray(ByRef TheArray As Variant)
    Dim Sorted As Boolean
    Dim x As Integer
    Dim Temp As Integer
    Sorted = False
    Do While Not Sorted
        Sorted = True
    For x = 0 To UBound(TheArray) - 1
        If TheArray(x) > TheArray(x + 1) Then
            Temp = TheArray(x + 1)
            TheArray(x + 1) = TheArray(x)
            TheArray(x) = Temp
            Sorted = False
        End If
    Next x
    SortArray = TheArray
End Function

Any help is appreciated. Thanks so much.

Hi there,

I have the following code:


Option Explicit

Function BSplineCoefficients(u As Double, n As Integer, p As Integer, m As Integer, ByRef clamped_knots As Range) As Double()

Dim y As Double

Dim Coeff() As Double
ReDim Coeff(n) As Double
Dim k As Integer
Dim d As Integer

Dim x As Integer
Dim i As Integer

x = 0
x = clamped_knots(0).Value

If u = clamped_knots(0).Value Then

Coeff(0) = 1
BSplineCoefficients = Coeff()
End If

If u = clamped_knots(m).Value Then
Coeff(n) = 1
BSplineCoefficients = Coeff()
End If

Do Until clamped_knots(k).Value >= u

k = k + 1


k = k - 1

Coeff(k + 1) = 1

For d = 1 To p

    Coeff(k - d + 1) = ((clamped_knots(k + 1).Value - u) / (clamped_knots(k + 1).Value - clamped_knots(k - d + 1).Value)) * Coeff(k - d + 2)
    x = clamped_knots(k + 1).Value
    If d > 1 Then
    For i = k - d + 1 To k - 1
    Coeff(i + 1) = ((u - clamped_knots(i).Value) / (clamped_knots(i + d).Value - clamped_knots(i).Value)) * Coeff(i + 1) + _
    ((clamped_knots(i + d + 1).Value - u) / (clamped_knots(i + d + 1).Value - clamped_knots(i + 1).Value)) * Coeff(i + 2)

    Next i
    End If

    Coeff(k + 1) = ((u - clamped_knots(k).Value) / (clamped_knots(k + d).Value - clamped_knots(k).Value)) * Coeff(k + 1)

Next d

BSplineCoefficients = Coeff()

End Function

As you can see I am successully calling the values from the range on the spreadsheet into the function as a range object. However there is a small logic error and it is hard for me to solve because I don't know how to see the value of each element in the Range object from within VBA.

All I want to do is use the information from a Range as an input and then be able to manipulate it like an array once it is input. If there is a way to do this with the range object please let me know

I want to be able to see each of the individual values in the range like you can with an array object. What is the best way to do this? At the moment it just gives all the attributes and properties of the range which means nothing to me.

If there is a different way not using the Range object I'd be happy to look at this too.

Thanks in advance


If you write:
Dim i,j,k As Integer

does that mean that only one of the variables actually is declared as an
Integer (i or k) and the are declared as Variant?

Also is it true that Integer no longer really exists in VBA (at least v.
6.3)? I am told that an Integer is converted to a Long so it is better to use
Long to begin with since the program does not have to convert the Integer to

Please ansewer these questions only if you are positive (I do not mean to
come off as rude but I need to be sure). Thanks very much in advance!

I have a networked DDE connection and a TDS server running to obtain values in Excel from a SCADA system. I want to create a user defined function to simplify the functions to obtain data.

Here is the current syntax:
[code] '\\computer_name\NDDE$'|'$TDS32$'!'meter_number.parameter' [code] So an example would be:



To make things easier, I want to create a user defined function that would allow you to use the following code to get the same result:


=SCADA(23009, PD_VOL)

any help is appreciated

Here is what I tried, which does not work:

Function scada(StationID As Integer, Parameter As String)

Dim StationID As Integer
Dim Parameter As String

scada = "'\\CWH42G1\NDDE$'|'TDS32$'!'" & StationID & "." & Parameter & "'"

End Function


I am having some trouble initializing an array in VB. Here is my code

Dim LastRow As Integer, myCount As Integer
Dim EmpArray As Variant

LastRow = ActiveSheet.Range("E65536").End(xlUp).Row
EmpArray = ActiveWorkbook.ActiveSheet.Range(Cells(2, 5), (Cells(LastRow, 8)))

I am getting an application or object defined error when it is trying to set the array.

I have tried declaring the EmpArray with () but still nothing. I have also tried to ReDim the array but still nothing.

Any Help would be much appreciated.



Every now and then I get a 'ran out-of-stack space' msg, and I know it's probably due to the fact that my routine (randomf) is calling itself several times, but I don't know any other way to code this!!

So could someone please take a look at my code and make some recommendations?? maybe in terms of defining the variables, or whatever you can think of, thanks!! I appreciate it. I'm using VBA in Excel 2007

This randomf routine is simply called by a For loop N number of times (ex. 50) in another subroutine 'X'. Within the routine there are also two mini subs.

Dim m As Integer
Dim x As Integer
Dim y As Integer
Dim x1 As Integer
Dim y1 As Integer
Dim maxX As Integer
Dim maxY As Integer
Dim busy As Integer
Dim direction As Integer
Dim COORD As Variant
Dim crossindex As String
Dim coord_count As Integer
Public walk_history(1 To 30) As Variant

Sub randomf(x, y, maxX, maxY, COORD, coord_count, m)

Dim coord_check As String
Dim rng As Range

direction = Int((4 - 1 + 1) * Rnd + 1)

If m > 1 Then
Call opposite_direct(m)
End If

If m > 3 Then
If busy < 4 Then
Call linearcheck(m)
End If
End If

If direction = 1 Then
y1 = y + 2
x1 = x
ElseIf direction = 2 Then
x1 = x + 2
y1 = y
ElseIf direction = 3 Then
y1 = y - 2
x1 = x
ElseIf direction = 4 Then
x1 = x - 2
y1 = y
End If

If y1 >= 0 And x1 >= 0 And x1

I've some code which works fine if I define the size of the array explicitly but want to have the array size dependant upon the size of another variable.

Present code:

Dim Rounds As Variant
Dim Missed(44) As Integer

Rounds = Array("xx", "xy", etc..) ~ 44 values in array

What I'm after is something which will let the Missed variable array be defined as the same size as the Rounds array

E.g. something like:

Dim Rounds As Variant
Rounds = Array("xx", "xy", etc..)
Dim Missed(Ubound(Rounds)) as Integer

Any pointers would be much appreciated.


May I know the meaning of the macro below.

Dim Filename As Variant
Dim ImpRng As Range
Dim r As Long, c As Integer
Dim txt As String, Char As String * 1
Dim data
Dim i As Integer
Dim lastrow As Integer, lastrow1 As Integer
Dim checkdate

Dim lastrow As Double, i As Double, j As Double, k As Double
Dim m As Double, n As Double
lastrow = WorksheetFunction.Count(Columns(10))
j = 1
k = 1
m = 1
n = 1
For i = 2 To lastrow

Kindly assist. Thank you