calling functions

  • Thread starter Thread starter Jean-Yves Simon
  • Start date Start date
J

Jean-Yves Simon

Hi,

It must be trivial, but somehow I cannot find an answer for this.

I have a function:

function foo(t as string, st as integer, lt as integer) as string
....
end function


this function works perfectly by itself.

I want to build another function which calls this function

function top (.. list of params....)

t1 = foo(MyResultCopy, 58, 1)

but when I enter in a cell =top(params)

I get a #REF! in the cell where I call the top function. I assume
that inside the top function, the foo is not known or something.

I tried the excel help but whatever the keyword I choose to help
I get no example. I feel stupid not being able to figure it out.

Thanks and regards.
Jean-Yves SIMON E-mail : (e-mail address removed)
 
Hi Jean-Yves,

Difficult to say without the exact text of your code of both functions, the
formula in which the function is called and the values of the arguments!

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Niek said:
Hi Jean-Yves,
Click to expand...
Difficult to say without the exact text of your code of both functions, the
formula in which the function is called and the values of the arguments!
Click to expand...
--
Click to expand...

Hi,

Here is my code:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Function substr(N1 As String, start As Integer, lt As Integer) As String
MyResult = "0000000000000000000000000000000000000000000000000000000000000000"
N3 = N1

If Len(N1) = 1 Then
N3 = "000000000000000" & N1
End If

If Len(N1) = 2 Then
N3 = "00000000000000" & N1
End If

If Len(N1) = 3 Then
N3 = "0000000000000" & N1
End If

If Len(N1) = 4 Then
N3 = "000000000000" & N1
End If

If Len(N1) = 5 Then
N3 = "00000000000" & N1
End If

If Len(N1) = 6 Then
N3 = "0000000000" & N1
End If

If Len(N1) = 7 Then
N3 = "000000000" & N1
End If

If Len(N1) = 8 Then
N3 = "00000000" & N1
End If

If Len(N1) = 9 Then
N3 = "0000000" & N1
End If

If Len(N1) = 10 Then
N3 = "000000" & N1
End If

If Len(N1) = 11 Then
N3 = "00000" & N1
End If

If Len(N1) = 12 Then
N3 = "0000" & N1
End If

If Len(N1) = 13 Then
N3 = "000" & N1
End If

If Len(N1) = 14 Then
N3 = "00" & N1
End If

If Len(N1) = 15 Then
N3 = "0" & N1
End If

For i = 1 To 16
t = Val("&H" & Right$(N3, 1))
If (t = 0) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "0000"
End If
If (t = 1) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "0001"
End If
If (t = 2) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "0010"
End If
If (t = 3) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "0011"
End If
If (t = 4) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "0100"
End If
If (t = 5) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "0101"
End If
If (t = 6) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "0110"
End If
If (t = 7) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "0111"
End If
If (t = 8) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "1000"
End If
If (t = 9) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "1001"
End If
If (t = 10) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "1010"
End If
If (t = 11) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "1011"
End If
If (t = 12) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "1100"
End If
If (t = 13) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "1101"
End If
If (t = 14) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "1110"
End If
If (t = 15) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "1111"
End If

N3 = Left$(N3, 16 - i)

Next i

For i = 1 To start - 1
MyResult = Right$(MyResult, 63) & "0"
Next i

substr = Left$(MyResult, lt)

End Function


Function IP2(N1 As String) As String
MyResult = "0000000000000000000000000000000000000000000000000000000000000000"
IP1 = "0000000000000000000000000000000000000000000000000000000000000000"

N3 = N1

If Len(N1) = 1 Then
N3 = "000000000000000" & N1
End If

If Len(N1) = 2 Then
N3 = "00000000000000" & N1
End If

If Len(N1) = 3 Then
N3 = "0000000000000" & N1
End If

If Len(N1) = 4 Then
N3 = "000000000000" & N1
End If

If Len(N1) = 5 Then
N3 = "00000000000" & N1
End If

If Len(N1) = 6 Then
N3 = "0000000000" & N1
End If

If Len(N1) = 7 Then
N3 = "000000000" & N1
End If

If Len(N1) = 8 Then
N3 = "00000000" & N1
End If

If Len(N1) = 9 Then
N3 = "0000000" & N1
End If

If Len(N1) = 10 Then
N3 = "000000" & N1
End If

If Len(N1) = 11 Then
N3 = "00000" & N1
End If

If Len(N1) = 12 Then
N3 = "0000" & N1
End If

If Len(N1) = 13 Then
N3 = "000" & N1
End If

If Len(N1) = 14 Then
N3 = "00" & N1
End If

If Len(N1) = 15 Then
N3 = "0" & N1
End If

For i = 1 To 16
t = Val("&H" & Right$(N3, 1))
If (t = 0) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "0000"
End If
If (t = 1) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "0001"
End If
If (t = 2) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "0010"
End If
If (t = 3) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "0011"
End If
If (t = 4) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "0100"
End If
If (t = 5) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "0101"
End If
If (t = 6) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "0110"
End If
If (t = 7) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "0111"
End If
If (t = 8) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "1000"
End If
If (t = 9) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "1001"
End If
If (t = 10) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "1010"
End If
If (t = 11) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "1011"
End If
If (t = 12) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "1100"
End If
If (t = 13) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "1101"
End If
If (t = 14) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "1110"
End If
If (t = 15) Then
Mid$(MyResult, 4 * (16 - i) + 1, 4) = "1111"
End If

N3 = Left$(N3, 16 - i)

Next i
MyResultCopy = MyResult
Mid$(IP1, 1, 1) = substr(MyResultCopy, 58, 1)
Mid$(IP1, 2, 1) = substr(MyResultCopy, 50, 1)
Mid$(IP1, 3, 1) = substr(MyResultCopy, 42, 1)
Mid$(IP1, 4, 1) = substr(MyResultCopy, 34, 1)
Mid$(IP1, 5, 1) = substr(MyResultCopy, 26, 1)
Mid$(IP1, 6, 1) = substr(MyResultCopy, 18, 1)
Mid$(IP1, 7, 1) = substr(MyResultCopy, 10, 1)
Mid$(IP1, 8, 1) = substr(MyResultCopy, 2, 1)
Mid$(IP1, 9, 1) = substr(MyResultCopy, 60, 1)
Mid$(IP1, 10, 1) = substr(MyResultCopy, 52, 1)
Mid$(IP1, 11, 1) = substr(MyResultCopy, 44, 1)
Mid$(IP1, 12, 1) = substr(MyResultCopy, 36, 1)
Mid$(IP1, 13, 1) = substr(MyResultCopy, 28, 1)
Mid$(IP1, 14, 1) = substr(MyResultCopy, 20, 1)
Mid$(IP1, 15, 1) = substr(MyResultCopy, 1, 1)
Mid$(IP1, 16, 1) = substr(MyResultCopy, 4, 1)
Mid$(IP1, 17, 1) = substr(MyResultCopy, 62, 1)
Mid$(IP1, 18, 1) = substr(MyResultCopy, 54, 1)
Mid$(IP1, 19, 1) = substr(MyResultCopy, 46, 1)
Mid$(IP1, 20, 1) = substr(MyResultCopy, 38, 1)
Mid$(IP1, 21, 1) = substr(MyResultCopy, 30, 1)
Mid$(IP1, 22, 1) = substr(MyResultCopy, 22, 1)
Mid$(IP1, 23, 1) = substr(MyResultCopy, 14, 1)
Mid$(IP1, 24, 1) = substr(MyResultCopy, 6, 1)
Mid$(IP1, 25, 1) = substr(MyResultCopy, 64, 1)
Mid$(IP1, 26, 1) = substr(MyResultCopy, 56, 1)
Mid$(IP1, 27, 1) = substr(MyResultCopy, 48, 1)
Mid$(IP1, 28, 1) = substr(MyResultCopy, 40, 1)
Mid$(IP1, 29, 1) = substr(MyResultCopy, 32, 1)
Mid$(IP1, 30, 1) = substr(MyResultCopy, 24, 1)
Mid$(IP1, 31, 1) = substr(MyResultCopy, 16, 1)
Mid$(IP1, 32, 1) = substr(MyResultCopy, 8, 1)
Mid$(IP1, 33, 1) = substr(MyResultCopy, 57, 1)
Mid$(IP1, 34, 1) = substr(MyResultCopy, 49, 1)
Mid$(IP1, 35, 1) = substr(MyResultCopy, 41, 1)
Mid$(IP1, 36, 1) = substr(MyResultCopy, 33, 1)
Mid$(IP1, 37, 1) = substr(MyResultCopy, 25, 1)
Mid$(IP1, 38, 1) = substr(MyResultCopy, 17, 1)
Mid$(IP1, 39, 1) = substr(MyResultCopy, 9, 1)
Mid$(IP1, 40, 1) = substr(MyResultCopy, 1, 1)
Mid$(IP1, 41, 1) = substr(MyResultCopy, 59, 1)
Mid$(IP1, 42, 1) = substr(MyResultCopy, 51, 1)
Mid$(IP1, 43, 1) = substr(MyResultCopy, 43, 1)
Mid$(IP1, 44, 1) = substr(MyResultCopy, 35, 1)
Mid$(IP1, 45, 1) = substr(MyResultCopy, 27, 1)
Mid$(IP1, 46, 1) = substr(MyResultCopy, 19, 1)
Mid$(IP1, 47, 1) = substr(MyResultCopy, 11, 1)
Mid$(IP1, 48, 1) = substr(MyResultCopy, 3, 1)
Mid$(IP1, 49, 1) = substr(MyResultCopy, 61, 1)
Mid$(IP1, 50, 1) = substr(MyResultCopy, 53, 1)
Mid$(IP1, 51, 1) = substr(MyResultCopy, 45, 1)
Mid$(IP1, 52, 1) = substr(MyResultCopy, 37, 1)
Mid$(IP1, 53, 1) = substr(MyResultCopy, 29, 1)
Mid$(IP1, 54, 1) = substr(MyResultCopy, 21, 1)
Mid$(IP1, 55, 1) = substr(MyResultCopy, 13, 1)
Mid$(IP1, 56, 1) = substr(MyResultCopy, 5, 1)
Mid$(IP1, 57, 1) = substr(MyResultCopy, 63, 1)
Mid$(IP1, 58, 1) = substr(MyResultCopy, 55, 1)
Mid$(IP1, 59, 1) = substr(MyResultCopy, 47, 1)
Mid$(IP1, 60, 1) = substr(MyResultCopy, 39, 1)
Mid$(IP1, 61, 1) = substr(MyResultCopy, 31, 1)
Mid$(IP1, 62, 1) = substr(MyResultCopy, 23, 1)
Mid$(IP1, 63, 1) = substr(MyResultCopy, 15, 1)
Mid$(IP1, 64, 1) = substr(MyResultCopy, 7, 1)


'-- build the number

IP2 = "0000000000000000"
For i = 1 To 16
t = Right$(IP1, 4)
If (t = "0000") Then
Mid$(IP2, (17 - i), 1) = "0"
End If
If (t = "0001") Then
Mid$(IP2, (17 - i), 1) = "1"
End If
If (t = "0010") Then
Mid$(IP2, (17 - i), 1) = "2"
End If
If (t = "0011") Then
Mid$(IP2, (17 - i), 1) = "3"
End If
If (t = "0100") Then
Mid$(IP2, (17 - i), 1) = "4"
End If
If (t = "0101") Then
Mid$(IP2, (17 - i), 1) = "5"
End If
If (t = "0110") Then
Mid$(IP2, (17 - i), 1) = "6"
End If
If (t = "0111") Then
Mid$(IP2, (17 - i), 1) = "7"
End If
If (t = "1000") Then
Mid$(IP2, (17 - i), 1) = "8"
End If
If (t = "1001") Then
Mid$(IP2, (17 - i), 1) = "9"
End If
If (t = "1010") Then
Mid$(IP2, (17 - i), 1) = "A"
End If
If (t = "1011") Then
Mid$(IP2, (17 - i), 1) = "B"
End If
If (t = "1100") Then
Mid$(IP2, (17 - i), 1) = "C"
End If
If (t = "1101") Then
Mid$(IP2, (17 - i), 1) = "D"
End If
If (t = "1110") Then
Mid$(IP2, (17 - i), 1) = "E"
End If
If (t = "1111") Then
Mid$(IP2, (17 - i), 1) = "F"
End If

IP1 = Left$(IP1, 4 * (16 - i))

Next i


End Function
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Now if i have in A1 56, and in A2 =IP2(A1), I am getting a #REF! so I
am suspecting that inside the IP2 function, the substr is not seen.

Thanks in advance.

Regards
 
...
...
Now if i have in A1 56, and in A2 =IP2(A1), I am getting a #REF! so I
am suspecting that inside the IP2 function, the substr is not seen.
Click to expand...

If you're getting #REF! errors, it's because you can't use IP2 as the name of a
VBA function that you'd call from cell formulas. IP2 is a valid cell address in
A1-style referencing. Change the function name to IP_2 or anything else that
doesn't match a cell address.
 
Nothing to do with your question (which I see is already answered by
Harlan), but I would point you that the whole sequence

If Len(N1) = 1 Then
N3 = "000000000000000" & N1
End If

If Len(N1) = 2 Then
N3 = "00000000000000" & N1
End If

If Len(N1) = 3 Then
N3 = "0000000000000" & N1
End If

If Len(N1) = 4 Then
N3 = "000000000000" & N1
End If

If Len(N1) = 5 Then
N3 = "00000000000" & N1
End If

If Len(N1) = 6 Then
N3 = "0000000000" & N1
End If

If Len(N1) = 7 Then
N3 = "000000000" & N1
End If

If Len(N1) = 8 Then
N3 = "00000000" & N1
End If

If Len(N1) = 9 Then
N3 = "0000000" & N1
End If

If Len(N1) = 10 Then
N3 = "000000" & N1
End If

If Len(N1) = 11 Then
N3 = "00000" & N1
End If

If Len(N1) = 12 Then
N3 = "0000" & N1
End If

If Len(N1) = 13 Then
N3 = "000" & N1
End If

If Len(N1) = 14 Then
N3 = "00" & N1
End If

If Len(N1) = 15 Then
N3 = "0" & N1
End If


could be reduced to :

IF Len(N1) <16 then
N3 = WorksheetFunction.Rept(0, 16-len(N1)) & N1
END IF



--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
...
...
could be reduced to :

IF Len(N1) <16 then
N3 = WorksheetFunction.Rept(0, 16-len(N1)) & N1
END IF
Click to expand...
...

If this is so, it could be reduced further to

N3 = String(IIf(Len(N1) < 16, 16 - Len(N1), 0), "0") & N1
 
Harlan said:
...
..
Click to expand...
If you're getting #REF! errors, it's because you can't use IP2 as the name of a
VBA function that you'd call from cell formulas. IP2 is a valid cell address in
A1-style referencing. Change the function name to IP_2 or anything else that
doesn't match a cell address.
Click to expand...

Harlan,

That was it ! Thanks for all the help.
 
Jean-Yves,

I'm not sure wether I "understand" you correct, but I get the impression I
offended you.
I did not at all try to imply anything of "you are just a beginner".

In browsing through the postings, it just struck me that the routine could
be more compact (and as I believe easier to read) and I made an additional
posting mentioning that. If that offended you, I'm seriously sorry for
that as I in no way did intend that.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Jean-Yves,

No need to answer.

My (wrong) impression was based on the fact that I thought that you made
that (further) reduction ( If this is so, it could be reduced further to :
N3 = String(IIf(Len(N1) < 16, 16 - Len(N1), 0), "0") & N1) as well as the
remark of being just a beginner.

This was caused by the fact that I did receive Harlan's second posting in
this thread very late, far after receiving your reaction.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Back
Top