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

Hi Marc,

Just declare the function a Double:

Function ExtractYear(MyFileName1 As String) As Double

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Marc" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
| 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
|

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 <marcbr...@gmail.com> 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

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

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 <A...@discussions.microsoft.com> 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 -

<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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
| 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 <A...@discussions.microsoft.com> 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 -
|
|

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" <nicol...@xs4all.nl> 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" <marcbr...@gmail.com> wrote in messagenews:(E-Mail Removed)...
>
> | 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 <A...@discussions.microsoft.com> 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 -
> |
> |