VBA Developers Handbook, 2nd Edition. questions on procedures and functions

there's nothing wrong, per se, aside the penultimate line in your Function... i.e. dhCountInCaseS

thanks @xlent. my goof. appreciate you taking the time to debug this for me. now all working.

so onto another issue with the following function:

Please Login or Register  to view this content.

no matter what i try, it always says "Constant expression required" even though it's optional. here is what i tried to use:

Please Login or Register  to view this content.

even if i add dhcDelimiters to the function, it still fails at the function. what am i implementing wrong?

ya i had tried that solution but did remove it from the procedures. however when i debug the project, i still get the same error on the function, this line:

Please Login or Register  to view this content.

and it highlights dhcDelimiters.

yes i understand, but even if i comment out the code after the function, when i try to compile, it always says the same error in post #6, so the problem is that the function error's out. i have even copied the code directly from the book, but still get the same error. so what is wrong with the code in the function. that is where the error is. so one note, is that i put the const dhcDelimiters in the actual function, so compile works but when i run one of the procedures, it still goes back to the constant error. what is my limited brain not understanding?

sigh, im without words. i copied everything verbatim from your post. cant get rid of the constant error.

here is two of my public procedure, neither work.

Please Login or Register  to view this content.

everything is in the same module (only have one). should i just move on?

is maybe something wrong with my excel program? or is it just me (Probably). let me attach my workbook, and maybe you can take a peek at it, see if that's whats wrong?

thanks again for sticking the out on me, i knew is was my mistake.

and that helped me solve a couple of other issues that weren't working as procedures. now i can cross off chapter 1. hopefully this makes me a better coder.

thanks, that will help me in several of the Array functions. appreciate the help. got two more that aren't working from chapter 2. will post them separately. then im done with the chapter and on to three.

thanks, i use notepad++ for some of my coding, just ran a compare. i see the two issues. thanks for pointing that out. one of those "cant see the forest for the tree's issue".

so another question on the numbers chapter. this has to do with factorials and recursive factorials. if i run the following procedure, shouldn't they both be the same (or am i not understanding what recursive means?

Please Login or Register  to view this content.

the result of running this is:

the factorial of 19 is 1.21645100408832E+17
the factorial recursive of 19 is 19

i did find a typo which i fixed so here is the recursive function:

Please Login or Register  to view this content.

what am i doing wrong?

Originally Posted by bakerman2

OP moves with fast pace through Functions.

well yup, basically i run through each chapter and the ones that don't work i add a "_nw" to. that way i can quickly find the ones that are "not working". by reading each chapter through the end, i can fix a lot of mistakes. but yes, i am cranking out the questions. really glad you guys are lending a hand. this will go a long way into making me a better coder.

Originally Posted by rorya

I get the same result with both.

sometimes i get hiccups with excel not working as expected, so let me load up my workbook to see if you are getting the same result as I am. if you run this and it works, it just means my version of excel (office 365) is to blame.

what version of excel are you using Rorya?

btw, closing chapter 2, on to dates and times

quick question, does anyone have the CD from this book? looking to get the Holidays.xml or Holidays.mdb file.

so only two procedures are not working from chap 3. here is the first function:

Please Login or Register  to view this content.

and here is the procedure that doesn't work (prints 12:00:00 AM)

Please Login or Register  to view this content.

is the book wrong or did i make a typo?

thoughts anyone?

thanks, thought it was me (mistyping). so the other function that doesn't work is a doozy for me.

the function thats tripping me up is below:

Please Login or Register  to view this content.

the procedure that i am running is:

Please Login or Register  to view this content.

it complains that "GetSystemDefaultLCID" and "GetLocalInfo" are not defined. from reading the text i understand that these are api calls. i googled the internet for both functions but came up empty (microsoft says they are depreciated i think). is there a way to declare the api function calls in the declaration section?

thanks rorya for the zip, helped me to close chapter 3. just so you know i have the ebook, so don't have access to the CD. trying to find out if sybex still has that with the printed book. be so much easier if i could just copy.

so finished with chapter 4 and 5, moving on to 6 "arrgh" = heavy topic. reading on classes from other excel sites. not for the faint of heart.

so i am having an issue with my new class module, getting a run time error 424, object required.

here is my module code:

Please Login or Register  to view this content.

i get the error on the set cls_btnCode statement.

here is my class module:

Please Login or Register  to view this content.

what am i doing wrong?

it behaves correctly in that it shows the userform, lets me type, tracks the character count. when i press ok, it displays a msgbox which contains the correct text.

Also, you don't want to be breaking encapsulation like that, you should be passing the UserForm object into the class. Oh, and don't use the default instances of user forms, ie. Never use NameOfUserForm.Show

You should be using them properly:

Please Login or Register  to view this content.

The default instances won't always be what you expect them to be

thanks @shg and @rorya for that info. @kyle123, here is code that is working but how do i get the data from the code window into the userform that the button generator makes?

here's the module code:

Please Login or Register  to view this content.

and here is the class module code:

Please Login or Register  to view this content.

and here is my userform code:

Please Login or Register  to view this content.

so i tried to put a msgbox in the module code to get the data from the code window but it failed on that line. how do i do that?

so nevermind, i got it working.

i added this (finally worked) to the module and now i can get the info out to the module.

Please Login or Register  to view this content.

now the question is how to get that code into the userform that the button generates. but i have to wait to get home to get the file that does that. didn't update my dropbox.

I've got no idea what that code is supposed to be doing, it doens't seem to make a lot of sense unless you are trying to create multiple userforms (even then it wouldn't work). I don't understand why you are using a class for this, there doesn't seem to be any need?

What do you want the code to do?

so the button generator generates multiple buttons. other than the spacing, i have that working. so now that the class module clsCode is working. i now need that code to 'attach' it to the buttons (buttons need to do something right?) but the file that i need to do this is at home. so the question will have to wait till i can get to my laptop. im sure once i pull this all together, it will make sense (at least i hope that it will). please be patient as this grasshopper is still learning the ropes

Are you still trying to do that IDE automation where you are writing code with code?

yes, isn't that the way im supposed to do it? i thought that my first approach was wrong (in that i did it all at design time).

I don't think you should do any of it at design time. I don't understand why you'd want to.

am i still doing it wrong?

It really depends why you want to do it. Why do you want to do any of it at design time? - What's the purpose of the project?

well at first, all i knew was to create it at design time (showing my lack of knowledge). then you and others said it's not the way to do it. hence writing code that makes code and the use of class modules. i thought that was the right way to do it. and since i didn't have the knowledge, i got the VBA book to learn more. now im on chapter 6 which goes into more detail about class modules. which is why i wrote the clsCode code to get the actual code for the button. do i still not understand the correct way to do this? and the purpose was to do this right (and not at design time, which i said was all that i knew at the time)

If you want to have a userform that you can add a dynamic number of buttons to that have similar behaviour, then do it all at runtime:

Module:

Please Login or Register  to view this content.

Userform:

Please Login or Register  to view this content.

Class:

Please Login or Register  to view this content.

ahh, that is it very helpful. can't wait to get home to do this. thanks for hanging in there.