dim vs set in vba

lezawang · Oct 1, 2018

Hi
I just noticed this code, the author used

Sub UsePassword() Dim wkbOne As Workbook Set wkbOne = Application.Workbooks.Open("C:\Password.xls") wkbOne.Password = InputBox ("Enter Password") wkbOne.Close End Sub

https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.password

Joe4 · Oct 1, 2018

They are two very different things, and cannot be substituted for each other.

Dim is just used to declare the variable type before using it (i.e. Integer, String, Date, Boolean, Worksheet, Workbook, Range, etc)
Set is used to assign a value to an Object (like a Range, Workbook, or Worksheet object)

You do NOT use Set on "values", like Integer, String, Date, or Boolean

See: https://www.homeandlearn.org/the_set_keyword.html

lezawang · Oct 1, 2018

Thank you very much for your reply. Just one more question please, what I am assigning to this object when I say:

Set wkbOne = Application.Workbooks.Open("C:\Password.xls"[FONT=Consolas, Menlo, Monaco, Lucida Console, Liberation Mono, DejaVu Sans Mono, Bitstream Vera Sans Mono, Courier New, monospace, sans-serif])[/FONT]

[FONT=Consolas, Menlo, Monaco, Lucida Console, Liberation Mono, DejaVu Sans Mono, Bitstream Vera Sans Mono, Courier New, monospace, sans-serif]I am confused because workbook object has many properties, so where the value of the right hand side of that above line will go to ? will go to which properties.
Let me explain it different. I would have understood if for example the above was like this

set wkbOne.XXXXXX = Application......

in that case I assigned whatever to the right to the property XXXXXX of wkbOne. am I right? [/FONT]

Norie · Oct 1, 2018

wbOne is a workbook object variable and it's a reference to the workbook being opened, not any of the workbook properties.

Once you have an object variable that references a workbook object you can access all of it's properties/methods via the variable.

lezawang · Oct 1, 2018

Thank you very much. I got it. Can I write a code to remove that password? in VBA? if not then how I can get rid of the password? Thank you again

Norie · Oct 1, 2018

All you should need to do is save the workbook without specifying a password.

TotallyConfused · Oct 1, 2018

Norie said:
wbOne is a workbook object variable and it's a reference to the workbook being opened, not any of the workbook properties.

Once you have an object variable that references a workbook object you can access all of it's properties/methods via the variable.

@Norie

THANK YOU Norie for your explanation of Objects and Properties. These two terms are something that I’ve struggled with since day one, which is part of the reason for my name. I’ve looked at Microsoft’s help files, but they seemed to add to the confusion. Whenever I’d lookup ‘Objects’ they would talk about properties of objects and vice versa. It was like running around in circles. After reading your simple, easy to understand explanation as well as your example of wbOne and what it is, maybe there is starting to be a light appear at the end of the tunnel.

I have no idea what your real-life job or profession is, but if you’re not one, then you should be a teacher or professor. To this TotallyConfused student, you certainly qualify as a professor here. After reading the comment made by Lezawang, it sounds as if I’m not the only person that appreciates you taking the time and effort to explain something. Once again, THANK YOU!

TotallyConfused

P.S. No, I’m not going to change my name just yet. There are at least a million other things about Excel and VBA that makes my name valid.

dim vs set in vba

lezawang

Well-known Member

Excel Facts

Joe4

MrExcel MVP, Junior Admin

lezawang

Well-known Member

Norie

Well-known Member

lezawang

Well-known Member

Norie

Well-known Member

TotallyConfused

Board Regular

Similar threads

Forum statistics

Share this page

dim vs set in vba

lezawang

Well-known Member

Excel Facts

Joe4

MrExcel MVP, Junior Admin

lezawang

Well-known Member

Norie

Well-known Member

lezawang

Well-known Member

Norie

Well-known Member

TotallyConfused

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock