Variable Name typed in Cell can be used in VBA or not?

Ashish8859

New Member
Joined
Feb 9, 2009
Messages
5
Hi ,


I am new to vba. I have simple thing to do but don't know how to do it. I have a simple code which is as follows:
Sub Test()

Dim Test1 As Long
Dim Test2 As String


Test1 = Range("J7") + Range("J8")

Test2 = Range("K7") + 2

MsgBox Test2

End Sub

I have define two variable Test1 & Test2. Test1 is simple addition of J7 + J8 (J7 = 1, J8 = 2) = 3. Then i have written Test1 in Cell K7 and in Test2 i have written the above formula. The answer must be 3 + 2 = 5, but when i ran the code it give error as Error 13 Type Mismatch.

Please can some one help me out with this. This is just a test check, actually i have written more than 20 variable names in excell cells and i want them to be used in Excel vba. What it should exactly do is treat Range("K7") as Test1 and take the result of Test1 i.e. 3 and add 2 to it.

if i use Test2 = Test1 + 2 then it give 5 as result, but when i reference Test1 from the cell K7 it gives error.


Thanks & regards,

Ashish
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
That can not be done, the variable names need to be set in vba, not in a cell.

There might be ways to work around it using different methods but I wouldn't recommend trying it.
 

Ashish8859

New Member
Joined
Feb 9, 2009
Messages
5
Thank for the reply.........

But i can you tell me the work around please.........


It will be a great help for me.........

Thanks & regards,

Ashish

That can not be done, the variable names need to be set in vba, not in a cell.

There might be ways to work around it using different methods but I wouldn't recommend trying it.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
But i can you tell me the work around please.........
I didn't say that there is a workaround, I said that there might be a workaround. There is a difference!

The initial idea that I had was to assign the calculations to named ranges, but giving it further thought, it is not going to work.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,844
Messages
5,766,748
Members
425,378
Latest member
kapoor2892

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top