Define Variable using UDF?

[CVDK]

Hi, I thought I could do this thing but no. Therefore I'm looking for help from someone smarter than me.

I'm creating "adress labels" and "order labels" using VBA macro based on a list in matrix form.

I've used links to the list which I copy and paste to another sheet that's going to be printed by someone. Doing this all the way throug the list could take som time. Therefore I created a macro to do the job. I use the macro to edit/correct the links so the labels are made using every row.

In order to correct the links and use the next row i used a function (ExtractNumber, found on Ozgrid.com, thank you!) to extract numbers from strings. I converted the formula/link to a string and put it in a variable, Cformula.

I Dimmed my variable HNumber and tried to define it using the function which did not work. I get the error messag ByRef argument type mismatch. Actually no matter what I put in the () I get the same error message.

HNumber = ExtractNumber(Cformula)

If I use the code below, there's no problem

HNumber = Right(Cformula,2)

Unfortunately there can be both 1, 2 and 3 digits. I could use a select case or if statement but I just want to understand why I'm having problems.

 

[RoryA]

How is CFormula declared? As String or Variant or other?

 

[CVDK]

CFormula is declared as String but I also tried variant. HNumber is also declared as String.

The Extractnumber function output is declared as Double if I understand the below correct?

Function Extractnumber(rCell As Range, _
     Optional Take_decimal As Boolean, Optional Take_negative As Boolean) As Double

This is what VBA help has to say about the error:


"An argument passed ByRef (by reference), the default, must have the precise data type expected in the procedure. This error has the following cause and solution:

• You passed an argument of one type that could not be coerced to the type expected. "

 

[RoryA]

There's your issue then - ExtractNumber is expecting a Range, not a String.

 

[CVDK]

So the problem is that the content of the cell is a formula, which I can't turn into an object. I can define the activecell as an object but it only works when the activecell does not contain a formula or it takes the formula output and extracts the number(s) from that.

I guess the solution is to rebuild/change the function?

 

[RoryA]

If the formula is in a cell, then you can probably just pass the cell to the function rather than its formula.

 

[CVDK]

Ha, it worked. Thank you for your inspiration.

It was incredibly simple, I just added ".formula" to the input string variable definition of the ExtractNumber function. (Did that make sense?)

 

[RoryA]

Not really, but if it works that's fine.