stringtype returned

[sali]

have a macro putting value "8000" [for example] to cell. notice that it is
string "8000", not number

when returned from function, like
function test1
test1="8000"
end function

and having formula in cell "=test1()" cell receives *text* type
[type(mycell)=2]

when cell modified from sub, like

sub test2
activecell.value="8000"
end sub

and invoking sub by run macro, cell receives *number* type [type(mycell)=1]

so, is there some [hidden] setting forcing text "8000" in second example not
to be converted to number when running macro?
to resolve this, in second case i need to put "'" & "8000" [simple quote in
front] to force text value. problem is that same method doesn't apply to
formula, since such quote becomes a part of returned value into cell.

is there anything to be done, or the things are simply like that?


thnx.

 

[Bernie Deitrick]

Sub test3()
ActiveCell.Value = "'8000"
End Sub

Or:

Sub test4()
With ActiveCell
.NumberFormat = "@"
.Value = "8000"
End With
End Sub

As to the formula, simply use a quote delimited string:

=IF(D1="This","8000","That")

HTH,
Bernie
MS Excel MVP