Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

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

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 ...
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 ...
Rename a Module for an Excel Macro
This Excel tip shows you how to rename a module in Excel. This is a very important thing to do when you have a larg ...
How to View, Arrange, or Hide All Shapes, Charts, Pictures, Etc. in Excel
In Excel, you can add shapes, images, objects, photos, and all sorts of items to the spreadsheet. The problem is th ...

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...

I am having difficulty with setting a loop to end when it has reached the end of an array (of objects of a user-defined class).

I will be the first to admit that the following code is not pretty, nor is it efficient. I welcome any suggestions whatsoever, but ultimately I need help getting this operation to run smoothly and effectively.

Here is the code which errors as I attempt to get the Array's elements to print out in my desired format.

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


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:


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:


Dim Dat_Array() As Shape_Export_Data_Ver_1


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

I get the following error:


Runtime Error 5: Invalid Procedure Call or Argument

While the Isarray() function



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:


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

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:

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


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)




    Close #1

End Sub


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,

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):

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,


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

I'm using EnumDisplayDevices to retrieve display information into a DEVMODE data structure.

Private Type DEVMODE
dmDeviceName As String * CCHDEVICENAME
dmSpecVersion As Integer
dmDriverVersion As Integer
dmSize As Integer
dmDriverExtra As Integer
dmFields As Long
dmOrientation As Integer
dmPaperSize As Integer
dmPaperLength As Integer
dmPaperWidth As Integer
dmScale As Integer
dmCopies As Integer
dmDefaultSource As Integer
dmPrintQuality As Integer
dmColor As Integer
dmDuplex As Integer
dmYResolution As Integer
dmTTOption As Integer
dmCollate As Integer
dmFormName As String * CCHFORMNAME
dmLogPixels As Integer
dmBitsPerPel As Long
dmPelsWidth As Long
dmPelsHeight As Long
dmDisplayFlags As Long
dmDisplayFrequency As Long
End Type

I'm interested in retrieving dmPelsWidth, dmPelsHeight, and dmLogPixels. Successful in getting dmPelsWidth and dmPelsHeight (display width and height in pixels), but dmLogPixels is returning as zero. dmLogPixels is defined as the number of pixels per logical inch. Anyone know why this would be coming back as 0?

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

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

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

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






and the final spreadsheet should look as follows;





















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