Vba Function - Output Message If Incorrect Input Data Type Used |
Vba Function - Output Message If Incorrect Input Data Type Used - Excel |
|
I have written a function that requires an integer input, i.e.
Function MyFunction(MyInput as Integer)
[...code...]
End Function
When I use the function in Excel with a non-integer input the function returns the output #VALUE!
Could someone please let me know how to modify the code so that if a non-integer input was used the function would return the message "Must use integer input"
Any help would be much appreciated.
Thanks
Ronan
Similar Topics
For 19,999 it show
Ringgit Malaysia : Nineteen Thousand Nine Hundred NinetyNine Only
For 20,000 and above it show
Ringgit Malaysia : Thousand Only
Code as below:
Function SpellNumber(amt As Variant) As Variant
Dim FIGURE As Variant
Dim LENFIG As Integer
Dim i As Integer
Dim WORDs(19) As String
Dim tens(9) As String
WORDs(1) = "One"
WORDs(2) = "Two"
WORDs(3) = "Three"
WORDs(4) = "Four"
WORDs(5) = "Five"
WORDs(6) = "Six"
WORDs(7) = "Seven"
WORDs(8) = "Eight"
WORDs(9) = "Nine"
WORDs(10) = "Ten"
WORDs(11) = "Eleven"
WORDs(12) = "Twelve"
WORDs(13) = "Thirteen"
WORDs(14) = "Fourteen"
WORDs(15) = "Fifteen"
WORDs(16) = "Sixteen"
WORDs(17) = "Seventeen"
WORDs(18) = "Eighteen"
WORDs(19) = "Nineteen"
tens(2) = "Twenty"
tens(3) = "Thirty"
tens(4) = "Fourty"
tens(5) = "Fifty"
tens(6) = "Sixty"
tens(7) = "Seventy"
tens(8) = "Eighty"
tens(9) = "Ninety"
FIGURE = amt
FIGURE = Format(FIGURE, "FIXED")
FIGLEN = Len(FIGURE)
If FIGLEN < 12 Then
FIGURE = Space(12 - FIGLEN) & FIGURE
End If
If Val(Left(FIGURE, 9)) > 1 Then
SpellNumber = "Ringgit Malaysia : "
ElseIf Val(Left(FIGURE, 9)) = 1 Then
SpellNumber = "Ringgit Malaysia "
End If
For i = 1 To 3
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
Ntow = Ntow & tens(Val(Left(FIGURE, 1)))
SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
If i = 1 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & " Crore "
ElseIf i = 2 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & " Hundred "
ElseIf i = 3 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & " Thousand "
End If
FIGURE = Mid(FIGURE, 3)
Next i
If Val(Left(FIGURE, 1)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 1))) + " Hundred "
End If
FIGURE = Mid(FIGURE, 2)
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))
SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
FIGURE = Mid(FIGURE, 4)
If Val(FIGURE) > 0 Then
SpellNumber = SpellNumber & " And Cents "
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))
SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
End If
FIGURE = amt
FIGURE = Format(FIGURE, "FIXED")
If Val(FIGURE) > 0 Then
SpellNumber = SpellNumber & " Only "
End If
End Function
Im unable to put any pics up as my work pc has java disable so photobucket ect won't work but has anyone else come across this problem and how did you rectify it? A search macro perhaps?
I have 2 colombs of data.
AT the bottom of the first, I have created a drop down menu using the "data validation" feature in excel.
At the bottom of the second colomb, I have used an "IF" function that returns a result, which depends on what value is chosen from the drop down menu in colomb 1
The problem I have is that I want the TRUE result from the IF function to be another drop down menu, being the data in colomb 2.
How do you write an IF function where the TRUE result is a drop down menu??
I tried to create a drop down menu of colomb2 elsewhere in the spread sheet, and used that cells location as the TRUE value, but this didn't work either...
ANy suggestions??
I was thinking of trying a nested if/and statement but I haven't quite figured out how to do it. Basically I want to identify the two values where it switches from positive to negative and also indentify the values where it goes negative to positive, I can then fit a straight line between them to find a better approximation of the intercept (though it might not be necessary). Preferably I'd like it all one function as I'm not doing it in VBA (I might do later though, we'll see).
Can anyone suggest how I'd find these value or the x-intercept. Any help would be greatly appreciated.
First post on the forum, have only recently joined and im hoping that someone can help me before my head explodes!
I am trying to prepare a simple Material Quantity List for some Steel materials.
The materials are all in specific sizes.
What i want to do is, as i input the length, width and height of the structure in the spreadsheet, the Material Quantity List will generate the quantity of each piece of material i will need.
Does anybody know of a formula for this. I have been trying to use the IF function and getting absolutely nowhere, all suggestions will be highly appreciated!!
The following can be used to simulate the same, and avoid having to achieve the same with VBA's IsDate function:
STEPS:
1- Format the column (ex A) as text
2- Formula to check for valid dates:
=ISERROR(DATEVALUE(A1))
format the text cells (name, etc) to have text entered as uppercase
automatically although the user might use title or lower case.
UPPER function cannot make cell look at itself and perform the function
Excel 2003
... If the time 04:16 falls in the time range 04:00 - 04:29, than put a one (1) in the filed x...
... If the time 04:16 doesn't fall in the time range 04:00 - 04:29, than leave the x fiel empty
Any help is appreciated.
a set of given numjbers. For example, 1, 3 and 8 would generate 138, 183,
318, 381, 813, 831 and so on...
I've code some VBA code which produces an output file from a template.
Now, if I want to run the output more than once, I get an message asking if I want to overwrite the existing file (which I always do).
I've tried turning off displayalerts but it's not working.
Is there any way of turning this message off?
So I am at work workin on this report and I need a function that will make Excel automatically insert the today's date in a cell (let's say D90) when I fill the content of another cell (for example C90) and the that will be inserted will never change. I tried to use the =TODAY( ) function but it keeps updating the date in the cell to the current date if i reopen the worksheet 2 days later. I need the cell to keep in the cell the date of the day when I filled in the content of cell C90 and do not updated it every time i open the document to the actual date.. Thanks in advance!!
I want it to automatically fill in today's date, when a certain empty cell has a value put in, then freeze there.
Thanks in advance!
----------------------------------------------------------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'this disables the save function on the XLS
MsgBox "****Save is Disabled****"
' Following line will prevent all saving
Cancel = True
' Following line will prevent the Save As Dialog box from showing
If SaveAsUI Then SaveAsUI = False
End Sub
I am trying to make excel automatically add a leading zero to values which are 5 digits long;
i.e. number input is 15185, then excel automatically changes it to 015185.
If I put a Customer Number Format of 0##### it works, however, a user could put any length of number into these cells, and if the number is less than 5 digits I don't want a leading zero.
Is there any way of writing a small macro to sort this out.
The numbers would be input into range B16:223.
Many thanks,
Andy
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?
=SUMIF(D3:D13,"criteria",D3:D13)
I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)
It might just be that I don't know what this means in Help:
"color" --> 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).
Can anyone help?
So if I input a date in Cell D7, then Cell B7 will receive a strikethrough.
Thanks
Louis
Alanda
I have formulas to add a number of days to a cell containing an entered date and display the resulting new date (ie. 03/01/2011 (c34)+11 = 03/12/2011)
I am seeking to leave resulting formula cell blank until a date is entered in the input cell. Currently when the input cell is empty the formula cell obviously displays 1/11/1900 using the above example.
What conditional format would achieve leaving the formula cell blank until date data in entered into the source cell?
Hopefully a simpler question for your experience level than mine.
The Geomean function doesn't work because it only uses positive numbers.
Any help would be greatly appreciated.
In simple terms, you can't enter data in cell X, until cell "Y" has been populated with something.
Can anyone help with this, because I can't figure it out....