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 RepliesI have dates & times in column A1:A20. In B1:B20 I have the corresponding temperatures for each date. I have set up the following dynamic ranges to refer to these ranges.
DateRange refers to A1:A20
TempRange refers to B1:B20
I have also made a user defined function that will determine if a date/time is between two times. Eg., If 21/05/06 07:30 is between "07:00" and "17:00". This function isn't concerned with the date, just if the time falls between the start and end times.
Function BetweenTimes(dDate As String, dStartTime As String, dEndTime As String) As Boolean
dDate = CDate(dDate)
dStartTime = CDate(dStartTime)
dEndTime = CDate(dEndTime)
BetweenTimes = False
'If the end time is before the start time, see if date/time falls between start and end..........
I have a cell B10 which contains a formula.
I would like a macro which copies this formula across the same row.
However, the macro should prompt the user to select a range.
Based on the range selected and in particular the columns in this selected range, I would like the macro to copy the formula only for the columns specified in the selected range.
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
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.
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
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
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
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 ?
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.
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"
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).
My workbook has sevaral sheets reresenting the payment methods used by our customers. Each sheet has a range of cells F9 to Q33 which should hold the value of payments for each working day. e.g. F9 represents April 1st, F10 represents April 2nd.
A daily list of values is supplied which then transfers that day's value into cell E1 on each sheet.
On each sheet I manually have to take the value in E1 and copy and paste special: value into that day's cell e.g. today I will paste into cell K21. The cell value then looks like this '12134.12'. I then edit the cell to put a calculation on the end to divide the value by the value in another cell on the sheet. The cell value ends up like this '=12134.12/$G$5'. This is so I can see the values in thousands of pounds or by changing the value of G5 to 1,000,000 in millions.
Tomorrow I will do the same but in cell K22.
I have to do this on 15 worksheets and I have been struggling to get a macro together to do this. I can get as far as copying and pasting but I don't know how to add the calculation onto the end. I also would like to be able to input the cell destination daily probably with an input box so I can be flexible and potentially run it sevaral times if I need to catch up on previous days.
I'm trying to enter a series of formulas into a worksheet using vba. However, this code is giving me Run time error '1004', along with the description in the title. This is the first formula (they're all relatively similar).
View 2 Replies View RelatedPossibility 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?
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 RelatedI 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
I have created a custom add-in with custom functions and am trying to make it user friendly for other users. By running the following macro I can add functions to different categories, but I was wondering if there was a way to do this automatically so that anyone that adds this add-in will have the functions show up in the proper category without having to manually run this macro.
View 6 Replies View RelatedMy goal is a macro that exports a .txt file where the user selects desired rows (strains, not necessarily contiguous), header columns (collection data), and a single sequence column. The output format (called fasta) is:
>headerA1 headerC1
sequence1
>headerA4 headerC4
sequence4
where each new ">" is a new strain (row). My code so far gets caught up in the string assignments in the for loops:
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:
I can't get a simple function to work. I attached a screen shot of my function. It's very simple -- adds two numbers.
View 4 Replies View RelatedThis is what I'm trying to achieve: Display a list box (or any other appropriate form/active X control) to get input from the users which is usually a list of several terms. Once they've input the list, upon clicking 'OK' it should add them all to Col. B.
The above request WILL be a good solution, but if there is an advanced solution to this for eg., something like a list box with buttons, for instance: click on 'Articles' and it populates 'a,' 'an,' & 'the, click on 'Prepositions' and it populates all the pre-fed prepositions, etc. that would be PERFECT!
I am trying to write a function which return the difference of sum of range A and sum or range B. But I keep getting error with my codes.
Code:
Function deltaPipeline(YTDPplComm As Range, LastYearPpln As Range)
deltaPipeline = Sum(YTDPplComm) - Sum(LastYearPpln)
End Function
I found out that using Enum one can have a dropdown box to choose from when inserting a parameter in a function.
But how can I have the same user-defined constant in more then one Enum? It works with standard VBA variables like vbEmpty, so why not with self made constants?
I tried this which gives the error: Ambiguous name detected
Code:
Private Const vbTest = 99 ''' = Ambiguous name detected !!!
Enum eTest1 '''gives 3 choices
vbEmpty ''' = standard VBA 1
vbLong ''' = standard VBA 3
vbTest ''' not in VBA, so I would assign 99 to it
End Enum
[Code] .....
I have a mental block when it comes to writing functions.
In a cell I have the Post Code followed by a "#" sign (no speech marks) and sometimes more text
e.g. FY6 8JF#ABCD (where the Post Code is FY6 8JF)
The "#" sign always indicates where the Post Code ends, i.e. I want to return all the characters to the left of the "#" sign.
In a spreadsheet I would accomplish this with "=LEFT(AA14,FIND("#",AA14,1)-1)"
where the string of interest is in cell "AA14"
How do I write a UDF to do this?
I am stepping through a sub in module one that for some reason keeps jumping to a user defined function in another module. I've checked and and the function isn't being called.
View 9 Replies View RelatedWhat I need is a Macro that will allow the user to enter a date (ex: 2/14/2008) and then look in column H for any dates prior to the user entered date and higlight the corresponding cell in Column A.
I created the macro below from one in another workbook of mine and when I run it, I get a Run-Time error '13' Type Mismatch. When I go to Debug this code is highlighted in yellow: lookVal = InputBox("Securitization Date")
How can this be fixed to accept a date entry in the Input Box?
Sub SecuritzationDate()
Application.ScreenUpdating = False
Dim lookVal As Long
lookVal = InputBox("Securitization Date")
For i = 2 To Range("H" & Rows.Count).End(xlUp).Row
If Range("H" & i).Value < lookVal Then
Range("A" & i).Interior.ColorIndex = 40
End If
Next i
Application.ScreenUpdating = True
End Sub
I need to create a business days calendar consisnting of two columns start_date and end_date. Start_date is today (always a work day), end_date column will have to be start_day +1 day, start_day +2 days, start_day +7 days, start_day +30 days, and so on till + 3600 days. Every end_date must fall on a business day, or the first following business day if it is falls on a WE. Assume I know all holidays for the next 10 years. I could use Workdays or other built in function, the trouble is they all assume Sat and Sun as WE, my WEs are FRI and SAT! I tried to write a UDF but I'm failing miserably with too many ifs and elses,
View 9 Replies View RelatedI would like to alter this code so that the user chooses the column where the numbers are. They aren't always in column D....
View 9 Replies View Relatedhow to return value from VBA User Defined Funtion
View 9 Replies View Related