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