Invalid use of property vba ошибка

Вопрос:

Private pCust_index as dictionary

Public Property Set set_cust_index(ByRef custIndex As Dictionary)
Set pCust_index = New Dictionary
Set pcust_index = custIndex
End Property

Private Sub newCustContagious_LBL_Click()
Dim contForm as New custContagions
Call contForm.set_cust_index(pCust_index)  'note pCust_index is a private here too
Call contForm.Show
...

Лучший ответ:

Public Property Set set_cust_index(ByVal custIndex As Dictionary)
    Set pcust_index = custIndex
End Property

Set contForm.set_cust_index = pCust_index

1. 10-08-2015, 12:31 PM

   
   

   #1

   

   
   Registered User
   

   

   ---

   VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
   

   Hi all,

   I’m trying to create a code for cell G20 (see attached workbook), which represents a value for «intercept». This «intercept» value changes, depending on multiple conditions, which is why I need to write a complex nesting IF function with multiple AND conditions to get the correct value. Currently, I have only six nesting IFs, but eventually I will need 963. I am just testing this out to see if I can get six going and then I will worry about the remaining 957 lol.

   So here is my code to accomplish this:

   Sub IfThenNesting()

   If C3 = «CCI» And C5 = «Case-Shiller National Home Price Index» And C9 = 5 Then Range(«I4»).Value
   End If
   ElseIf C3 = «CCI» And C5 = «National Unemployment Rate» And C9 = 0 Then Range(«I5»).Value
   End If
   ElseIf C3 = «CCI» And C5 = «S&P 500 Stock Prices» And C9 = 0 Then Range(«I6»).Value
   End If
   ElseIf C3 = «CCI» And C5 = «# of House Units Starts» And C9 = 3 Then Range(«I7»).Value
   End If
   ElseIf C3 = «National Unemployment Rate» And C5 = «S&P 500 Stock Prices» And C9 = 0 Then Range(«I8»).Value
   End If
   ElseIf C3 = «S&P 500 Stock Prices» And C5 = «Nordstrom Stock Price» And C9 = 0 Then Range(«I9»).Value
   End If
   End Sub

   and my workbook is attached here:
   VBA IF test.xlsx

   I want to do a nesting IF statement with multiple conditions. It all makes sense if you look at the worksheet. I want the calculation of the IF statement to be entered into cell G20.

   Any help is GREATLY appreciated on getting me started. THANK YOU.

   Last edited by tvwright; 10-08-2015 at 12:49 PM.

   ---

2. 10-08-2015, 12:34 PM

   
   

   #2

   Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
   

   Get rid of every «End If» and «ElseIf» — you really are not using nesting at all.

   But your code has other problems: if your C3, C5, and C9 are cells , then use

   If Range(«C3»).Value = «CCI» And Range(«C5»).Value = «Case-Shiller National Home Price Index» And Range(«C9»).Value = 5 Then Range(«G19»).Value = Range(«I4»).Value

   But if you expect 900+ nested ifs then you really should be doing something different.

   Last edited by Bernie Deitrick; 10-08-2015 at 12:41 PM.

   Bernie Deitrick
   Excel MVP 2000-2010

   ---

3. 10-08-2015, 12:42 PM

   
   

   #3

   

   
   Registered User
   

   

   ---

   Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
   

   Originally Posted by Bernie Deitrick
   

   Get rid of every «End If» and «ElseIf»

   But your code has other problems: if your C3, C5, and C9 are cells , then use

   If Range(«C3»).Value = «CCI» And Range(«C5»).Value = «Case-Shiller National Home Price Index» And Range(«C9»).Value = 5 Then Range(«I4»).Value = «Something»

   OK Great, did that. Still get the same «Compile Error: Invalid Use of Property» on the very first line of code.

   The error is highlighting this line of code:

   FirstLine.png

   Also, how do I get it so that it enters this IF formula into cell G20? Thank you!

   ---

4. 10-08-2015, 12:45 PM

   
   

   #4

   

   
   Registered User
   

   

   ---

   Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
   

   Originally Posted by Bernie Deitrick
   

   Get rid of every «End If» and «ElseIf» — you really are not using nesting at all.

   But your code has other problems: if your C3, C5, and C9 are cells , then use

   If Range(«C3»).Value = «CCI» And Range(«C5»).Value = «Case-Shiller National Home Price Index» And Range(«C9»).Value = 5 Then Range(«G19»).Value = Range(«I4»).Value

   But if you expect 900+ nested ifs then you really should be doing something different.

   OK — I still get the same error with the first line even after making those ‘Range(«C9»).Value’ updates for all of my cell values. what are other options that I should be doing other than nesting IF statements? Keep in mind, I am not a VBA coder so I was trying to keep it simple even though it is very repetitive.

   My code now currently reads like this and still get the error on first line:

   Sub Test()
   If Range(«C3»).Value = «CCI» And Range(«C5»).Value = «Case-Shiller National Home Price Index» And Range(«C9»).Value = 5 Then Range(«I4»).Value

   If Range(«C3»).Value = «CCI» And Range(«C5»).Value = «National Unemployment Rate» And Range(«C9»).Value = 0 Then Range(«I5»).Value

   If Range(«C3»).Value = «CCI» And Range(«C5»).Value = «S&P 500 Stock Prices» And Range(«C9»).Value = 0 Then Range(«I6»).Value

   If Range(«C3»).Value = «CCI» And Range(«C5»).Value = «# of House Units Starts» And Range(«C9»).Value = 3 Then Range(«I7»).Value

   If Range(«C3»).Value = «National Unemployment Rate» And C5 = «S&P 500 Stock Prices» And Range(«C9»).Value = 0 Then Range(«I8»).Value

   If Range(«C3»).Value = «S&P 500 Stock Prices» And C5 = «Nordstrom Stock Price» And Range(«C9»).Value = 0 Then Range(«I9»).Value

   End Sub

   Last edited by tvwright; 10-08-2015 at 12:54 PM.

   ---

5. 10-08-2015, 01:01 PM

   
   

   #5

   Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
   

   First, you are putting your code into the wrong place. Delete the code that you have, then double-click the Module1 object in your project, and paste this into the window that appears. You were getting the error on the first line because the sub was not compiling, because you were using

   …. Then Range(«I4»).Value

   without assigning a value to I4 or using I4’s value in any way.

   ---

6. 10-08-2015, 01:40 PM

   
   

   #6

   

   
   Registered User
   

   

   ---

   Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
   

   Originally Posted by Bernie Deitrick
   

   First, you are putting your code into the wrong place. Delete the code that you have, then double-click the Module1 object in your project, and paste this into the window that appears. You were getting the error on the first line because the sub was not compiling, because you were using

   …. Then Range(«I4»).Value

   without assigning a value to I4 or using I4’s value in any way.

   Thank you. Copying and pasting the above code worked!!! WOW. I am very thankful for your help and it makes sense. Except I made a typo in my first post and said G19 when I meant G20 so I will change it. You’re awesome! THANK YOU.

   Except now I have to press «run» anytime I want G20 to update. How do I have it automatically run anytime someone changes any of the criteria in cells C3, C5, and or C9?

   Last edited by tvwright; 10-08-2015 at 01:43 PM.

   ---

7. 10-08-2015, 01:45 PM

   
   

   #7

   Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
   

   Great! Get yourself a good book — I always recommend John Walkenbach’s «Excel XXXX Power Programming With VBA» series — available for older versions used (and cheaper), but the basics still apply, so it is a good start:

   http://www.amazon.com/gp/offer-listi…condition=used

   ---

8. 10-08-2015, 01:49 PM

   
   

   #8

   

   
   Registered User
   

   

   ---

   Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
   

   Originally Posted by Bernie Deitrick
   

   Great! Get yourself a good book — I always recommend John Walkenbach’s «Excel XXXX Power Programming With VBA» series — available for older versions used (and cheaper), but the basics still apply, so it is a good start:

   http://www.amazon.com/gp/offer-listi…condition=used

   Thank you. I will definitely get a book. I added an «edit» after I already hit submit… one more quick question: how do I get the code to automatically run anytime the conditions in cells C3, C5 and/or C9 are changed?

   ---

9. 10-08-2015, 02:00 PM

   
   

   #9

   Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
   

   1) Copy this code.
   2) Right-Click the sheet tab of interest.
   3) Select «View Code»
   4) Paste the code into the window that appears.
   5) Save the file as a macro-enabled .xlsm file.
   6) Make changes as needed to the macro name

   ---

10. 10-08-2015, 02:15 PM

    
    

    #10

    

    
    Registered User
    

    

    ---

    Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
    

    ---

11. 10-14-2015, 01:57 PM

    
    

    #11

    

    
    Registered User
    

    

    ---

    Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
    

    Originally Posted by Bernie Deitrick
    

    1) Copy this code.
    2) Right-Click the sheet tab of interest.
    3) Select «View Code»
    4) Paste the code into the window that appears.
    5) Save the file as a macro-enabled .xlsm file.
    6) Make changes as needed to the macro name

    Hi Bernie,

    I ordered that book from Amazon and it hasn’t come yet. But I’m excited to be getting it next week. So I have another question regarding this project for you since you have been so great at helping! Attached is my updated workbook. Everything is working correctly and flawlessly, thanks to your help.

    Right now all of my data and calculations are on the same sheet, but I would like to move the data to sheet 2, and have my calcuations on sheet 1 so I don’t have to be looking at all the data on the form. Here is the workbook for reference:

    La la.xlsm

    Here is my code for reference:

    In Module1

    In Sheet 2

    So all of the above code works perfectly. Except now I have all the data on sheet named «Calculation» in the same position except on sheet named «data». I want the VBA code to use the cells on worksheet named «Data» instead of the current cell ranges it is using on worksheet named «calculation.

    I tried changing all of the above to

    but this did not work? Can you send me in the right direction to how to keep the code working properly by changing the data to another sheet?

    Thank you! <3

    Last edited by tvwright; 10-14-2015 at 02:03 PM.

    ---

12. 10-14-2015, 02:50 PM

    
    

    #12

    Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
    

    You don’t have a worksheet name «data» in the posted file.

    So try this. Change

    to

    ---

13. 10-14-2015, 02:58 PM

    
    

    #13

    

    
    Registered User
    

    

    ---

    Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
    

    I Am so sorry. I updated the worksheet names and didn’t save it before I sent you the file. You got the older version. Here is the new version. Will your code still work? I will go ahead and try it in a minute.

    La la.xlsm

    ---

14. 10-14-2015, 03:58 PM

    
    

    #14

    Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
    

    You should just use a formula in those three cells:

    B28:

    =SUMPRODUCT((F4:F15=C3)*(G4:G15=C5)*I4:I15)

    B29:

    =SUMPRODUCT((F4:F15=C3)*(G4:G15=C5)*H4:H15)

    C19

    =INDEX(J:J,SUMPRODUCT((F4:F15=C3)*(G4:G15=C5)*ROW(H4:H15)))

    When you have a larger table just change the 15s to the a number higher than the number or rows in your table.

    In code, you would use:

    Last edited by Bernie Deitrick; 10-14-2015 at 04:03 PM.

    ---

15. 10-14-2015, 04:24 PM

    
    

    #15

    

    
    Registered User
    

    

    ---

    Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
    

    Originally Posted by Bernie Deitrick
    

    You don’t have a worksheet name «data» in the posted file.

    So try this. Change

    to

    Hi Bernie,

    Thank you. It says I have a Run Time Error when I enter that code. I replaced all cell values to have the sheet reference preceding it. Here is my code now after following your update:

    and I get «Run-Time error ‘424’ Object Required» pop up. It highlights my sixth line of code, see picture below:

    delete.png

    ---

16. 10-14-2015, 04:25 PM

    
    

    #16

    

    
    Registered User
    

    

    ---

    Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
    

    Also here is my workbook now. It has no data in the calculation tab.

    La la.xlsx

    Any idea on why I’m getting that error? Thank you!

    ---

17. 10-14-2015, 04:34 PM

    
    

    #17

    

    
    Registered User
    

    

    ---

    Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
    

    OK I got it to work. THe issue was I did not have a T in the «shtd» or «shtc» in this part:

    Corrected version:

    it used to be..
    Incorrect version:

    I added the T, and it works when I manually «run» the code, but it is no longer automatically updating. The code you gave me to get it to automatically update is no longer applicable because I changed the sheet.How do I get it to run automatically again? I copied the code into the «data» sheet’s code dialogue, but this does not work.

    EDIT: OK so weirdly enough, i closed out of excel and re-opened it and now it all works automatically without any problems. this is so confusing! Thank you again for all of your help.

    Last edited by tvwright; 10-14-2015 at 06:58 PM.

    ---

18. 10-15-2015, 10:31 AM

    
    

    #18

    Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
    

    Great — glad to hear you got it to work, despite my poor typing skills

    ---

19. 10-20-2015, 10:34 AM

    
    

    #19

    

    
    Registered User
    

    

    ---

    Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
    

    Originally Posted by Bernie Deitrick
    

    1) Copy this code.
    2) Right-Click the sheet tab of interest.
    3) Select «View Code»
    4) Paste the code into the window that appears.
    5) Save the file as a macro-enabled .xlsm file.
    6) Make changes as needed to the macro name

    Hi Bernie,

    I have a few more macros that I would like to automatically run when those same cells are changed. The above code currently only works to run my first macro called «Test». I have 5 additional macros now that I would also like to run in the same way, named «Test1» «Test2» «Test3» «Test4» and «Test5» without the quotation marks. How do I edit your above code to get them to run in the same way as my first macro called «Test»?

    I attempted to change the code to this, but it did not work:

    How do you advise?

    Also I got the book you recommended and have already read Part 1 — it was just the introductory stuff. The next part is more advanced, and I’m very excited to read it tonight!

    ---

20. 10-20-2015, 12:16 PM

    
    

    #20

    Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
    

    ---

21. 10-20-2015, 12:54 PM

    
    

    #21

    

    
    Registered User
    

    

    ---

    Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
    

    Originally Posted by Bernie Deitrick
    

    Thanks, but when I do that it triggers a window with a drop down menu prompting me to select which single macro of the 11 I would like to run. See image below:

    MacroList.png

    I would like to select ALL macros and run them at the same time. I had to make 11 different macros, because I kept getting an error that the procedure was «too long» so I had to divide the one long procedure into multiple procedures. Do you have any suggestions?

    Thank you so much!

    ---

22. 10-20-2015, 03:50 PM

    
    

    #22

    Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
    

    Post your workbook.

    Did you look at using the formula that I posted instead of macro code?

    ---

23. 10-20-2015, 03:59 PM

    
    

    #23

    

    
    Registered User
    

    

    ---

    Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
    

    Originally Posted by Bernie Deitrick
    

    Post your workbook.

    Did you look at using the formula that I posted instead of macro code?

    Sorry for the late reply Bernie, usually I get an email notification when you respond but this time I did not for some reason. I did not look at the formula you posted, no. Do you think I should do that still — here is my workbook:

    La la (Autosaved).xlsm

    Let me know! Thank you.

    EDIT:

    It appears that your formula method is working!!!!!!!!!!!!!!! WOW. THANK YOU~

    Last edited by tvwright; 10-20-2015 at 04:33 PM.

    ---

24. 10-20-2015, 05:00 PM

    
    

    #24

    

    
    Registered User
    

    

    ---

    Re: VBA Noob, «Compile Error: Invalid Use of Property» On First line of Code (great..)
    

    If you’re interested, here is the final worksheet (for now… )

    La la Final.xlsm

    Last edited by tvwright; 10-20-2015 at 05:22 PM.

    ---