ExcelBanter - Convert text value to numeric value in VBA

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Convert text value to numeric value in VBA (https://www.excelbanter.com/excel-programming/388983-convert-text-value-numeric-value-vba.html)

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

Convert text value to numeric value in VBA

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

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

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 -

Convert text value to numeric value in VBA

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