{"id":1099,"date":"2022-05-24T10:55:11","date_gmt":"2022-05-24T09:55:11","guid":{"rendered":"https:\/\/wiskunst.nl\/?page_id=1099"},"modified":"2022-05-24T13:49:30","modified_gmt":"2022-05-24T12:49:30","slug":"tips-trucs-en-artikelen-vba","status":"publish","type":"page","link":"https:\/\/wiskunst.nl\/index.php\/programmeren1\/office-programmeren\/tips-trucs-en-artikelen-vba\/","title":{"rendered":"VBA-overig"},"content":{"rendered":"<h3><a name=\"top\"><\/a>De volgende onderwerpen komen hier aan bod:<\/h3>\n<ul>\n<li><a href=\"#000\">Office 2013 en Windows 8.x: Clipboard<\/a><\/li>\n<li><a href=\"#002\">Zet Excel-reeks om in een rij<\/a><\/li>\n<\/ul>\n<p><a name=\"000\"><\/a><\/p>\n<h3>Office 2013 en Windows 8.x: Clipboard<\/h3>\n<p>In VBA kunt u op eenvoudige wijze het clipboard gebruiken:<\/p>\n<pre>Dim Clipboard As New DataObject\r\nClipboard.Clear\r\nClipboard. SetText \"Tekst voor op het kladblok\"<\/pre>\n<p>Echter in Windows 8.x werkt deze code niet meer. Microsoft onderkent het probleem maar heeft (nog) geen oplossing.<\/p>\n<p>U kunt met onderstaande (algemene) module het probleem ondervangen:<\/p>\n<pre>Option Explicit<\/pre>\n<pre>Declare Function GlobalUnlock Lib \"kernel32\" (ByVal hMem As Long) As Long\r\nDeclare Function GlobalLock Lib \"kernel32\" (ByVal hMem As Long) As Long\r\nDeclare Function GlobalAlloc Lib \"kernel32\" (ByVal wFlags As Long, _\r\n ByVal dwBytes As Long) As Long\r\nDeclare Function CloseClipboard Lib \"User32\" () As Long\r\nDeclare Function OpenClipboard Lib \"User32\" (ByVal hwnd As Long) As Long\r\nDeclare Function EmptyClipboard Lib \"User32\" () As Long\r\nDeclare Function lstrcpy Lib \"kernel32\" (ByVal lpString1 As Any, _\r\n ByVal lpString2 As Any) As Long\r\nDeclare Function SetClipboardData Lib \"User32\" (ByVal wFormat _\r\n As Long, ByVal hMem As Long) As Long\r\n Declare Function GetClipboardData Lib \"User32\" (ByVal wFormat As _\r\n Long) As Long\r\nDeclare Function GlobalSize Lib \"kernel32\" (ByVal hMem As Long) As Long\r\n \r\nPublic Const GHND = &amp;H42\r\nPublic Const CF_TEXT = 1\r\nPublic Const MAXSIZE = 4096<\/pre>\n<pre>Function ClipBoard_SetData(MyString As String)\r\n Dim hGlobalMemory As Long, lpGlobalMemory As Long\r\n Dim hClipMemory As Long, X As Long\r\n \r\n ' Allocate moveable global memory.\r\n '-------------------------------------------\r\n hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)\r\n \r\n ' Lock the block to get a far pointer\r\n ' to this memory.\r\n lpGlobalMemory = GlobalLock(hGlobalMemory)\r\n \r\n ' Copy the string to this global memory.\r\n lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)\r\n \r\n ' Unlock the memory.\r\n If GlobalUnlock(hGlobalMemory) &lt;&gt; 0 Then\r\n MsgBox \"Could not unlock memory location. Copy aborted.\"\r\n GoTo OutOfHere2\r\n End If\r\n \r\n ' Open the Clipboard to copy data to.\r\n If OpenClipboard(0&amp;) = 0 Then\r\n  MsgBox \"Could not open the Clipboard. Copy aborted.\"\r\n  Exit Function\r\n End If\r\n \r\n ' Clear the Clipboard.\r\n X = EmptyClipboard()\r\n \r\n ' Copy the data to the Clipboard.\r\n hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)\r\n \r\nOutOfHere2:\r\n \r\n If CloseClipboard() = 0 Then\r\n  MsgBox \"Could not close Clipboard.\"\r\n End If\r\n \r\nEnd Function<\/pre>\n<pre>Function ClipBoard_GetData(ByVal Dummy As String)\r\n Dim hClipMemory As Long\r\n Dim lpClipMemory As Long\r\n Dim MyString As String\r\n Dim RetVal As Long\r\n \r\n If OpenClipboard(0&amp;) = 0 Then\r\n MsgBox \"Cannot open Clipboard. Another app. may have it open\"\r\n Exit Function\r\n End If\r\n \r\n ' Obtain the handle to the global memory\r\n ' block that is referencing the text.\r\n hClipMemory = GetClipboardData(CF_TEXT)\r\n If IsNull(hClipMemory) Then\r\n  MsgBox \"Could not allocate memory\"\r\n  GoTo OutOfHere\r\n End If\r\n \r\n ' Lock Clipboard memory so we can reference\r\n ' the actual data string.\r\n lpClipMemory = GlobalLock(hClipMemory)\r\n \r\n If Not IsNull(lpClipMemory) Then\r\n  MyString = Space$(MAXSIZE)\r\n  RetVal = lstrcpy(MyString, lpClipMemory)\r\n  RetVal = GlobalUnlock(hClipMemory)\r\n \r\n  ' Peel off the null terminating character.\r\n  MyString = Mid(MyString, 1, InStr(1, MyString, Chr$(0), 0) - 1)\r\n Else\r\n  MsgBox \"Could not lock memory to copy string from.\"\r\n End If\r\n \r\nOutOfHere:\r\n \r\n RetVal = CloseClipboard()\r\n ClipBoard_GetData = MyString\r\n \r\nEnd Function<\/pre>\n<p><a href=\"#top\">Naar boven<\/a><\/p>\n<p><a name=\"002\"><\/a><\/p>\n<h3>Zet Excel-reeks om in een rij<\/h3>\n<h4>Multidimensionale dynamische array&#8217;s<\/h4>\n<p>Om een Excel-reeks (Range) in VBA om te zetten naar een rij moet u gebruik maken van een multidimensionale dynamische array.<\/p>\n<p>Dit komt bijvoorbeeld voor als u van een reeks getallen wat beschrijvende statistieken wilt bepalen; bijvoorbeeld kwartiel-bereik.<\/p>\n<p>Het probleem is dat u van te voren, dus wanneer u de code maakt, niet weet hoe groot de Range is.<\/p>\n<p>Hieronder staat een Sub dat een reeks in een array zal neerzetten:<\/p>\n<pre>Sub Reeks2Rij()\r\n    Dim reeks As Range 'alleen voor test-doeleinden\r\n    Dim tmpreeks() As Variant\r\n    Dim rij() As Variant\r\n    Dim r As Integer, k As Integer, i As Integer\r\n    Dim dimensie As Integer\r\n    Dim regels As Integer, kolommen As Integer\r\n    \r\n    'normaal geeft u reeks als value-paramter mee aan de sub, dus\r\n    'Sub Reeks2Rij(ByVal reeks as Range)\r\n    Set reeks = Application.ActiveSheet.Range(\"B2:D4\")\r\n    \r\n    regels = reeks.Rows.Count 'aantal regels van de reeks\r\n    kolommen = reeks.Columns.Count 'aantal kolommen van de reeks\r\n    dimensie = regels * kolommen 'aantal elementen van de rij\r\n    ReDim rij(1 To dimensie)\r\n    ReDim tmpreeks(1 To regels, 1 To kolommen)\r\n    For r = 1 To regels\r\n        For k = 1 To kolommen\r\n            'alleen ter illustratie\r\n            tmpreeks(r, k) = reeks(r, k).Value\r\n            'hier vindt de omzetting van reeks naar rij plaats\r\n            rij((r - 1) * kolommen + k) = tmpreeks(r, k)\r\n        Next k\r\n    Next r\r\n    \r\n    'alleen voor test-doeleinden\r\n    For i = 1 To dimensie\r\n        Debug.Print i &amp; \": \" &amp; rij(i)\r\n    Next i\r\nEnd Sub\r\n<\/pre>\n<p>Meestal zal u de variabele reeks als parameter in de Sub opnemen. De declaratie wordt dan: <em>Sub Reeks2Rij(ByVal reeks as Range)<\/em>.<\/p>\n<p>Wanneer u in code de dimensie wilt bepalen van een multidimensionale array dan gebruikt u de functie <em>UBound<\/em> met een tweede parameter. De tweede parameter geeft de dimensie aan. Dus de expressie <em>UBound(reeks , 2)<\/em> geeft aan tot hoever de tweede dimensie van reeks loopt.<\/p>\n<p><a href=\"#top\">Naar boven<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>De volgende onderwerpen komen hier aan bod: Office 2013 en Windows 8.x: Clipboard Zet Excel-reeks om in een rij Office 2013 en Windows 8.x: Clipboard In VBA kunt u op eenvoudige wijze het clipboard gebruiken: Dim Clipboard As New DataObject Clipboard.Clear Clipboard. SetText &#8220;Tekst voor op het kladblok&#8221; Echter in Windows 8.x werkt deze code [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":554,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"templates\/template-full-width.php","meta":{"_lmt_disableupdate":"","_lmt_disable":"","footnotes":""},"class_list":["post-1099","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>VBA-overig - Wiskunst<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/wiskunst.nl\/index.php\/programmeren1\/office-programmeren\/tips-trucs-en-artikelen-vba\/\" \/>\n<meta property=\"og:locale\" content=\"nl_NL\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"VBA-overig - Wiskunst\" \/>\n<meta property=\"og:description\" content=\"De volgende onderwerpen komen hier aan bod: Office 2013 en Windows 8.x: Clipboard Zet Excel-reeks om in een rij Office 2013 en Windows 8.x: Clipboard In VBA kunt u op eenvoudige wijze het clipboard gebruiken: Dim Clipboard As New DataObject Clipboard.Clear Clipboard. SetText &quot;Tekst voor op het kladblok&quot; Echter in Windows 8.x werkt deze code [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/wiskunst.nl\/index.php\/programmeren1\/office-programmeren\/tips-trucs-en-artikelen-vba\/\" \/>\n<meta property=\"og:site_name\" content=\"Wiskunst\" \/>\n<meta property=\"article:modified_time\" content=\"2022-05-24T12:49:30+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Geschatte leestijd\" \/>\n\t<meta name=\"twitter:data1\" content=\"4 minuten\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/wiskunst.nl\\\/index.php\\\/programmeren1\\\/office-programmeren\\\/tips-trucs-en-artikelen-vba\\\/\",\"url\":\"https:\\\/\\\/wiskunst.nl\\\/index.php\\\/programmeren1\\\/office-programmeren\\\/tips-trucs-en-artikelen-vba\\\/\",\"name\":\"VBA-overig - Wiskunst\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/wiskunst.nl\\\/#website\"},\"datePublished\":\"2022-05-24T09:55:11+00:00\",\"dateModified\":\"2022-05-24T12:49:30+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/wiskunst.nl\\\/index.php\\\/programmeren1\\\/office-programmeren\\\/tips-trucs-en-artikelen-vba\\\/#breadcrumb\"},\"inLanguage\":\"nl-NL\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/wiskunst.nl\\\/index.php\\\/programmeren1\\\/office-programmeren\\\/tips-trucs-en-artikelen-vba\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/wiskunst.nl\\\/index.php\\\/programmeren1\\\/office-programmeren\\\/tips-trucs-en-artikelen-vba\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/wiskunst.nl\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Programmeren\",\"item\":\"https:\\\/\\\/wiskunst.nl\\\/index.php\\\/programmeren1\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Office programmeren\",\"item\":\"https:\\\/\\\/wiskunst.nl\\\/index.php\\\/programmeren1\\\/office-programmeren\\\/\"},{\"@type\":\"ListItem\",\"position\":4,\"name\":\"VBA-overig\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/wiskunst.nl\\\/#website\",\"url\":\"https:\\\/\\\/wiskunst.nl\\\/\",\"name\":\"Wiskunst\",\"description\":\"2\u221e\u2227&gt;\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/wiskunst.nl\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"nl-NL\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"VBA-overig - Wiskunst","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/wiskunst.nl\/index.php\/programmeren1\/office-programmeren\/tips-trucs-en-artikelen-vba\/","og_locale":"nl_NL","og_type":"article","og_title":"VBA-overig - Wiskunst","og_description":"De volgende onderwerpen komen hier aan bod: Office 2013 en Windows 8.x: Clipboard Zet Excel-reeks om in een rij Office 2013 en Windows 8.x: Clipboard In VBA kunt u op eenvoudige wijze het clipboard gebruiken: Dim Clipboard As New DataObject Clipboard.Clear Clipboard. SetText \"Tekst voor op het kladblok\" Echter in Windows 8.x werkt deze code [&hellip;]","og_url":"https:\/\/wiskunst.nl\/index.php\/programmeren1\/office-programmeren\/tips-trucs-en-artikelen-vba\/","og_site_name":"Wiskunst","article_modified_time":"2022-05-24T12:49:30+00:00","twitter_card":"summary_large_image","twitter_misc":{"Geschatte leestijd":"4 minuten"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/wiskunst.nl\/index.php\/programmeren1\/office-programmeren\/tips-trucs-en-artikelen-vba\/","url":"https:\/\/wiskunst.nl\/index.php\/programmeren1\/office-programmeren\/tips-trucs-en-artikelen-vba\/","name":"VBA-overig - Wiskunst","isPartOf":{"@id":"https:\/\/wiskunst.nl\/#website"},"datePublished":"2022-05-24T09:55:11+00:00","dateModified":"2022-05-24T12:49:30+00:00","breadcrumb":{"@id":"https:\/\/wiskunst.nl\/index.php\/programmeren1\/office-programmeren\/tips-trucs-en-artikelen-vba\/#breadcrumb"},"inLanguage":"nl-NL","potentialAction":[{"@type":"ReadAction","target":["https:\/\/wiskunst.nl\/index.php\/programmeren1\/office-programmeren\/tips-trucs-en-artikelen-vba\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/wiskunst.nl\/index.php\/programmeren1\/office-programmeren\/tips-trucs-en-artikelen-vba\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/wiskunst.nl\/"},{"@type":"ListItem","position":2,"name":"Programmeren","item":"https:\/\/wiskunst.nl\/index.php\/programmeren1\/"},{"@type":"ListItem","position":3,"name":"Office programmeren","item":"https:\/\/wiskunst.nl\/index.php\/programmeren1\/office-programmeren\/"},{"@type":"ListItem","position":4,"name":"VBA-overig"}]},{"@type":"WebSite","@id":"https:\/\/wiskunst.nl\/#website","url":"https:\/\/wiskunst.nl\/","name":"Wiskunst","description":"2\u221e\u2227&gt;","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/wiskunst.nl\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"nl-NL"}]}},"_links":{"self":[{"href":"https:\/\/wiskunst.nl\/index.php\/wp-json\/wp\/v2\/pages\/1099","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wiskunst.nl\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/wiskunst.nl\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/wiskunst.nl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wiskunst.nl\/index.php\/wp-json\/wp\/v2\/comments?post=1099"}],"version-history":[{"count":4,"href":"https:\/\/wiskunst.nl\/index.php\/wp-json\/wp\/v2\/pages\/1099\/revisions"}],"predecessor-version":[{"id":1207,"href":"https:\/\/wiskunst.nl\/index.php\/wp-json\/wp\/v2\/pages\/1099\/revisions\/1207"}],"up":[{"embeddable":true,"href":"https:\/\/wiskunst.nl\/index.php\/wp-json\/wp\/v2\/pages\/554"}],"wp:attachment":[{"href":"https:\/\/wiskunst.nl\/index.php\/wp-json\/wp\/v2\/media?parent=1099"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}