Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text value to numeric value in VBA
Hi,
I am having some big troubles with a quit simple VBA code. It extracts 4 digit year number out of a text string. The problem is that it returns the year as a text value no matter what I do. I have tried to use Val, Cdbl, multiply with 1 etc. but nothing helps. I dont want to use the worhsheetfunction value in excel and it is not valid in the VBE. Nor either to mutiply with 1 in excel, which also works. This should work with just this UDF! Please help me!! See code below. Function ExtractYear(MyFileName1 As String) As String Dim AntalTegn, j As Integer AntalTegn = Len(MyFileName1) If AntalTegn = 0 Then Exit Function Else For j = 1 To AntalTegn If IsNumeric(Mid(MyFileName1, j, 1)) = True Then If IsNumeric(Mid(MyFileName1, j, 4)) = True Then ExtractYear = Mid(MyFileName1, j, 4) 'ExtractYear = ExtractYear.Value Exit For End If End If Next j End If End Function Hope to hear from someone soon :-) Cheers Marc |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text value to numeric value in VBA
Hi Marc,
Just declare the function a Double: Function ExtractYear(MyFileName1 As String) As Double -- Kind regards, Niek Otten Microsoft MVP - Excel "Marc" wrote in message ups.com... | Hi, | | I am having some big troubles with a quit simple VBA code. | It extracts 4 digit year number out of a text string. | | The problem is that it returns the year as a text value no matter what | I do. | I have tried to use Val, Cdbl, multiply with 1 etc. but nothing helps. | | I dont want to use the worhsheetfunction value in excel and it is not | valid in the VBE. | Nor either to mutiply with 1 in excel, which also works. This should | work with just this UDF! | | Please help me!! See code below. | | Function ExtractYear(MyFileName1 As String) As String | Dim AntalTegn, j As Integer | | AntalTegn = Len(MyFileName1) | If AntalTegn = 0 Then | Exit Function | Else | | For j = 1 To AntalTegn | If IsNumeric(Mid(MyFileName1, j, 1)) = True Then | If IsNumeric(Mid(MyFileName1, j, 4)) = True Then | ExtractYear = Mid(MyFileName1, j, 4) | 'ExtractYear = ExtractYear.Value | Exit For | End If | End If | Next j | End If | End Function | | | Hope to hear from someone soon :-) | | Cheers | | Marc | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text value to numeric value in VBA
when i want to change a string into a number, or a number into a
string, i just make it run thru 2 or more variables (doesn't ALWAYS work, but sometimes i get lucky!)....... Sub change_2_integer() Dim ws As Worksheet Dim ExtractYear As String Dim sYear As String Dim iYear As Integer Set ws = ActiveSheet ExtractYear = ws.Range("b3") sYear = ExtractYear iYear = sYear MsgBox iYear End Sub by the time you get to iYear, it's changed from a string to an integer. the other thing is, you're telling it to be a string: Function ExtractYear(MyFileName1 As String) As String maybe you need to change the function arguments (but i don't know much about functions). hope this helps! susan On May 8, 2:57 pm, Marc wrote: Hi, I am having some big troubles with a quit simple VBA code. It extracts 4 digit year number out of a text string. The problem is that it returns the year as a text value no matter what I do. I have tried to use Val, Cdbl, multiply with 1 etc. but nothing helps. I dont want to use the worhsheetfunction value in excel and it is not valid in the VBE. Nor either to mutiply with 1 in excel, which also works. This should work with just this UDF! Please help me!! See code below. Function ExtractYear(MyFileName1 As String) As String Dim AntalTegn, j As Integer AntalTegn = Len(MyFileName1) If AntalTegn = 0 Then Exit Function Else For j = 1 To AntalTegn If IsNumeric(Mid(MyFileName1, j, 1)) = True Then If IsNumeric(Mid(MyFileName1, j, 4)) = True Then ExtractYear = Mid(MyFileName1, j, 4) 'ExtractYear = ExtractYear.Value Exit For End If End If Next j End If End Function Hope to hear from someone soon :-) Cheers Marc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text value to numeric value in VBA
Declare your function like returning integer: Function ExtractYear(MyFileName1 As String) As Integer "Marc" wrote: Hi, I am having some big troubles with a quit simple VBA code. It extracts 4 digit year number out of a text string. The problem is that it returns the year as a text value no matter what I do. I have tried to use Val, Cdbl, multiply with 1 etc. but nothing helps. I dont want to use the worhsheetfunction value in excel and it is not valid in the VBE. Nor either to mutiply with 1 in excel, which also works. This should work with just this UDF! Please help me!! See code below. Function ExtractYear(MyFileName1 As String) As String Dim AntalTegn, j As Integer AntalTegn = Len(MyFileName1) If AntalTegn = 0 Then Exit Function Else For j = 1 To AntalTegn If IsNumeric(Mid(MyFileName1, j, 1)) = True Then If IsNumeric(Mid(MyFileName1, j, 4)) = True Then ExtractYear = Mid(MyFileName1, j, 4) 'ExtractYear = ExtractYear.Value Exit For End If End If Next j End If End Function Hope to hear from someone soon :-) Cheers Marc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text value to numeric value in VBA
Hi again!
Thanks for your fast replys. It helped to declare the function as an integer instead. I guess double would work too, but it takes "more" memory. This helped me to understand some of the basics about vba. Thanks! Cheers Marc On May 8, 9:57 pm, Alex wrote: Declare your function like returning integer: Function ExtractYear(MyFileName1 As String) As Integer "Marc" wrote: Hi, I am having some big troubles with a quit simple VBA code. It extracts 4 digit year number out of a text string. The problem is that it returns the year as a text value no matter what I do. I have tried to use Val, Cdbl, multiply with 1 etc. but nothing helps. I dont want to use the worhsheetfunction value in excel and it is not valid in the VBE. Nor either to mutiply with 1 in excel, which also works. This should work with just this UDF! Please help me!! See code below. Function ExtractYear(MyFileName1 As String) As String Dim AntalTegn, j As Integer AntalTegn = Len(MyFileName1) If AntalTegn = 0 Then Exit Function Else For j = 1 To AntalTegn If IsNumeric(Mid(MyFileName1, j, 1)) = True Then If IsNumeric(Mid(MyFileName1, j, 4)) = True Then ExtractYear = Mid(MyFileName1, j, 4) 'ExtractYear = ExtractYear.Value Exit For End If End If Next j End If End Function Hope to hear from someone soon :-) Cheers Marc- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text value to numeric value in VBA
<but it takes "more" memory
You might be surprised if you could check! Excel's internal format for numeric cells is Double. So for an Integer it needs code to convert. You might actually experience a performance difference if you tested for thousands of cells. Of course the practical impact is null. -- Kind regards, Niek Otten Microsoft MVP - Excel "Marc" wrote in message oups.com... | Hi again! | | Thanks for your fast replys. | It helped to declare the function as an integer instead. | I guess double would work too, but it takes "more" memory. | | This helped me to understand some of the basics about vba. | | Thanks! | | Cheers | | Marc | | | | On May 8, 9:57 pm, Alex wrote: | Declare your function like returning integer: | Function ExtractYear(MyFileName1 As String) As Integer | | | | "Marc" wrote: | Hi, | | I am having some big troubles with a quit simple VBA code. | It extracts 4 digit year number out of a text string. | | The problem is that it returns the year as a text value no matter what | I do. | I have tried to use Val, Cdbl, multiply with 1 etc. but nothing helps. | | I dont want to use the worhsheetfunction value in excel and it is not | valid in the VBE. | Nor either to mutiply with 1 in excel, which also works. This should | work with just this UDF! | | Please help me!! See code below. | | Function ExtractYear(MyFileName1 As String) As String | Dim AntalTegn, j As Integer | | AntalTegn = Len(MyFileName1) | If AntalTegn = 0 Then | Exit Function | Else | | For j = 1 To AntalTegn | If IsNumeric(Mid(MyFileName1, j, 1)) = True Then | If IsNumeric(Mid(MyFileName1, j, 4)) = True Then | ExtractYear = Mid(MyFileName1, j, 4) | 'ExtractYear = ExtractYear.Value | Exit For | End If | End If | Next j | End If | End Function | | Hope to hear from someone soon :-) | | Cheers | | Marc- Hide quoted text - | | - Show quoted text - | | |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text value to numeric value in VBA
Ok thanks. I was just thinking about the table which shows
how many byte each variable use in memory. I have a small extra question. If there is no 4 digit year in the text string, MyFileName, I would like my UDF to return an "empty string" like: If ExtractYear = 0 Then ExtractYear = "" End If But it always returns 0 and I guess it is because of the varibale type? How can I change this so it looks like the cell is empty? In excel, i would just have used IF function that returns "" if false. Thanks in advance. Marc On 9 Maj, 14:02, "Niek Otten" wrote: <but it takes "more" memory You might be surprised if you could check! Excel's internal format for numeric cells is Double. So for an Integer it needs code to convert. You might actually experience a performance difference if you tested for thousands of cells. Of course the practical impact is null. -- Kind regards, Niek Otten Microsoft MVP - Excel "Marc" wrote in ooglegroups.com... | Hi again! | | Thanks for your fast replys. | It helped to declare the function as an integer instead. | I guess double would work too, but it takes "more" memory. | | This helped me to understand some of the basics about vba. | | Thanks! | | Cheers | | Marc | | | | On May 8, 9:57 pm, Alex wrote: | Declare your function like returning integer: | Function ExtractYear(MyFileName1 As String) As Integer | | | | "Marc" wrote: | Hi, | | I am having some big troubles with a quit simple VBA code. | It extracts 4 digit year number out of a text string. | | The problem is that it returns the year as a text value no matter what | I do. | I have tried to use Val, Cdbl, multiply with 1 etc. but nothing helps. | | I dont want to use the worhsheetfunction value in excel and it is not | valid in the VBE. | Nor either to mutiply with 1 in excel, which also works. This should | work with just this UDF! | | Please help me!! See code below. | | Function ExtractYear(MyFileName1 As String) As String | Dim AntalTegn, j As Integer | | AntalTegn = Len(MyFileName1) | If AntalTegn = 0 Then | Exit Function | Else | | For j = 1 To AntalTegn | If IsNumeric(Mid(MyFileName1, j, 1)) = True Then | If IsNumeric(Mid(MyFileName1, j, 4)) = True Then | ExtractYear = Mid(MyFileName1, j, 4) | 'ExtractYear = ExtractYear.Value | Exit For | End If | End If | Next j | End If | End Function | | Hope to hear from someone soon :-) | | Cheers | | Marc- Hide quoted text - | | - Show quoted text - | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert text to numeric value | Excel Discussion (Misc queries) | |||
Numeric in Text to convert back to the form of Numeric for VLookup Purposes | Excel Discussion (Misc queries) | |||
convert text to numeric vba add-in? | Excel Worksheet Functions | |||
Convert Numeric into Text | Excel Worksheet Functions |