Pass TextBox To Custom Function (UDF)
Mar 10, 2008
I have a simple function defined in one of my worksheets (Sheet1):
Function AddFuel(fuel As String)
MsgBox fuel
End Function
How would I be able to call this from a form button event?
Private Sub CommandButton1_Click()
Sheet1.AddFuel(TextBox1)
End Sub
Everytime I try running this code I receive the error: Run-time error '1004'; Application-defined or, Object-defined error. I've even tried Application.Run("Sheet1.AddFuel", TextBox1) but still no luck. I think this is a pretty common question but I couldn't find any answer to it on the forums.
View 2 Replies
ADVERTISEMENT
Jan 14, 2010
I have 5 worksheets each with tables that are formatted the same on each sheet. I have named each table as a named range (ex:filter5tsd15BSF). I have a userdefined function that interpolates values for a single table in a vba module. I can make this function work for a single table by calling the function as =Linterp(filter5tsd15BSF,C12) so I know the function is OK. However, I really rather program this some way that all the tables get passed to the function and then a select case for the conditions for which filter and which TSD (15 or 25) will then select the correct table.
View 2 Replies
View Related
Feb 13, 2008
I am trying to create a user form that has a series of text boxes that will all have a VLOOKUP function in them based off of input from a the first text box. If I can just get the code for the first one, I think I can figure out the rest. We will say that the file that the user form is contained in is 'Agent Administration' and the file that I want the VLOOKUP to pull from is called ' Roster for Auto Population'.
View 7 Replies
View Related
Aug 17, 2008
I used to be pretty good with VBA, and I know I am a quite decent programmer in other languages, but I have been working on this for six hours and have no idea what I am doing wrong. Any help would be appreciated.
Here is my class (I know I can do better with access modifiers/ properties in a serious version):
'The class is called ConsCell
Public Car As Variant
Public Cdr As Variant
Private Sub Class_Initialize()
End Sub
Here is the code from the module which tries to call it:
Function MakeCell()
Cell = New ConsCell 'Execution always stops here-- but with no error message
Cell.Car = 15
Cell.Cdr = "NIL"
MakeCell = Cell
End Function
Function PrintCell(c As ConsCell)
PrintCell = c.Car
End Function
The code for a cell in the spreadsheet itself is this:
=PrintCell(MakeCell())
But it gives me the #VALUE! error.
View 9 Replies
View Related
Jun 28, 2007
One of my userform text boxes is for the input of a date. this date is being exported to a defined cell in excel, but in a US format, mm/dd/yyyy. i need this to be exported into excel in UK/Aussie format dd/mm/yyyy as our accounting software is getting confused (i am also).
Private Sub TextBox3_Change()
Range("C7").Value = TextBox3.Value
End Sub
View 9 Replies
View Related
Aug 24, 2007
I have uploaded a copy of the spreadsheet I am working on and have completed the user forms and coding for march the 1st only, so if you test, please use the options march and then the 1st!
The problems i have is, when I enter a number into a text box and press next page, the numbers fill where i want them to but the cells do not recognise them as numbers, therefore conditional formatting doesnt work! Even if I change the cell properties to numbers, this does not remove the error!
Second problem!! If a user forgetts to enter a value in a box, or they wish to edit just one value, and go back into the user form to change a figure, when they press next page, all the values in the column seem to disappear!
View 6 Replies
View Related
Feb 27, 2008
I've created a variable number (i) of text boxes at run time (i also named them, "txt"&i, at the same time), i'm now trying to get the values entered in these to fit into a column that has been inserted during the same sub routine. I'm having a bit of trouble refering to the text boxes though, this is the patch of code i'm struggling with:
Dim TextBox As String
For i = 0 To 2 * NumVar - 1
TextBox = "txt" & i
ActiveCell.Offset(i, 0).Value = TextBox.Value
Next i
I also tried this:
Dim TextBox As Object
For i = 0 To 2 * NumVar - 1
TextBox.Name = "txt" & i
ActiveCell.Offset(i, 0).Value = TextBox.Value
Next i
View 8 Replies
View Related
May 9, 2008
I have a workbook that has multiple spreadsheets. Data is added to the spreadsheets using userforms for each spreadsheet. I have created a pop-up calendar to add dates and want to know if there is a way to add dates without having to create multiple calendars for each txt field on each userform. I would like it so that when I click on the calendar it would put the date into the text field it was launched from.
This is the code in the calendar which only puts the date into the field named Dat_Clsd.
Private Sub CMD_Close_Click() .....
View 5 Replies
View Related
Jul 5, 2007
I am trying to validate a textbox (txtCode) on my userform to accept the following type of entry: AB/123/07. The centre set of numbers will change & could also be four or five digits long. I have searched & found several threads but none that solve my problem.
View 8 Replies
View Related
Jun 11, 2009
I am attempting to call a Function from a cell and pass it a range and an Integer. The Integer works fine, but I cannot get the Range to pass into the function correctly.
View 8 Replies
View Related
Aug 11, 2006
If I have code like this
Private Sub cmdHex_Click()
Dim HexNum As String
HexNum = Me.txtHexVal
'Call a function
ToBinary (HexNum)
'Now do something else
End Sub
And the function ToBinary creates a value called BinVal. How do I pass it back to my subprocedure to do something with it.
View 4 Replies
View Related
Oct 11, 2006
I have a simple/dumb question... How do you "capture" a value that is returned by a custom function.
I have tried searching the forums for this & I know that I should by all rights know how to do this by now... but I just can't figure it out.
Here is a Function that I copied from Ktrasler in this thread:
Week Numbers
Public Function MyWeek(DateArg As Date) As Byte
Const BaseDate = "30/12/2001"
DateArg = CDate(DateArg) - (Weekday(DateArg) - 1)
MyWeek = Int(((DateArg - CDate(BaseDate)) / 7) Mod 52)
If MyWeek = 0 Then MyWeek = 52
End Function
I know how to pass variables to Functions, and tried this one out & it worked splendidly for my needs, but how do I take the value of "MyWeek" and use it in the subroutine that I am calling it from?
View 3 Replies
View Related
Nov 11, 2009
On the attached example, there is a user form with a text box and an enter button.
What i want, is when i press the enter button, the value in the text box goes into cell B2 on sheet1 in the format of "?/???".
View 2 Replies
View Related
Nov 15, 2013
I understand that the SumProduct doesn't have the same capabilities in VBA as it does in worksheet formulas. I am trying to construct it in an evaluate statement in VBA but the problem is I am using it in a userform, so the conditionals are passed from two combo boxes. Here is the worksheet formula
Code:
=SUMPRODUCT((Table1[Home]=C3)*(Table1[Away]=M3)*(Table1[Goals (H)]>Table1[Goals (A)]))
The values C3 and M3 refer to a cell known as "Home" & "Away". It's comparing to the two teams selected to a table with game stats to show wins and losses. The user form has a combo box list with those teams. Combo Box 1 passes to a variable known as hTeam and Combo Box 2 passes to a variable known as aTeam. I need those values passed through to C3 and M3. Here's what I constructed but get a type mismatch
Code:
Evaluate("=SUMPRODUCT((Table1[Home]=" & chr(34) & hTeam & chr(34) & ")*(Table1[Away]
=" & chr(34) & aTeam & chr(34) & ")*(Table1[Goals(H)]>Table1[Goals(A)]))")
View 4 Replies
View Related
Feb 5, 2014
Basically someone has coded a VBA script to pull a users details from AD when a button is clicked, using the staff id as the search criteria.
I've amended the criteria so it uses Environ$(USERNAME) to find details of the current logged on user, but i am struggling to get the resulting information out of the Sub to be used in other Subs! It currently just fills a cell with the required info.
View 1 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
Mar 3, 2008
I'm trying to do is pass the contents of a listbox to a function to sort it into alphabeticalorder. I get a error saying object required
Sub PopulateAll()
Dim lgLastRow As Long
Dim lgRowCounter As Long
Dim intCounter As Integer
Dim olb As MSForms.ListBox
For intCounter = 0 To UBound(vAllEnv)
Me.lstAll.AddItem (vAllEnv(intCounter))
Next intCounter
Set olb = frmOptions.lstAll.........................
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
Aug 11, 2004
I am wanting to create a custom function that i can enter into a cell to run a macro (MyMacro). I do not know how to write a function, but so far i have:
Public Function Run(MacroName As String)
Application.Run MyMacro
End Function
View 6 Replies
View Related
Dec 11, 2009
Custom average function. can this be done with Worksheet functions:
View 4 Replies
View Related
Jan 28, 2009
This just a shot in the dark, but does anyone have a custom function that calculates the check digit for a cusip? http://en.wikipedia.org/wiki/CUSIP
The algorithm is listed above.
View 3 Replies
View Related
Jun 14, 2009
Imagine i have 2 columns: Open and Close, both of these got numbers like
1 ---- 2
3 ---- 1
4 ---- 10
and so on. I had to make a function which checks if some number is in between any of those Open and Close numbers and count how many, for example: im searching for number 1.3, so according to previously drawn table i would get answer of 2, because 2 is in between 1----2 and 3-----1, i achieved this by a simple function:
=IF(OR(AND(Bendras!$J$1>=Table1[[#This Row],[Open]],(Table1[[#This Row],[Close]]>=Bendras!$J$1)),AND((Bendras!$J$1<=Table1[[#This Row],[Open]]),(Table1[[#This Row],[Close]]<=Bendras!$J$1))),TRUE,FALSE)
this generated an additional column with TRUE and FALSE values which i counted with:
=COUNTIF(Table1[T/F],TRUE)
and got the answer.
so now then preparations are ready i need to make a function which would for example if the number i was searching was in 10th and 45th rows find the MAX/MIN values of Close column between those rows(hope i made my self clear)
this is how i was hoping to do that : first of all make a new array of all cell addresses from "Close" column which were "TRUE" from the first function i wrote and when do w/e i like with those cell addresses in other functions.
View 12 Replies
View Related
Nov 21, 2011
My co-worker created a function that would calculate the standard deviation according to whatever range is selected by the user.
I was hoping to add an if statement to the formula but am getting a #value! error.
coworker's function:
Function volatility(r As Range, Optional scl As Double = 252) As Double
Dim lr() As Double
rws = r.Rows.Count
ReDim lr(1 To (rws - 1)) As Double
Dim variance As Double
[code]....
What I'd like to do with the function:
=volatility(IF((YEAR(A8:A1036)>=K3)*(YEAR(A8:A1036)
View 3 Replies
View Related
Sep 9, 2009
I have created a custom Function in excel. I would like to create a custom button so I can run this funtion with a Click of a button unstead of typing it in or clicking on the insert function button. I am not sure if this is possible to have VB call a custom function. Below is my VB for the Custom Function. I basically want the custom button to open the formula in excel.
Public Function CreateFlexstring(Company As String, Cost_Center As String, _
Division As String, Geography As String)
CreateFlexstring = Company & "-" & Cost_Center & "-" & Division & "-" & Geography
End Function
View 9 Replies
View Related
Jul 10, 2009
I have a column of data with 2 possible values, "H" or "A". The second column contains numbers (unsorted).
How do I write the VBA code to return the Highest number, where the first column is 'H' ?
Eg.
Location Attendance
H 25365
A 17436
A 47252
H 15494
A 37578
H 17549
H 28756
A 59756
This would need to return 28756.
View 5 Replies
View Related
Feb 27, 2007
I am very proficient at Excel/VBA and have a question about custom/user-defined functions that may be a little more advanced. I understand how to write custom functions and access them through the user-defined functions menu, but I would like to be able to include my function in an add-in that users could simply add, and then access the function via the 'Fx' box at the top of Excel, like they would any other built-in function, instead of having to go to the user-defined functions menu. I guess what I am looking for is how to add 'built-in' functions and not user-defined ones. I just want the user to start typing '=customFunction(' and have the parameters pop-up in tool-tip form, like any Excel built-in function would.
View 9 Replies
View Related
Nov 19, 2008
I have a calendar I have developed that highlights the current date and opens by default to the current month. Each worksheet is a seperate month. The current date highlighting is written with a public macro and the opening to current month is a private macro. The problem comes when this workbook is shared and the other users have to agree to accept my macro because of security. I would like to bypass that requirement and just have the workbook stand alone without the permission.
View 5 Replies
View Related
Apr 22, 2009
Does anyone know why you can't specify an array, like an array of Doubles, as an argument or input to a custom function? For example:
View 14 Replies
View Related
Nov 5, 2012
I have a custom function that will tell me which quarter it is based on a date entered into a cell. It works except if the cell is empty is still returns the last Case but not my Case else. If the cell is blank I wanted the function to not return anything.
Function QuarterMonth(InputDate As Date)
Dim MonthNumber As Integer
MonthNumber = Month(InputDate)
Select Case MonthNumber
Case 1
QuarterMonth = "Q1 - 13"
[Code] ...........
View 9 Replies
View Related
Feb 8, 2013
Basically i want to use a UDF to count cells in a range of a certain colour.
But i want to make it easy for the user to be able to change the colour the function counts, so i thought i could ask them to colour the cell in which the function is written.
Is there a way to tell the function to pick up the Interior.ColorIndex of the cell it is written in?
Use Application.caller.interior.colorindex
View 2 Replies
View Related
