Vectorial formula for cell validation in Excel using VBA -

-

i writing vba formula check characters in cell "testchars" allowed, allowed means each character appears in list defined cell "allowedchars". make things harder, formula work on ranges of cells rather on single cell.

the current code seems work:

option explicit   public function allcharsvalid(inputcells range, allowedchars string) boolean ' check characters in inputcells among ' characters in allowedchars      dim char string     dim index integer     dim rangetestchars range     dim testchars string     each rangetestchars in inputcells         testchars = rangetestchars.value         index = 1 len(testchars)             char = mid(testchars, index, 1)             if instr(allowedchars, char) = 0                 allcharsvalid = false                 exit function             end if         next index     next rangetestchars     allcharsvalid = true end function

i have following questions:

  1. the formula takes range , returns single boolean. prefer vectorized function, where, given input range, corresponding range of booleans. seems built-in formulas 'exact' can (those formulas have press ctrl-shift-enter execute them , curly-brackets). there way user-defined functions?
  2. i not new programming, new vba (i started literally today). there obvious problem, weirdness above code?
  3. are there special characters, extremely long texts or particular input values cause formula fail?
  4. is there easier way achieve same effect? code slow?
  5. when start typing built-in formulas in excel suggestions , auto-completion. doesn't seem work formula, asking or possible achieve this?

i realize question contains several weakly related sub-questions, happy sub-answers.

the following code return range of boolean values offset 1 column initial input range. create new tab in excel , run testallcharsvalid , show immediate window in ide see how works.

sub testallcharsvalid()      dim integer     dim cll range, rng range     dim allowedchars string      ' insert test values in sheet: testing purposes     activesheet ' change thisworkbook.sheets("nameofyoursheet")         set rng = .range("a1:a10")         = 1 10             .cells(i, 1) = chr(i + 92)         next     end      ' fill allowedchars letters z: testing purposes     = 97 122         allowedchars = allowedchars & chr(i)     next      ' boolean range     set rng = allcharsvalid(rng, allowedchars)      ' check if returned range contains expected boolean values     = 0     each cll in rng         = + 1         debug.print & " boolean value: " & cll.value     next cll  end sub  ' check characters in inputcells among ' characters in allowedchars public function allcharsvalid(inputcells range, allowedchars string) range      dim booltest boolean     dim char string     dim index integer     dim rangetestchars range, rangebooleans range, rangetemp range     dim testchars string     each rangetestchars in inputcells         booltest = true         testchars = rangetestchars.value         index = 1 len(testchars)             char = mid(testchars, index, 1)             if instr(allowedchars, char) = 0 booltest = false         next index          set rangetemp = rangetestchars.offset(0, 1) ' change offset suits purpose         rangetemp.value = booltest          if rangebooleans nothing             set rangebooleans = rangetestchars         else             set rangebooleans = union(rangebooleans, rangetemp)         end if      next rangetestchars      set allcharsvalid = rangebooleans  end function

cf 2) if length of test string zero, function return true cell in question, may not desirable.

cf 3) there limit how many characters excel cell can contain, read more here. suppose, if concatenated long strings , sent them function, reach integer limit of +32767, cause run-time error due integer index variable. however, since character limit of excel cells +32767, function should work without problems.

cf 4) none know of.

cf 5) not easiest thing achieve, there found here.


Comments

Post a Comment

Popular posts from this blog

mysql - Dreamhost PyCharm Django Python 3 Launching a Site -

-
i'm new python/django , created simple django website using pycharm. site works fine running on computer, i'm lost when comes taking site onto host (dreamhost). i'm able upload files correct directories fine, , believe made correct changes database on mysql. i'm not sure go there though. right now, page shows files rather site. direction appreciated. much! you should not run django application using python manage.py runserver on production. how run application on django depends on server (apache, nginx) want use for apache - https://docs.djangoproject.com/en/1.10/howto/deployment/wsgi/modwsgi/ for nginx - https://www.digitalocean.com/community/tutorials/how-to-serve-django-applications-with-uwsgi-and-nginx-on-ubuntu-14-04 the mysql database should, of course, exist , have correct credentials. or, if choose use sqlite, file should accessible, readable , writable www-user (may different depending on linux distributive)
Read more

java - Sending SMS with SMSLib and Web Services -

-
i have made rest web service send smss using hsdpa usb modem. using smslib in java send smss. every time web service invoked, create gateway start service, send message, stop service , remove gateway. takes 20 seconds each message. found starting service takes lot of time. part of code use send smss service.getinstance().addgateway(gateway); service.getinstance().startservice(); outboundmessage msg = new outboundmessage(phonenumber, message); if (service.getinstance().sendmessage(msg)) { result = "message sent successfully!!"; } else { result = "could not send message."; } service.getinstance().stopservice(); service.getinstance().removegateway(gateway);//remove gateway is there way can start service once if not started , use send message when ever web service invoked? why dont group messages , send in 1 go? service.getinstance().sendmessages(messa...
Read more

java - How to resolve The method toString() in the type Object is not applicable for the arguments (InputStream) -

-
i'm trying read json file local machine. @path("/") public class jsonparsing { file f = new file("file.json"); if (f.exists()){ inputstream = new fileinputstream("file.json"); string jsontxt = ioutils.tostring(is); system.out.println(jsontxt); jsonobject json = new jsonobject(jsontxt); string = json.getstring("1000"); system.out.println(a); } } but i'm getting error in tostring() method. possible read .txt file containing json object local machine? if possible, how done? firstly: whenever ask question- add imports well. secondly: yes possible read .txt file containing json object. steps: 1. add maven dependency in pom.xml - <dependency> <groupid>com.googlecode.json-simple</groupid> <artifactid>json-simple</artifactid> <version>1.1.1</version> </dependency> or add jar of dependency. 2.import...
Read more