VBA Worksheetfunction

Hi guys, I am quite new to VBA excel programming so I have two simple questions, firstly what's the difference between worksheetfunction. and application. ? From what I've played around with, both are just used to call upon functions in excel to be used in VBA, is there a specific difference between the two?

Second question is the code below:

Please Login or Register  to view this content.

I know that Rnd() is a VBA in built function, however if I replace it with worksheetfunction.Rand() which is using the excel Rand() function, the code doesn't work, why is this? I tried putting application.rand() as well, it doesn't work either, is there no way I can use Rand() from excel?

Thanks!

As you type Application.WorksheetFunction. the VBA intellisense should list for you the functions available.

Attachment 217073


If you are NOT seeing those, then turn them on here:

Attachment 217075

Thanks for that, however I am still wondering if VBA has its own function then does that mean we can't use the same function from excel? Why doesn't Rand() show up after I type worksheetfunction? (I have intellisense enabled)

Also what's the difference between say Worksheetfunction.NPV and Application.NPV, both codes calls upon the function NPV from excel but is there any differences between the two? When do we need to put worksheetfunction. in front of the function and when do we need to put Application. in front of the function?

Thanks

Intellisense shows you the worksheetfunctions you can employ with that method. Not in the list? Not available.


I was taught early on to always use the Application.WorksheetFunction.????? syntax so I do. Haven't spent time trying to test why I should... sorry.