Pass Element Of User-Defined Data Type
Oct 17, 2007
I have an array with structure, i.e. User Defined Type. Assume the user defined type has two elements: Element1 and Element2 and array name is Array.
So the definition is:
Dim Array(1 To 10) As UserType
and access to elements is
Array(5).Element1
The problem is that I need to pass the whole set of Element1 or Element2 to a function. Should it have been two separate arrays, it would not be a problem. But because of the user defined structure I have no idea how to pass a single element.
I hope there is another solution rather than to use loops. I have many arrays like this with complex structures. I simply can not replicate all of them.
View 9 Replies
ADVERTISEMENT
Jun 23, 2006
I can create my own new collection which is handy as it accepts uniques only and i can access using its "key"
But can i have a collection of "user defined data types"
View 5 Replies
View Related
Oct 9, 2012
Trying to convert an Excel 2003 macro to work in Excel 2007.
The problem line is
Dim MyDataObject As DataObject
I suspect the problem is a Missing Reference, but I cannot figure out which one. I have the same ones (in 2007) as 2003 except for one which is not showing
Microsoft Forms 2.0 Object Library
Is this the one it needs? It is called something else in 2007?
The ones I do have ticked are
Visual Basic For Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library
Microsoft ADO Ext. 2.8 for DDL and Security
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.8 Library
Microsoft Scripting Runtime
Microsoft XML v2.6
View 8 Replies
View Related
Jul 29, 2014
I am using Microsoft Excel 2010 and Microsoft VBA 7.0 on my system. I would like to eventually create a PowerPoint and insert charts generated in the Excel workbook. In the meantime, I cannot get the basic PowerPoint created.
The line in red is highlighted blue when the compile error "User-defined type not defined" message box appears.
Public Sub TryAgain() Dim myPowerPoint As PowerPoint.Application
'
' do nothing for now
'End Sub
I have set the references such that Microsoft Project 14.0 Object Library is indeed checked. The Excel file only contains this code in a module. All sheets are blank. Nothing else is written yet.
View 1 Replies
View Related
Nov 28, 2006
I had a working subroutine which I moved to another workbook (rather than linking to it and having both workbooks open at the same time).
Now I get "user-defined type not defined" when it runs. Here is the beginning of the
Global oApp As Object
Sub UpdateForecasts()
Call Update1
Call Update2
End Sub
Sub Update1()
Dim dPath As String
Dim dDest As String
Dim dCondition As String
Dim dName As String
Dim FSO As New FileSystemObject
View 9 Replies
View Related
May 5, 2013
Base 64 Encoding.
Dim objXML As MSXML2.DOMDocument
Dim objNode As MSXML2.IXMLDOMElement
Are causing the User Defined error, but the same workbook is OK in XP and Win 7.
VB6: Free, Easy and Quick Base64 Encoding and Decoding in Visual Basic [a NonHostile article]
Says you need a reference to Microsoft XML, v2.6 (or later) and the Win8/2010 workbook does have one for Microsoft XML, v6.0
View 1 Replies
View Related
Mar 4, 2010
I am trying to capture with Worksheet Change a command bar action like paste but I am getting an error :
“User-Defined type not defined”
Do I need a library of sort in References ??
Public Sub Right_Click()
Dim oControl As CommandBarControl
For Each oControl In CommandBars("Cell").Controls
Debug.Print oControl.Caption
If oControl.Caption = "&Paste" Then
oControl.OnAction = "MyPaste"
End If
Next oControl
End Sub
View 9 Replies
View Related
Jun 27, 2008
When I try to complile my VBA project, I get the following error "User-defined type not defined" but the compiler doesn't point to any line.
I remember what I did last was to rename a form and a Module, but I replaced their old names in all my code with the new ones ... Does this have anything to do with the error I am getting ?
View 9 Replies
View Related
Dec 28, 2006
I am trying to write a User Defined Function in VBA to perform a simple two-dimensional table lookup. I have the temperature distribution in a solid given in a table in Sheet2. The first column of the table contains the time values, the first row contains the spacial values (radii), and the intersections contain temperature values. These ranges are named times, radii, and temperatures. In Excel, I can perform the lookup using: =index(temperatures, match(time, times, 1), match(radius, radii, 1))
But how do you do this in VBA? More specifically, how do you deal with passing ranges to the WorksheetFunctions?
Function temperature_ref(time_range As Range, radii_range As Range, temperatures_range As Range, time As Double, raduis As Double)
r = WorksheetFunction.Match(time, time_range, 1)
c = WorksheetFunction.Match(radius, radii_range, 1)
temperature = WorksheetFunction.Index(temperatures_range, r, c)
End Function
This function would be called (from any worksheet) as..................
View 4 Replies
View Related
Dec 11, 2012
Which I am trying to modify to fit my needs:
Code:
Sub Test()
Const cURL = "Website Here" 'Enter the web address here
Const cUsername = "XXXXXX" 'Enter your user name here
Const cPassword = "XXXXXX" 'Enter your Password here
Dim IE As InternetExplorer
Dim doc As HTMLDocument
Dim LoginForm As HTMLFormElement
[Code] .......
I get the error User Defined type not defined on line:
Code:
Dim IE As InternetExplorer
Do I need to use references? Is there a quick fix for this?
I have code that works for a particular website but I am having trouble making it work for others. This code seems more flexible than the other code:
Code:
Option Explicit
Public Sub Press_Button()
'make sure you add references to Microsoft Internet Controls (shdocvw.dll) and
'Microsoft HTML object Library.
'Code will NOT run otherwise.
[Code] ........
My end goal is to log on to several websites and pull data out of tables on the websites back into excel so that I can have a one stop shop instead of logging into many websites manually.
View 5 Replies
View Related
Aug 28, 2008
This code works fine when I use it, but not when a co-worker uses it on his PC. The code and VBA references are the same. He gets the "User-Defined Type not defined" error. I'm wondering if there is an Outlook setting that may blow this up.
the code was working on his PC and then it stopped though no changes were made to the code. That's why I think it may be blocked by an Outlook setting.
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
MailOutLook.Subject = "E-mail"
View 9 Replies
View Related
Dec 23, 2009
I am trying to call a function that calculates forecast error (wMAPE). This function needs to be able to handle passing in non-contiguous ranges. I can't seem to figure out how to do that.
Function wMAPE(Forecasts As Range, Actuals As Range, Weights As Range) As Variant
Dim Denominator As Double
Dim Numerator As Double
Dim i As Long
Dim Fcst As Variant
Dim Act As Variant
Dim Wt As Variant
If Forecasts.Cells.Count Actuals.Cells.Count Then MsgBox ("Error: Arrays not same size")
If Forecasts.Cells.Count Weights.Cells.Count Then MsgBox ("Error: Arrays not same size")
Denominator = 0............
View 9 Replies
View Related
Nov 3, 2004
I am currently working in an application in which I use the following user defined types:
Public Type correlationstruct
corr As Variant
corrstd As Variant
lower As Variant
upper As Variant
support As Variant
End Type
I have a public variable called myctafund, of type ctafund:
Public myctafund As ctafund
My problem is the following: When I need to update myctafund, I first want to clean it entirely before putting new values, so as to be sure older values do not stick around (I do not necessarily overwrite all the values of the mycta type).I can't figure out a way of clearing the entire myctafund variable in one shot. I tried:
myctafund=empty
but I am granted by a nasty excel error. I'd like to avoid writing 200 lines of code just to clean the entire variable.
View 9 Replies
View Related
Apr 18, 2007
I am trying to figure out how to create a "User-Defined" enumerated type typically used in other languages (other than VBA).
for example in Java a typical enum type is declared like this:
enum Season { WINTER, SPRING, SUMMER, FALL }
This gives the benefit of writing "readable" code.
(back to vb)
If Season SUMMER then
call wear_a_sweater
end if
View 9 Replies
View Related
Oct 2, 2007
I am trying to pass information that is filled by user in a userform into an excel sheet. Let's say a user would click on a control button in a userform and Macro would ask him what value to store for the first variable. If user clicks one more time then Macro would identify that it was a second click and ask what value to set for a second variable. It is easy to do with limited number of variables, but is it possible that the variable which stores a number of clicks would become a number for variable to store the value?
1 click - a1 = ..
2 click - a2 = ..
....
n click - an = ..
If not possible - which way to search a solution?
View 5 Replies
View Related
Dec 31, 2009
For some reason get a type mismatch error on when I try to determine the rth member of summation range. I have highlighted the relevant part of the code in bold. It is strange as I can obtain the address.
If ((All_nurse_names(r, 1) = nurse_name) And (All_status(r, 1) = status)) Then
Debug.Print "test " & r & " " & summation_range(r).Address & " " & val(summation_range(r))
total_hours_in_shift = total_hours_in_shift + (summation_range(r))
End If
View 9 Replies
View Related
Apr 12, 2009
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:
View 6 Replies
View Related
May 3, 2007
I have a spreadsheet which links to an external source, runs a sql msquery and retrieves data based on dates selected by the user from two drop down lists. From Date and Date To.This works fine. However I also need to total any fixed data which resides in the same spreadsheet based upon the same dates selected. The end user selects 2 dates , say 15/04/2007 (this relates to a week number,week15)and 28/04/2007(week 17)
A column of data lists the week numbers (in cells A3 - A22) and next to this their respective production quantities in cells B3-B22.
I now have a problem in totaling the production quantities in the worksheet as my user is not just selecting the week numbers 15 and 17 but 15,16 and 17.
qty
week 15100
week 16123
week 1789
How do I sum from 15 to week 17 inclusive or any other range selected?
View 7 Replies
View Related
Apr 2, 2009
I have a workbook that is used to track overtime hours worked through the year. I've received some help from users on this forum, and others, to create the necessary macros and formulas to allow a supervisor to make use of the form; however, the workbook is also used to track those that wish to volunteer to work overtime each weekend. The sheet has a list of names for the employees on each shift, then a column to allow a "Y" to be placed under the date - designating them as a volunteer for any overtime available. I've found this spreadsheet (attached - from this site) that will allow individual users to access the workbook and have unique passwords for each. I don't completely understand how it works, but I feel it may be a start to getting what I want.
Essentially, once the user selects their name and inputs their password, I'd like the workbook to be opened and then allow them to only manipulate the row associated with their name. That row will change from week to week, so specific numbers will not be able to be used, but their name will be the same as the name used in the logon.
I feel like I'm asking for a lot here, so just some initial guidance on whether this type of thing is possible would be an acceptable answer.
View 9 Replies
View Related
Jun 3, 2014
I have declared a 'long' variable. however; how do I change the variable data type to string if user input is not a number? how do I recognize the user input's datatype?
View 7 Replies
View Related
Oct 7, 2012
I have a financial dataset which I need to "clean" before manipulating/analysing.
Each row of the data represents a completed transaction and the first step is to delete rows that are done with particular (internal) clients; the client data (the client's name) is in Column D.
Currently, I delete the unwanted data by autofilter and delete (code below), however this means the clients to be deleted are only defined within the code. I would prefer to have a worksheet within the workbook where the user defines the clients by adding or subtracting their name from a list.
I have created a dynamic range for the client list by inserting a Named variable along the lines of "=OFFSET(!$B$3,1,0,COUNTA($B$3:$B$200)-1," which works fine.
However how do I work this list into my code so that it works when new clients are added or deleted?
Current code for deleting unwanted client data:
Sub filterdelete()
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
With Range(Range("D1"), Range("D" & LastRow))
[Code] .....
View 2 Replies
View Related
Mar 25, 2008
I want to create a user defined function to search a column of data for a part number.
If it exists I want to have a the UDf returna "fail" otherwise "pass"
Here is the code I was trying to use
Function firstpass(SN As String) As String
ws = Worksheets("Defects")
c = ""
With ws.Range("a1:a9999")
Set c = .Find(SN, LookIn:=xlValues, lookat:=xlWhole)
End With
If Not c Is Nothing Then
firstpass = "Pass"
Else
firstpass = "Fail"
End If
End Function
This function only returns a "#value" and I don't quite know how to troubleshoot it.
View 9 Replies
View Related
Sep 16, 2006
I've made a macro that inserts an entire new row above a specified cell, then updates each column of that row using offset.value and various constants and inputbox variables. Works great so far, unless I try to apply a validation rule to one of the cells.
The code I'm using to add the validation works okay when referring to a specific cell by reference e.g:
With Range("A1").Validation
etc...
However, when I change it thusly:
With Sheet2.Range("LastRow").Offset(-1, 5).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Departments"
.IgnoreBlank = True
.InCellDropdown = True
End With
It doesn't like that one bit.
This is part of a private Sub attached to a button in the same sheet. I've had a scan through the archives looking for validation threads, but couldn't find anything that refers to problems with offsets (if indeed that is the problem here).
View 9 Replies
View Related
Jun 24, 2006
I have created a custome User Form with three option buttons inside a frame. How do I set the value for each button and then how do I pass that value back to the code that opened the form (after closing the form upon a selection?
Sub Test2()
UserForm1.Show
Select Case List
Case 1
Range("b1").Value = "List 1"
Case 2
Range("B1").Value = "List 2"
Case 3
Range("B1").Value = "List 3"
End Select
End Sub
in the form I have the following code associated with each of the butons (but I do not think I have it right). Isn't there a way (becasue I have them in a frame) to shorten the code?.................
View 8 Replies
View Related
Sep 9, 2006
I have an existing macro which I am enhancing and I would like to have the user provide a date, either with a popup text box in a userform and then use a command button to hit OK and have the box dissapear, or in a calender which the date is selectable, and the date supplied go into a variable.
View 2 Replies
View Related
Jul 17, 2013
I am trying to display number of lines which depends on value pass by user. Actually user is passing some value. On which some group of lines has to be display.
E.g.
for 1 value 9 lines
for 2 value first 9 lines + another 9 lines
for 3 value first 18lines = 9 lines
and so on till 52.
View 2 Replies
View Related
Jul 8, 2009
I'm having trouble getting a User Defined formula to work. I received an email containing a User Defined formula for SUMCOLOR and COUNTCOLOR. The first sums all the values in like-colored cells, and the second counts all the cells of like-color in a given range. The VB code for the module is as follows:
View 2 Replies
View Related
Jan 2, 2007
Possibility of creating "on measure" functions for the necessities of every customer, but I have some things to ask in merit and I hope that you can answer to me:
- which are the differences between a User Defined Function and a Macro?
- also User Defined Function is written in Visual Basic?
View 9 Replies
View Related
Jan 21, 2007
Can you create user-defined events in excel? I ask because the worksheet_change event does not capture pastes or undo/redo.
View 4 Replies
View Related
Jul 14, 2014
I am trying to create a function using the functions of IF and "And", but for any reason I don't know how to add the Add function
My function written in excel will looks like =IF(AND(A80="00",C80<>"8300"),"yes","no")
LOB Cost Center
00 1000
00 2000
00 8300
01 5000
02 8300
Function Allocation(LOB As Integer, CostCenter as Integer) As String
If LOB = 00 And CostCenter <> 8300 Then
Allocation = "Yes"
ElseIf LOB = 1, 2, 3, 4, 5 Then
Allocation = "No"
End If
End Function
View 3 Replies
View Related
