0

I create a variant of 10 rows and 2 columns from a Range in a Worksheet with this code :

Dim ws As Worksheet
Dim leRange As Variant

Set ws = ThisWorkbook.Worksheets(1)
leRange = ws.Range(ws.Cells(1, 1), ws.Cells(10, 2)).Value

The first column is always filled but not the second one. When I check the leRange in the Locals windows, when there is nothing in the cell the value is Empty of type Variant/Empty. I would like to change it to "" of type `Variant/String.

I could loop the variant to check and replace but i would like to know if there is a smarter way to do it, thanks !

Edit : The function, which takes those datas as argument, needs a variant of strings and i can't change it so i need to do the replacement.

TmSmth
  • 450
  • 5
  • 31
  • Why? Looping will be as efficient as any alternative (not that I can see a lot of point). – Rory Nov 26 '18 at 15:35
  • This range will probably grow quickly in the future so i wanted to be sure it'll be efficient – TmSmth Nov 26 '18 at 15:38
  • 1
    Seems like premature optimization. `Variant` is a fairly efficient data type. Why spend CPU cycles in changing empty variants to empty strings unless you know that it is an actual problem? – John Coleman Nov 26 '18 at 15:45
  • 1
    @JohnColeman because the function which takes those datas asks for a variant with only string in it and i can't change it – TmSmth Nov 26 '18 at 15:48
  • @TmSmth then that is a different issue than the efficiency concern you raised in a different comment. – John Coleman Nov 26 '18 at 15:49
  • @JohnColeman i wanted to say that as i have no choice to replace it, what is the best way to do it – TmSmth Nov 26 '18 at 15:51
  • 1
    I don't think there would be any more efficient method. You could use `Evaluate` to get an array with empty strings in it but I doubt it would be more efficient. – Rory Nov 26 '18 at 15:54
  • 1
    It makes no difference. Pass a `Variant/Empty` to a function that accepts a `String`, the function merrily gets `vbNullString` - expect problems when the subtype is `Error` though, so before you pass it to the function, verify whether the variant is `Variant/Error` with `IsError`, then explicitly convert it to a `String` with `CStr` if you want. – Mathieu Guindon Nov 26 '18 at 15:54
  • 1
    Wait, *the function* - **what function**? Can we see at least its signature? "needs a variant of strings" makes no sense at all. – Mathieu Guindon Nov 26 '18 at 15:56
  • 1
    @MathieuGuindon `Pass a Variant/Empty to a function that accepts a String, the function merrily gets vbNullString` - nitpicker's corner: it will receive a zero-length string, not `vbNullString`. The difference [may](https://stackoverflow.com/a/20909528/11683) or may not be important. – GSerg Nov 26 '18 at 16:02
  • You could use the Excel's evaluate function: leRange = [if(A1:b10="","",A1:b10)] – EvR Nov 26 '18 at 16:33

1 Answers1

3

Declare leRange As String and you'll get a String that's empty. Your code has no bearing on the return type of Range.Value, that's for Excel to decide.

The problem is that leRange isn't just any other Variant/Empty here: the range isn't spanning a single cell, so what the variant contains is actually a 2D array of variants, and that's how things work. You can't change the variant subtype of individual array elements you're getting from the host appliation's object model.

Variant/Empty is not equivalent to an empty string: if a cell is effectively blank, then IsEmpty returns true. If a cell contains/evaluates to an empty string, then IsEmpty returns false.

Note that only a Variant can hold an Error value. If a cell evaluates to e.g. #REF!, the variant subtype will be Variant/Error, and IsError will return true for it - trying to assign such a value to a String will result in a type mismatch error.

Excel is giving you a variant array: your code needs to deal with a variant array. Empty easily compares to "" or vbNullString (comparison evaluates to True) - there is no problem to solve, your worry about the variant subtype is misplaced, there's nothing to worry about.

Edit : The function, which takes those datas as argument, needs a variant of strings and i can't change it so i need to do the replacement.

I'm not sure what this means, but if it means you're passing the whole 2D Variant array to a function that accepts a String() array, then yes, you need to declare a new array and copy your variants into strings, otherwise you can expect a type mismatch - this is as inefficient as you would expect it to be given a few hundreds/thousands of cells, but if this is about the handful of cells involved in the OP, it shouldn't be too bad.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • thanks for your detailed answer ! (sorry for the first delete, i saw your edited your post in the meanwhile) – TmSmth Nov 26 '18 at 15:59
  • Regarding the function it's a matlab function in their excel add-in and the function definition is something like `functionName(Optional variableName as Variant)`. I have an error when the variant is not full of string, they probably iterate on the variant and don't accept other thing than string i guess – TmSmth Nov 26 '18 at 16:12
  • 2
    @TmSmth possible. In that case you can declare a `String()` array, and populate it with your cell values - there's no shortcut for that, unfortunately. Next time, make sure your question includes *all* the relevant context ;-) – Mathieu Guindon Nov 26 '18 at 16:15
  • Sorry for that ! I've accepted your answer as you give some interesting details. – TmSmth Nov 26 '18 at 16:22