Email:      Pass:    Pass?

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

Similar Excel Video Tutorials

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

My question is about creating User-Defined Data Types. Is there ANY way possible i can create a User-Defined Data Type that declares a variable of another User-Defined Data Type instead of the Pre-Defined User Types like String, Integer, etc?

The following explains my problem in more detail.

I know to create a User-Defined Data Type at the top of the module before any procedures. Like this:


Type CustomerInfo
    Company As String
    RegionCode As Integer
End Type

In a procedure I would then declare a variable as that type using a Dim statement. Like this:


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

So, in the first code example above I created a User-Defined Data Type called CustomerInfo. And in that User-Defined Data Type I declared the variable Company and RegionCode as a String and Integer, respectively. String and Integer being, of course, Pre-Defined Data Types.

Then in the next code example I declared the variable in a procedure.

All that works just great.

BUT... Is there any possible way to create a User-Defined Data Type with a variable that is declared not as a Pre-Defined Data Type, like String and Integer, but instead is declared as another User-Defined Data Type?

It's my understanding an example would look like this:


Type CustomerInfo
    Company As String
    RegionCode As Integer
End Type

Type EveryCustomer
      EveryCompany As CustomerInfo
End Type

The above would come at the top of the module, before the procedures. The only problem with this is that I didn't declare the variable EveryCompany using a Dim statement. That's something you have to do when using a User-Defined Data Type but not when using a Pre-Defined Data Type. However, the VBE won't allow me to use a Dim statement before the first procedure.

So, in light of this, is there ANY way possible i can create a User-Defined Data Type that declares a variable of User-Defined Data Type instead of the Pre-Defined User Types like String, Integer, etc.

Is there any workaround? I really want to create a hierarchy of Data Types...

Thanks for your help in advance!!! I really appreciate the guidance!


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?

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


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,

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

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




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

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.

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

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 ?


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