VBA: Is there a way to use an image in the formula in VBA?

[datastudent]

Hi,

I have this data where a circle is used to identify yes or no. In regular excel formula I can copy ang paste the image inside the formula but in VBA the image is not recognized. If its not possible, please give me suggestions on how I can work around it. Thank you!

In here the circle image turn into a question mark.
Range("L2").FormulaR1C1 = "=IF(RC[-4]=""?"",""N"",""Y"")"

Template_v1.xlsm
	H	I	J	K	L
1	EMS	Portability
2	○	○			N
3	●	●
iBASIS_IIS

Cell Formulas
Range	Formula
L2	L2	=IF(H2="○","N","Y")

 

[Micron]

In VBA you could try using Sheets("Sheet1").Range("A2") = ChrW(&H2710)
The character code in brackets comes from a chart (Dingbats) like this

https://www.unicode.org/charts/PDF/U2700.pdf

from here

Unicode 16.0 Character Code Charts

 

[datastudent]

In VBA you could try using Sheets("Sheet1").Range("A2") = ChrW(&H2710)
The character code in brackets comes from a chart (Dingbats) like this

https://www.unicode.org/charts/PDF/U2700.pdf

from here

Unicode 16.0 Character Code Charts

Okay so I see that the black circle character code is 25CF. How do I use that in my IF formula?

 

[Micron]

How do I use that in my IF formula?

I have no idea. I took it that you were trying to use code and that was your problem.

but in VBA the image is not recognized

If you're using vba you don't need a formula?

 

[datastudent]

I have no idea. I took it that you were trying to use code and that was your problem.

If you're using vba you don't need a formula?

If I don't use a formula, how do I convert the circles into word format in VBA?

 

[Micron]

What is "word format", Microsoft Word format? Probably not. I think you're asking how to make use of the code example I gave you in post 2.
Then you need an event to trigger that code to run and that will depend on how you want to interact with the sheet. One option would be to double click the cell which would run beforedoubleclick event and insert the character. If you need different characters then you'll need to handle it differently. One idea might be to enter a particular character (e.g. numbers - 1 for black circle, 2 for white circle, nothing for some other character, and so on). Then when the code runs it first checks what's in the cell and acts accordingly. Again, it all depends on what you want in the cell and how you want to make it go there.

 

[FormR]

How do I use that in my IF formula?

Hi, something like this:
Range("L2").FormulaR1C1 = "=IF(RC[-4]=""" & ChrW(9675) & """,""N"",""Y"")"

 

[datastudent]

Hi, something like this:
Range("L2").FormulaR1C1 = "=IF(RC[-4]=""" & ChrW(9675) & """,""N"",""Y"")"

It work!

I do want to ask though where and how did you get the 9675 for my future reference.

 

[FormR]

how did you get the 9675

Hi, with the UNICODE() function.

Book1
	H	I	J
1	EMS	Portability	Here ↓
2	○	○	9675
3	●	●	9679
Sheet1

Cell Formulas
Range	Formula
J2:J3	J2	=UNICODE(H2)

 

[datastudent]

Hi, with the UNICODE() function.

Book1
	H	I	J
1	EMS	Portability	Here ↓
2	○	○	9675
3	●	●	9679
Sheet1

Cell Formulas
Range	Formula
J2:J3	J2	=UNICODE(H2)

Thank you so much! I appreciate you!