Convert text value to numeric value in VBA

[Marc]

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

 

[Niek Otten]

Hi Marc,

Just declare the function a Double:

Function ExtractYear(MyFileName1 As String) As Double

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| 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
|

 

[Susan]

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

 

[Alex]

Declare your function like returning integer:
Function ExtractYear(MyFileName1 As String) As Integer

 

[Marc]

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

 

[Niek Otten]

<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

| 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
|
|
|
| > 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 -
|
|

 

[Marc]

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